Solved

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

Posted on 2014-11-19
8
832 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 50

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 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