Importing a delimited text file using VBA

[ecawilkinson]

hi,

I knw how to import a delimited text file in VBA if I know in advance what the delimiter will be. The reason you have to know in advance is that you have to save the specification so that you can then use it in the

Docmd.TransferText acImportDelim, SavedSpecName

command. So, for instance, if you know you will be importing a file that is comma-delimited, then you can save a specification that contains that information. My problem is that I am not going to know in advance what the delimiter will be. The only thing I can think of is to anticipate as many possibilities as I can and save them all as separate specifications. This just does not seem very satisfactory to me, so can anyone suggest a better way?

I thought I could solve it if I could work out how to save a specification using VBA but I can't work out how to do that.

Thanks in advance,
Chris

 

[namliam]

You can import the file(s) as "fixed width" into a staging table then do the spliting "manually" in code depending on the parameters entered by the user.

 

[gemma-the-husky]

if you dont know what the separator is you are clearly going to struggle

there is a limit to what you can automate ...

however there is no reason why you cant have several alternative importspecs and pick the right one manually - ie csv, tab separated etc

 

[ecawilkinson]

Thanks for your help, I think I'll have to stick with multiple import specifications, as, if I used a staging table, I would lose the data types of the fields which for my purposes is important. The staging table idea is interesting, though, and could work in different circumstances.

Chris

 

[namliam]

The point of different layouts is that you may not know the order of and/or field layout either... so one can only be " that flexible " Just like Gemma said...

You can prefix 1000 layouts and still get caught by # 1001....

 

[MatMac]

You could always settle for one specific delimitter in your import spec. Then, when your files arrive, simnply use a text editor to search for the included delimitter, and replace it with your chosen one before importing to Access.

Maybe you could even do it in Access automatically - though I haven't tried. Import your entire text file into a holding table of single field records, then run a search/replace routine to change the delimitters?