Transfertext import text file - pls help!!! 1

[RichM1]

I've been working for days on trying to import a simple text file which is in the format below (16 columns, each separated by a

MF;;Admin;;;21152;SB WCTR LAB;11/07/04;21:44:44;1;;;;;;0

I've tried the following command which doesn't work because this looks for commas as a delimiter. This code when ran places everying into one cell (I guess I could work with this but seems like a lot of work). I used to have a Spec file in my mdb but now that it's an adp the spec file will NOT work (I can't even create a spec file - the save as option is available).

DoCmd.TransferText acImportDelim, , "Upload", PortPath & "\UPLOAD.TXT", False

Can anyone help??? I've tried stuff like:

lngHandle = FreeFile()
FileName = PortPath & "\UPLOAD.TXT"
Delimiter = ";"
Open FileName For Input As lngHandle

Not sure where to go after this....

 

[RichM1]

Can anyone help me with this?

 

[payback]

Perhaps this might assist with some modification. I wrote this to import fixed-length text files therefore it uses specified string lengths. Modify it to use a looping character search or InStr function (have not tried either but I am sure it can be done) to suit your application.


fp = FreeFile()
Open strFileName For Input As fp

While Not EOF(fp)
strline = ""
strchar = " "
bolend = False
While Not EOF(fp) And Not bolend
If Asc(strchar) <> 10 And Asc(strchar) <> 13 Then
strchar = Input(1, #fp)
If Not EOF(fp) Then
If Asc(strchar) <> 10 And Asc(strchar) <> 13 Then
strline = strline & strchar
End If
End If
Else
bolend = True
End If
Wend
If Len(strline) = 0 Then GoTo Nextline

rst.AddNew

tst = (Nz(Trim(Mid(strline, 1, 11))))
If Len(tst) = 0 Then
rst!ListID = Null
Else
rst!ListID = tst
End If

tst = (Nz(Trim(Mid(strline, 12, 40))))
If Len(tst) = 0 Then
rst!ListName = Null
Else
rst!ListName = tst
End If

rst.Update
Nextline:
Wend

Close #fp

There are various traps to ensure that the fields are imported correctly in place. You of course will need to know the structures of both your text file and the table to import it into, but this code also lets you restructure as you import - very helpful at times.

Also if importing an identify field into an SQL table remember to use the dbSeeChanges option when opening the recordset.

 

[RichM1]

Thanks for all the help!