Issue with transfertext

[ionisz]

I have been using DoCmd.TransferText successfully for years to import a CSV file into an existing table. The CSV file comes from an (external) application. In the past, the external app developer has occasionally changed the output fields, which has caused my app to bomb. No problem. I go into my app and update the fields as required. However, for the past few months (2nd Q 2019) my application has bombed with error 2391 and a message that the field "" doesn't exist in destination table. When I compare the fields in the source CSV and my destination file, the fields line up. I'm wondering if MS introduced a bug in one of the recent updates to Access? Has anyone else experienced this? The code is really simple - "DoCmd.TransferText acImportDelim, , fN1, fN2, True".

Thanks for any help!

 

[theDBguy]

Hi. I am not aware of any, but it doesn't mean there wasn't any. One other thing to check is to see if there are any non-printing or invisible characters in the column headers or field names in the text file. Maybe Access is seeing an "extra" character in there somewhere and complaining about a no matching field. Just a thought...

 

[ionisz]

Good suggestion. I will test for that, but ... when I import manually it works fine, and if I link the table to the external CSV it works fine. So not hopeful, but good suggestion I will immediately test for.

 

[theDBguy]

Good suggestion. I will test for that, but ... when I import manually it works fine, and if I link the table to the external CSV it works fine. So not hopeful, but good suggestion I will immediately test for.

Also, how about maybe "extra" (empty) columns? This in reference to the error message you got with an empty field name.

 

[ionisz]

Interesting. The first 3 characters in the field name list have ascii values 239, 187, 191 which are, of course, all non-readable ascii values. Looks like you were right, theDBguy! Strange that manual imports and linked files had no problems with this! I've opened a ticked with the author of the external app I use to get the CSV and hopefully that fixes it. Thanks

 

[theDBguy]

Interesting. The first 3 characters in the field name list have ascii values 239, 187, 191 which are, of course, all non-readable ascii values. Looks like you were right, theDBguy! Strange that manual imports and linked files had no problems with this! I've opened a ticked with the author of the external app I use to get the CSV and hopefully that fixes it. Thanks

Hi. You're welcome. Glad to hear you found the problem. Good luck!

 

[ionisz]

An update as a FYI. It seems that the CSV specs have been extended to include a BOM or Byte Order Mark. This helps those with non English CSV files to import their files correctly. Unfortunately, the BOM requires that 3 hex characters be placed at the beginning of the file, to indicate if the file is UTF-8, UTF-16, etc. While most Office apps have been modified to handle this, Access hasn't been. So, I had to write a routine to strip out the first 3 non-ascii characters! More information on BOM here: https://en.wikipedia.org/wiki/Byte_order_mark

 

[laffeg]

Hi
got same issue with CSV file sent from vehicle tracking company who are being less than helpful.
can i ask if your 3rd party company were able to resolve, and if so do you know what had changed ?

thanks

 

[gemma-the-husky]

can you not import, ignoring the column headers. then the non-printing characters shouldn't matter. Of course this relies on the columns being in the same order as the destination table. Maybe this is why the hex tags are at the start of the column names

alternatively, re-write the file with the column headers cleaned up. (sounds like this is what you did)

 

[laffeg]

I've tried saying no to has fieldnames. works fine if you manually import and have the fieldnames as F1, F2 etc. if you then run transfertxt via VB i get "you cannot record your changes because a value you entered violates the settings defined for this table or list...

 

[gemma-the-husky]

I don't understand that error

If you select the option - field names = true, then Access will match the csv columns to the table, irrespective of the table order.

If you select the option - field names = false, then Access will insert the csv columns, assuming the table is in the sme order - so if it isn't some values will get rejected.

I can't understand the entire import failing. Can you post your transfertext command?

 

[laffeg]

hi code below - was previously using blnHasFieldnames variable but now just forced it to false

Dim strPathFile As String, strFile2 As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strpath = "s:\tracker reports"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tracker"

strFile2 = Dir(strpath & "*.csv")
Do While Len(strFile2) > 0

strPathFile = strpath & strFile2
DoCmd.TransferText , , strTable, strPathFile, False

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile2 = Dir()
Loop

 

[ionisz]

Hi
got same issue with CSV file sent from vehicle tracking company who are being less than helpful.
can i ask if your 3rd party company were able to resolve, and if so do you know what had changed ?

thanks

Read my reply above about BOM characters now being inserted. Easiest thing to do (until MS fixes Access the way they have Excel) is to rewrite the file while stripping out the first 3 non-visible characters. To do that use Readline to read the string from the the file, and then for each character test it's ASC value. Hope that helps.