How to transferText with acImport Dinamicaly?

[natatbh]

Dear All,

I am using docmd.TransferText to import the data from a .txt
file into a msAccess table. The import specification specifies
17 fields to be imported. The problem is that sometimes I want
to import more than 17 fields and sometimes not. I do not know in advance what amount of fields I will get. If I have 17 fields specified in the specification it will only transfer the first 17 fields. If I don't specify an import specification in the docmd.TransferText function then it imports all fields but it puts them in one big field in the msaccess table. How can I dinamically import any amount of fields from a .txt file into a table and have them in seperate fields in the table.

Thanks,

Nathan

 

[grnzbra]

What tells you that you want more than 17 fields? When you get more than 17 fields, does the spec change other than adding more fields. (ie do the field sizes change?)

 

[natatbh]

Thanks grnzbra,

No, the spec doesn't change other than the fact that more
fields have to be added.

The .txt file that I import to msAccess is sent to me by someone else. The file can contain 17 fields and sometimes more than that. However, I don't know beforehand how many fields I will get.

Nathan

 

[grnzbra]

You always get a minimum of 17 fields? When more are added, are they always the same size as the last time they were sent?

A kind of klunky way of doing it would be to set up a series of specs, one for each possible length. Then, import the file without a spec, read the first record and, based on its length, pick the corresponding spec and import it again with that spec. Then delete the first imported table.

What happens if you try to import with a spec containing more fields than are present in the record?

 

[natatbh]

Thanks,

I see what your'e saying about creating several specs.

Yes my minimum is 17 fields. If I import a file with more than
17 fields using this spec it imports only the first 17 fields
and gives them the 17 names defiened in the spec. Let's say 3 fields are added in the .txt file so there are now 20 fields.
And the new fields are in columns 15, 16 and 17, then fields 18, 19 and 20 will be left out and fields 15, 16 and 17 will be named with the names set in the spec for 15, 16 and 17 which are now really fields 18,19 and 20 because they were pushed further in the file by the addition of the 3 new fields.

I hope your'e getting the picture.

The idea of creating several specs will run into messy stuff
for me.
If you have any other ideas please let me know.

Thanks,
Nathan

 

[grnzbra]

So the spec does change.
How do you know where the 3 extra fields are located in the original 17? If you get 3 extra fields, are they always the same 3 fields located in the same place?