Importing TextFiles 1

[Squibbles]

Hi,
I am trying to import a textfile into Access (using VBA, TransferText). The file has over 400 columns, but I only want to import a selection of columns.

Also the first line has column headers and the second line is blank (and so i do not want to import it).

How can I do this using VBA?

Kind Regards,

Squibs

 

[AlanJordan]

In my experience, the best way to do something like this is to import the text file manually using the import wizard. (File > Get External Data > Import. Select *.txt files as the type of file to import.

You'll have to use the Advanced Settings. That will allow you to import only certain fields. After you do this, you can click on the save button. Name the "Specification".

Then, later you can reference this specification in VBA code. Probably the best way is to use the TransferText method.

I hope this helps.

Alan

 

[Squibbles]

Unfortunetly Access will only allow 255 fields.
So when specifying manually, i can only choose to skip columns in the first 255 columns, the rest of the columns are simply ignored.

I've been toying with the idea of streaming the data into a new text file, keeping the columns I want, and then importing the new text file via TransferText.
But i don't know how to skip a column using the TextStream object.

Any ideas?

 

[PHV]

How are the columns, delimited or fixed width ?
If delimited, play with the Split function.
If fixed, play with the Mid function.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Squibbles]

they are comma delimited...
Split function?

Whats that? Any chance you being able to knock up some sample code please?

Cheers,

Squibs

 

[PHV]

You need ac2k or above.
Even better if you have the VBA help installed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Squibbles]

I am using Access 2002,
Just playing with the split function now.
Should I read all the text file in, and use the split function. and then stream the relavent columns into a new text file?

Or is there a simplar method?

Thanks for hints!

Squibs

 

[PHV]

I guess you have to use the Split function on each ReadLine result.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886