Importing csv without Specs

[kwvh]

I am importing a csv file into an Access table. I intentionally am not using a SpecificationName as the routine imports a couple of different csv files into the same table. Every column in each of the csv’s to be imported exists in the Access destination table, but not every field in the table exists in the csv file. This has not been a problem before.

The issue now is that I am importing a csv file with about 2000 lines. One column of the 2000 lines is mapped to a text field in the table. However, for some reason, it is taking the 5 out of 2000 that alpha and not numeric and bringing them in as blank. The alpha records are very near the bottom of the csv file. Does Access make assumptions, like Excel does, when importing text files? Is there a way around it without creating a Specification Name? I don’t want to create a Spec Name because I would need different spec for each file type.

Any ideas are greatly appreciated.

Ken

[HansV]

Yes, Access looks at the first rows only to determine the data types.

A trick I sometimes use is to add a dummy line immediately below the header line (or at the top if the .csv file doesn't contain a header line), with a non-blank value of the appropriate type in each column, e.g. 2 in each number column, "a" in each text column, 01/01/2000 in each date/time column.
This forces Access to interpret each column correctly.

After importing into Access, remove the dummy record.

[kwvh]

I guess I could insert a line via VBA as I don't need user poking around with a csv file.
I have not used automation with csv files. Thanks to you I do have experience with excel/access automation. Is it possible to insert a line into a csv file with VBA?
Thanks for your ideas.

[HansV]

You can use the Open, (Line) Input, Print and Close instructions to read and write text files, but you have to know already which fields you need, and you mentioned that the files would be different each time...

[kwvh]

Hans,

You are good with details. duh!
You are correct since the three files all have different formats. Guess I will need to play with some code checking a string in the file name to identify the files, then create Specs for each file, and import via the appropriate spec.

It would sure be nice if Access were to consider the destination table, and use the field specs in the destination table instead of trying to do it all itself.

THANKS for your help, Hans!

Respectfully,

Ken