MS Access import from Text File

I am working on a MS Access Database which would allow the user to upload a text file like the one attached using the form called "frmBrowserFedWeb" in the first form of the button the file is selected and then the Append button executes the following code. I have having issues getting the data to upload from the text file to the "Amazon Export" table.

Can you help me identify the error? I am attaching the text file and the Database I created so far.Amazon-Export-II.txt


Private Sub cmdAppend_Click()
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 If
End Sub

Private Sub cmdQuit_Click()
'    DoCmd.Quit
DoCmd.Close
End Sub

Open in new window

Amazon-to-Xero-import.accdb
joeserroneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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"
joeserroneAuthor Commented:
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

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DoCmd.TransferText acImportDelim, , [Amazon_Staging_Tbl], Me.txtFullpath, True

the line above should be:

DoCmd.TransferText acImportDelim, , "Amazon_Staging_Tbl", Me.txtFullpath, True
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
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

Open 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.
joeserroneAuthor Commented:
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

Open 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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'll need to do a few things:

1. Make sure your "staging" table does not already exist. The TransferText method will create it for you.

2. As Dale suggests, use a Specification to do the initial import. To do this with VBA, you'll first need to create the spec manually. To do that, use the External Data - Text file ribbon command, and import the file. When you do this, you can create and save the Specification. You would then refer to that Specification in your TransferText call. Looks like you may already have these in the database, but I'm not sure they're correct.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joeserroneAuthor Commented:
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.
PatHartmanCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.