Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1077
  • Last Modified:

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

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
zself
Asked:
zself
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Dale FyeCommented:
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
 
Rgonzo1971Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
zselfAuthor Commented:
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
 
slubekCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< 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
 
zselfAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now