Import selected columns from a CSV with 1412 fields

[zaqyu]

Hello Everyone,

I want to import selected fields/columns from a text file with 1412 fields into an Access 2007 DB but Access has a limitation to read only the first 255 columns. Is there a way or work around on this limitation. Any VBA code? Also another option is to use Excel make some macros or vba codes to manipulate the file to come up with a text file that contains only the columns i need in my Access DB.

Thank you very much for any help extended.

 

[Rabbie]

I think you will find that Excel has a max of 256 columns so I think you are going to have to edit the your file to remove the unwanted columns before you read it it into Access or Excel.

 

[DCrake]

Just to please me, and may bo a few other readers, what type of file contains records with over 1400 fields? It can't be Access, Don't know about SQL, it must be some proprietory database. Anyway I can't even think about a senario where I would need to record so many items of data about a single record.

That aside. You first need to establish how the text file is parsing the fields. Does the file come with field headings? What are the delimiters? is it Fixed width?

Which fields do you want and what are there ordinal position in the record.

Once you have established all that then you will need to use the Split() function to copy the row into an array and by matching the ordinal position in the record to the index in the array append the data item to the table. This would have to be done using Recordsets.

Not an easy task but it is achievable. However my first question would be can the originators of the text file not create an extract that matches your requirements. This would save you all the hassle.

David

 

[zaqyu]

Thank you for the replies. I'm sorry i cut short my specs. The SOURCE file is a CSV file with 1412 columns (No Headers), it's a proprietary file which is only available to subscribers, so requesting it to match my need is i think not possible. All the Columns in a record seems to have use but i just need 30 columns from each record which i will manipulate. I am considering to use VBA Text manipulation but this is CSV, it would be nice if it has been a fixed length text file but are there VBA TEXT functions if i manipulate directly the text file that allow me to determine/read a particular column in the text file using its comma separator? Thanks again

 

[DCrake]

I don't use them much but when using import specs you can determine which columns to import and which to skip. If you set up an import spec and specify this when you run the import it should only import the fields you require.

David

 

[zaqyu]

Well import specification has 255 field limitation, it should have been easy if the source CSV file columns is within that. thanks...