Importing several text files with different specs (2003)

Topic

New Reply

Hi,

Wondering…

I have a set of 11 textfiles coming in, which need to be imported into 11 distinct tables.

The text files have no headers
The text files may have different field separators and different data formats.

My problem:

I want to use VBA to import the text files. Do I really need to have (number of different formats) * 11 import specifications?

Being a simple Excel expert I thought perhaps I could pass the formats as a parameter to some import function that looks something like:

Private Function ImportTable(sTextFileName as String, sTargetTable as String, sFieldDelim as String, sDateFormat As String, bFourDigitYear as Boolean, bLeadingZeroes as Boolean)

But TransferText doesn’t allow this, or does it?

Question 2: If I save an import spec, Access insists on creating field names, if I try to import another text file (targetting same table, same # of fields), Access complains that the field names don’t match. WHat gives?

Reply | Quote

Replies

DoCmd.TransferText does not let you specify the delimiter etc.
You do need a separate import specification for each layout.
There is no support in the Access VBA object model for creating or modifying import/export specifications, but if you’re careful, you can manipulate them: they are stored in two system tables:
MSysIMEXSpecs contains a record for each specification.
MSysIMEXColumns contains a record for each column (field) in each specification.
You can use DAO or ADO to work with these tables.
If you want to see what they look like, (temporarily) tick the System Objects check box in the View tab of Tools | Options…

About question 2: do you have a mixture of text files with and without field names in the first row?

Reply | Quote

I cracked Q2 already, it is a matter of first opening the textfile, then defining and saving the import spec, then setting the target table, then stepping back through the wizard two times, clicking advanced again and lo and behold, Access has now updated the field names. Save import spec again and we’re done. Far from user friendly, nor as they call “Discoverable”. Duh.

I’ve also found a way using DAO and SQL in conjunction with a schema.ini file, but I cannot get that to work either.

I guess I’ll bite the bullet and start defining import specs for each of them. Tedious, but it should do the job.

Does anyone know of a tool that can help you manage import specs?

Reply | Quote

Some time ago unkamunka mentioned the free V-Tools collection. It includes an import/export specification editor/manager.

Reply | Quote

Excellent!!

BTW: Did you read this:

http://blogs.msdn.com/thirdoffive/archive/…ication-om.aspx%5B/url%5D

Reply | Quote

It’s nice that Access 2007 finally exposes import/export specifications in the object model! Better late than never…

Reply | Quote