MS Access menu import not same as TransferText

[bwlee]

Hi All,

I'm attempting to automate the import of .txt files into Access.

When I use the File > Get External Data > Import function and select my import specification, the data is imported correctly and in order.

When I use the TransferText macro, and specify all the same paramenters i.e. fixed width, import specification, file path, table name etc, the data is imported; however, it is 'jumbled.' It is not in the same order as the text file (does not import the same way as when I do it manually).

It is critical that the data is imported in the correct order for it to be useful.

Has anyone ever encountered this issue before? Can someone provide some insights?

Many thanks!!!

[MMcCarthy]

Hi All,

I'm attempting to automate the import of .txt files into Access.

When I use the File > Get External Data > Import function and select my import specification, the data is imported correctly and in order.

When I use the TransferText macro, and specify all the same paramenters i.e. fixed width, import specification, file path, table name etc, the data is imported; however, it is 'jumbled.' It is not in the same order as the text file (does not import the same way as when I do it manually).

It is critical that the data is imported in the correct order for it to be useful.

Has anyone ever encountered this issue before? Can someone provide some insights?

Many thanks!!!

Check your specification file. There may be some problems with it.

[bwlee]

Check your specification file. There may be some problems with it.

I use the same specification file in TransferText as I do using Menu > import...

[MMcCarthy]

I use the same specification file in TransferText as I do using Menu > import...

Check it anyway.

[bwlee]

Check it anyway.

I've been banging my head against the wall for the past 24 hrs because of this problem. This was seemingly a straightforward task. I've checked and double checked the import specification. As I mentioned before, I use the same one when I import manually as I do for the TransferText macro. Still not sure why the automation method doesn't work properly...

[MMcCarthy]

Honestly not sure what is going on here. I've never had this problem. Usually though I use VBA to import text files. You can use code something like the following. It gives better control anyway.

Function ImportTextFile()
Dim fhandle1 As Integer
Dim fline As String
Dim fPath As String
Dim fld1 As Long
Dim fld2 As Date
Dim fld3 As String
Dim strSQL As String
 
    fpath = "Full path of file.txt"
    ' open the text file to be read
    fhandle1 = FreeFile()
    Open fpath For Input Access Read Lock Write As #fhandle1   
 
    Do While Not EOF(fhandle1) ' loop until you reach the end of the file.
 
        Line Input #fhandle1, fline       
 
        fld1 = Left(fline, 5) 
        fld2 = Mid(fline, 6, 10) 
        fld3 = Left(fline, 16, 30) 
 
        strSQL = "INSERT INTO TableName (Field1, Field2, Field3) " & _
           "VALUES(" & fld1 & ", #" & fld2 "#, '" & fld3 & "')"
        DoCmd.RunSQL strSQL
    Loop
 
    Close #fhandle1
 
End Function
 

Mary