Solved

Importing CSV with 255+ fields into Access to get 50 fields

Posted on 2014-11-19
8
714 Views
Last Modified: 2014-11-19
I have a CSV file with more than 255 fields (columns), an export from FormStack survey, with more than 12,000 records. I need to import the file, but don't need all the fields. Access 2013 doesn't want to import the file even when I tell it to skip most of the fields via Import Specs. It returns "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." The fields that are not "skipped" are all set to "Long Text" format except the date fields. I think the error comes up because the Import Spec fields don't allow me to change or skip the fields after the 255th field.
So I can't import the file into Access via the standard, menu-driven method. Any help greatly appreciated!
0
Comment
Question by:zself
8 Comments
 
LVL 57
ID: 40452288
<<So I can't import the file into Access via the standard, menu-driven method. >>

 Your only option in this case is to use VBA, open the file directly, read the records in, and then parse as needed.

 You would use an OPEN statement, then INPUT or GET depending on how you want to access the file.   Since it's CSV, I would use INPUT and record mode.

If the data is well formatted, you can use SPLIT() to break things up on the comma or you can use Left(), Mid(), and InStr() to parse the record.

 Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40452300
No points please.  Agree with Jim on the technique to manage this.  

You will need to play with the technique for reading the file, in particular searching for the commas, and ensuring that you don't find a comma that is embedded in a value (either within quotes for a text string, or within a number formatted with comma delimiters).

Any chance you can export the FormStack data in two chunks?
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40452302
Hi,

Maybe you could try to open it in Excel then deleting the excess columns and save the result, as csv

EDITED the limit for the number of  imported fields seem to be 2000 in excel in comparison of 256 for Access

So instead of opening the csv file you could import it  in Excel, select the fields you want and skip the ones you dont want and then save the result as csv as well

Regards
0
 

Author Closing Comment

by:zself
ID: 40452319
Wow! Thank you for the quick responses. I was about to begin the arduous VBA trek when the Excel suggestion came in. That's what I will do. Thanks to all who took the time to consider.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

by:slubek
ID: 40452348
Remove unneeded columns from your input file with Powershell:
Import-Csv .\input.csv | select column1name, column2name, column3name | Export-Csv output.csv -NoTypeInformation

Open in new window

The import to Access resulted output.csv file.
0
 
LVL 57
ID: 40452546
<< I was about to begin the arduous VBA trek >>

I would just comment that the VBA trek is not all that arduous as you might think.  In fact it's a pretty simple matter and I do it all the time.

Not sure if your doing this as a one time thing or not, but the Powershell script to edit the file I like a lot.  I'm not sure though I'd bother to fire up an instance of Excel just to get the file imported.  In terms of process, it seems like over kill just to get around the 255 field limit in Access.  But if it is a one time thing, then there's nothing wrong with that.

Jim.
0
 

Author Comment

by:zself
ID: 40452559
Thanks. Good point. I may go back to it because at the end of the day, I need to create extracts based on a field and distribute it to 18 different people. If that makes sense. I don't want all 18 teams to see the other team's data. so running a script to extract only what I need and generate a new file would be slick.
0
 
LVL 57
ID: 40452932
BTW, one thing I should have added is that:

a. You can link to a file, read it like a table and process.

b. Use the TransferText action in VBA to pull the file into a table, then parse it out from there.

I've found limitations with both however and generally like to use the VBA route because of that.

Jim.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now