import delimited text via code?

[SirPoonga]

I have a pipe '|' delimited file that I want to import into an existing
table via VBA. How do I do that?

 

[Ken Snell [MVP]]

DoCmd.TransferText

It's discussed in Help file.

 

[Van T. Dinh]

In addition, you probably need to create / save an Import Spec (manually)
before you can use TransferText in code so that Access recognise the pipe
character | as the Field delimiter.

 

[SirPoonga]

I read that, unfortunately I'd have to be able to make a spec
dynamically. Can I do that?

The table is generated dynamically so I don't think I can make a spec
ahead of time.

 

[Mike Painter]

I read that, unfortunately I'd have to be able to make a spec
dynamically. Can I do that?

The table is generated dynamically so I don't think I can make a spec
ahead of time.

If the table is different each time then you will probably have to parse it
in code.
By different I mean a different number of columns or columns not always
haveing the same meaning.

If it is not different then it is just a mattter of creating the spec and
supplying the file name and location.

 

[Jim Sizelove]

I read that, unfortunately I'd have to be able to make a spec
dynamically. Can I do that?

The table is generated dynamically so I don't think I can make a spec
ahead of time.

As Mike points out, you should only need to make the spec dynamically if
the columns change frequently. If that is the case, here are a couple
links to Microsoft's knowledgebase that show how to dynamically create a
Schema.ini file and use it when importing:

http://support.microsoft.com/default.aspx?scid=kb;en-us;155512
http://support.microsoft.com/default.aspx?scid=kb;en-us;241477

If the columns aren't changing very often, it will be simpler to create
a spec with the Import Text wizard.

HTH,
Jim Sizelove