Solved

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

Posted on 2014-11-19
8
884 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 58
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 48

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 51

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Industry Leaders: 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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

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