MS Access import from Text File

I believe your trouble may have to do with the field names in both your INSERT and SELECT statements. They must be enclosed in square brackets if the field names contain spaces, for example:

SELECT Amazon_Export.Date, Amazon_Export.Order ID, Amazon_Export.SKU, Amazon_Export.[Transaction type], Amazon_Export.[Payment Type] etc etc

That said, I'd suggest you use TransferText to move the incoming data to a "staging" table, and then use standard VBA/SQL to review the data and then move it to your Amazon_Export table. Importing data without first validating it can result in invalid/errant data.

DoCmd.TransferText acImportFixed, , "YourStagingTable", Me.txtFullPath, True

This would put the results in a table named "YourStagingTable" (obviously you can change that to fit your own needs). From there, you could then simply INSERT rows, after validating:

'/ do your validation, then use the SQL below to move the records to your live table:
Currentdb.Execute "INSERT INTO Amazon_Export (Date, Order ID, SKU, Transaction type, Payment Type, Payment Detail, Amount, Quantity, Product Title) SELECT [Date], Order ID, SKU, [Transaction type], [Payment Type], [Payment Detail], [Amount], [Quantity], [Product Title] FROM YourStagingTable"

I went ahead and created a "Staging Table" called "Amazon_"Staging_Tbl" but when I try to upload the text file to the table I receive an error message. Attached is a copy of the Datbase, I receive Microsoft Access can't find the field '|1' referred to in your expression message

Private Sub cmdAppend_Click()
'DoCmd.TransferText acImportFixed, Amazon_Staging_Tbl, Me.txtFullpath, True
DoCmd.TransferText acImportDelim, , [Amazon_Staging_Tbl], Me.txtFullpath, True

' ====================================================================================================================================
'Dim aSQL As String
'If Len(Trim(Me.txtFullpath) & "") > 0 Then
'    aSQL = "INSERT INTO Amazon_Export ([Date],[Order ID],[SKU],[Transaction type],[Payment Type],[Payment Detail],[Amount],[Quantity],[Product Title]) "
'    aSQL = aSQL & " SELECT Amazon_Export.[Date],Amazon_Export.[Order ID],Amazon_Export.[SKU],Amazon_Export.[Transaction type],Amazon_Export.[Payment Type],Amazon_Export.[Payment Detail],Amazon_Export.[Amount],Amazon_Export.[Quantity],Amazon_Export.[Product Title]"
'    aSQL = aSQL & " FROM ItemsReceived IN '" & Me.txtFullpath & "';"
'    CurrentDb.Execute aSQL
'    Beep
'MsgBox "Your data has been imported successfully" & vbCrLf & "", vbInformation, "Data import complete"
'DoCmd.Close
'Else
'    MsgBox "Please select a file"
'    Exit Sub
'End If
End Sub

Select allOpen in new window

DoCmd.TransferText acImportDelim, , [Amazon_Staging_Tbl], Me.txtFullpath, True

the line above should be:

DoCmd.TransferText acImportDelim, , "Amazon_Staging_Tbl", Me.txtFullpath, True

The problem with importing without an import specification is that it allows Access to determine the data type of the fields as they are imported.  It does this by looking at the values in something like the first 25 rows of data to determine and determines the "best" appropriate data type based on the values it finds.  You can improve on that by using an Import Specification (click the advanced button in the bottom left portion of the import wizard).

When I do this, I generally set all of the fields to be text, so that no matter what the data looks like, it imports as text.  Then, as Scott mentions, I conduct some error checking by running queries against each of the columns.  Examples:

For columns which should be numeric, I  run a query and identify the rows where the values in that field are not numeric, something like:

strCriteria = "([Field1] IS NOT NULL) AND (IsNumeric([Field1]) = False)
if DCount("*", "stagingtable", strCriteria) = 0 then
'  Do something here
end if

Select allOpen in new window

A lot of times, I will provide a method for them to either correct the error, or to mark the record to prevent it from being imported into the destination table, when the time comes.

When done with the error checking, you need to write an append query which will translate the strings into the appropriate data types.  Something like:

INSERT INTO tblDestination (Field1, Field2, Field3, ..., FieldN)
SELECT Field1, CLNG([Field2]), Cdbl([Field3]), ..., [FieldN]
FROM stagingTable

This will ensure the proper data type translation in your destination table.

I don't seem to get this to work correctly, I updated the code to the one below but it is not telling me that the field don't exist in the destination table.

Private Sub cmdAppend_Click()

DoCmd.TransferText acImportDelim, , "Amazon_Staging_Tbl", Me.txtFullpath, True

End Sub

Select allOpen in new window

I'm attaching the Text file and the revised database, can you help me identify why this is not working?
Amazon-Export-3.txt
Amazon-to-Xero-import.accdb

I see... Is it possible to simply transfer the contents of the file to the existing staging table? I have additional queries that need to based off of that table.

Rather than importing the file, you can link to it.  Using the linked table, you can run append queries to add rows to permanent tables.