TransferText imports field data as #NAME? from CSV text file when data starts with a plus sign +

[Poduska]

When I use an import specification (the field is TEXT) and import from a CSV file I receive a value of #NAME? for fields with values starting with a "+".

Why would TransferText not be able to handle text starting with a + ?

Any ideas?

This process has been working correctly for several years until this particular piece of data appeared starting with the Plus sign.

60,000 lines import fine except 25 which possess this quirky data.

The funny thing is I can manually copy and paste the row from my CSV file with the field starting with a plus sign and paste it in to my table and it pastes FINE!

Field is defined as TEXT in the import specification and the destination field is TEXT with field size of 255.

[COLOR=red][b]
For example.[/b]
[u]CSV SOURCE field      Result in Table[/u][b][/b][/color]
PF-LOT                     PF-LOT                 
+PF-LOT                    #NAME?

I just tested this from the Immediate window and received the same errors with the fields starting with a Plus sign.

DoCmd.TransferText acImportDelim, "mportSpecification", "DESTINATIONTABLE", "\SOURCEFILE.csv", True, ""

 

[SkipVought]

hi,

This is Excel being "helpful!"

It is one of several examples where Excel 'examines' the STRING and makes some assumptions, and, based on those assumptions, may CONVERT your data in some way.

BTW, your PASTE only works as you expect, if you FORMAT the receiving COLUMN as TEXT, before you do the paste.

I would normally recommend IMPORTING (rather than OPENing) the .csv using the Data > Get External Data > Text Files feature, where you can control the field format of each column, but the PLUS sign is a killer!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Well I spoke too soon.

Using the DATA > IMPORT feature, I simply change the CHARACTER SET to Windows (ANSI) and it imported as expected...
[tt]
CSV SOURCE field
PF-LOT
+PF-LOT
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Poduska]

Thanks Skip
However in Access 2007 I am utilizing the Import Specification window and Windows (ANSI) is not an option. I am using this as the original file is 255 fields and I am only importing about 35 fields.
I will try other CHARACTER SET options within the import specification.

 

[SkipVought]

It has NOTHING to do with the Export Characterset!!!

This is IN Excel Data Import feature, to IMPORT from a .csv. THAT is how it can be accomplished.

You can also do it be Querying your MS Access table directly IN EXCEL, as long as the receiving column is PRE FORMATTED as TEXT (one time) prior to executing the query or REFRESHING the query IN EXCEL.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Poduska]

Sorry if I wasn't clear, I realize it how hard it is to help others without being there!

Excel is not involved at all, I have users who do a download from a system of which access cannot read, and the source program creates a CSV file and saves it on our network. I then go and programmatically read 4 CSV files into an Access table using the TransferText and an Import Specification to reduce the number of fields I import.

Unfortunately, MS did not make the import specification window very user friendly.

I Suppose I could attempt to first save the CSV files into Excel if this will "play nice" and then into my access table..... Hmmmm. This will take a bit of time to try but one never knows.

thanks for your insight.

 

[Poduska]

Rats, Excel also reads +PF-LOT as #NAME? so I cannot read from Excel.

Funny a text editor reads the CSV file just fine.

And it started out such a good day this morning!

I'll keep trying.

 

[MajP]

I think there are a couple things to try. I could at first not recreate this so I could not verify the fix.
1) If I open in notepad a CSV when I select save as I can change it to unicode from UTF-8 or other endcoding. It is an option at the bottom. Try changing the encoding to unicode. When I did that it seemed to work.
2) Using the import text wizard there is an advanced button to create the import spec. This also allows me to pick the code page. Again it appears the default is UTF-8 but can be changed. I believe you need unicode
or
3)The last parameter of transfertext is a long code page constant. I cannot find these values anywhere. I think I saw that you can try 1200, or 6500.