unknow link specifications

  • Thread starter Thread starter CJ_DB
  • Start date Start date
C

CJ_DB

Folks, thank you in advance for your help.
I have a directory of tab delimited text data files I would like to link to
my database (programmiclly) and do some processing(programmiclly). Each text
file has a header row of field names and then the data. I have no way of
wading thru the hundreds of files and using the link wizard to manually
create a link/import specification. I need all the data types to be text. I
have used Text;;FMT=Delimited;HDR=Yes;IMEX=1;CharacterSet=437;DATABASE=xxx.md
which gives me only 1 unseparted field of data. Same for DoCmd.TransferText
acLinkDelim,xxx,xxx,xxx
The big problem is creating the link specification on the fly. Thanks for
any help.
 
Do you need all of the files linked at once?

If not, why not create a single link specification. You could then rename
each file to the appropriate name, link it, process it, unlink it, then
rename the file back.
 
Doug, Thanks for replying.
I do not need them all at one but the problem is the single link
specification. Each file has a unique set of data fields that I exam and
process individually. I do not know the number of fields nor the names until
I do the processing but I can not process until I create the linked/imported
table.
i.e table1---- fldAN, KUP, SAP, JJJ
table2 --- xxx, yyy, sup, sap
etc.
 
You may have to read the files in using the Line Input # statement and parse
them yourself in VBA.
 
Doug, the files are all tab separated TSV. I changed one file to CSV and the
statement: Text;;FMT=Delimited;HDR=Yes;IMEX=1;CharacterSet=437;DATABASE=xxx.md
works. Do you know of a way to have the format FMT=TabDelimited? I tried
that FMT=TabDelimited ; FMT=Delimited(" & vbTab & ") and
FMT=Delimited("<delimiter>") as suggestion from a web search but they had no
effect unlike the CSV which works completely. I could write a parser but it
seems like there should be an easy way to create a link/import specification
 
The article and others say that Format=TabDelimited should work but the
link still comes back to only 1 field. I have also used the
Format=Delimited(vbTab) and chr(9). These do not work thus far. Any other
suggestions.
 
You seem to have already rejected my other suggestion, which was to use VBA
to import the data.
 
Doug,

Thanks for your help. I created a parser for the 1 field import. I still
wonder why the Format=TabDelimited does not work in a connect string as does
the default Format=Delimited(which assumes comma). Even the article you
suggested say that it should work but alas no go.

Thanks again.
 
Back
Top