import a line delimited file into an access table

[Bill Voorhees]

I have a line delimited txt file that contains fields for 500 records
containing 3 fields each

a1
a2
a3
b1
b2
b3
..
..
aac1
aac2
aac3

I need to import that txt file into an access table defined with 3 fields
(field1, field2, field3)

Is there an easy way to do this or should I use a macro or vba?

Thanks for any help.

Bill V.

 

[Perry]

I'm afraid y'll have to reformat yr textfile using a bit of code...
something like

Whereby:
- InputFile.txt (offending textfile with sequential entries)
- BetterInputFile.txt (reformatted textfile using semicolon as delimiter)
- Table1 (table with 3 fields)
- IS_Spec (existing import specific. using semicolon as delimiter)

'Open offending textfile
Open "d:\test\InputFile.txt" _
For Input As #1

sTmp = Input(LOF(1), 1) '< read file in one go
Close #1

aTmp = Split(sTmp, vbCrLf)
sTmp = ""

'reformatting
Open "d:\test\BetterInputFile.txt" _
For Output As #1

For x = LBound(aTmp) To UBound(aTmp)
If x Mod 3 = 0 And x > 0 Then
Print #1, Mid(sTmp, 2)
sTmp = ""
End If
sTmp = sTmp & ";" & aTmp(x)
Next
Close #1

'importing in Access - Table1
DoCmd.TransferText acImportDelim, _
"IS_Spec", _
"Table1", _
"d:\test\BetterTest.txt", 0


Krgrds,
Perry