TransferText Import Tab Delimited

[Jen123]

Hi,

I need to create an automated process to import tab delimited files. The problem I've got is that they are all in different formats i.e different columns in different places etc. I've tried using TransferText but it doesn't seem powerful enough? The text files that I am importing from have columns headings in the first row and I want these titles in my new table column headings. Any suggestions?

 

[KenReay]

Hi

You should be able to do that with docmd.transfertext, are you sure you have set teh appropriate parameters to denote that first row contains column headings, and that it is to import to a new table ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Jen123]

Yes I have. The problem I have however, I'm having to use a specification file, otherwise all the data transfers into one column and because the text files are all different, I can't name the columns in the specification file.

 

[KenReay]

Hi

OK, I see the problem, to tell it to use tab as delimiter you must use an Import Specification,

Could you replace tab with Comma ?

Or

Import into single column table (ie do not use Import Specification), then parse the table produced to extract the columns ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Jen123]

How would I parse the table then?

 

[KenReay]

Hi

Use Instr() to search ot the tabs (chr(9)) and Left(), Mid(), right() to break it up into fields

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PJStephenson]

If the Column Headings of the txt file correspond to the field names of the table you want to import it to you could try something like this:

set rstTable as currentdb.recordset("tblTable&quot

open "C:\Importfile.txt" for Import as 1
line input #1, strTitles
strArrTitles = split(strTitles,chr(9))

with rstTable
while not eof(1)
Line Input #1, strBody
strArrBody = split(strBody,chr(9))
.addnew
for LoopCount = 0 to Ubound(strArrBody)
.Feild(strArrTitles(LoopCount)) = strArrBody(LoopCount)
Next
.update
wend
.close
End With
Close #1

as long as you are using Access2000 or better then you can use Split(). There are probably Syntax errors in the code so check it carefully.
Good Luck

Peter