Solved

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

Posted on 2014-11-19
8
762 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 49

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

770 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