Issue with transfertext (1 Viewer)

ionisz

New member
Local time
Today, 02:33
Joined
May 10, 2019
Messages
8
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. :mad: 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 :eek: 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

I’m here to help
Staff member
Local time
Yesterday, 23:33
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 02:33
Joined
May 10, 2019
Messages
8
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

I’m here to help
Staff member
Local time
Yesterday, 23:33
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 02:33
Joined
May 10, 2019
Messages
8
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

I’m here to help
Staff member
Local time
Yesterday, 23:33
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 02:33
Joined
May 10, 2019
Messages
8
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

Registered User.
Local time
Today, 06:33
Joined
Jun 3, 2008
Messages
40
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

Super Moderator
Staff member
Local time
Today, 06:33
Joined
Sep 12, 2006
Messages
15,614
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

Registered User.
Local time
Today, 06:33
Joined
Jun 3, 2008
Messages
40
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

Super Moderator
Staff member
Local time
Today, 06:33
Joined
Sep 12, 2006
Messages
15,614
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

Registered User.
Local time
Today, 06:33
Joined
Jun 3, 2008
Messages
40
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

New member
Local time
Today, 02:33
Joined
May 10, 2019
Messages
8
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.
 

Users who are viewing this thread

Top Bottom