Importing a CSV file that contains trailing spaces - converts it to “á” special char

[dowlingm815]

I need to prevent access from importing a CSV file that contains a field with trailing spaces that gets converted into a special character, such as “á.” The import specification file requires all fields to be imported as text. The import statement is straight forward and doesn’t cause any issues, which is listed below.

DoCmd.TransferText acImportDelim, "SO_Data Import Specification", "SO_Data", "\\nbpcafile01.core.umdnj.edu\CA\Purchasing\Shared\SCM IT\eProcurement\MarketPlace Data\data\SO_Data.csv", True

Any suggestions would be greatly appreciated.

[NeoPa]

DowlingM815:
I need to prevent access from importing a CSV file that contains

Do you really mean you want to prevent the import, or do you mean prevent the data from being converted to special characters?

Whichever, you need to post some example data and explain clearly what the results are. What you say so far is pretty unclear.

[dowlingm815]

The data needs to be imported as its original CSV value. For example, in the CSV file, the product description field contains the following value:

"SCRUBBRUSH SURG W/ CHLORHEX GLU "

When it is imported into Access, the field is converted into:

"SCRUBBRUSH SURG W/ CHLORHEX GLUá"

[NeoPa]

Strange. I can only guess that what appears to be a space when you look at it, is in fact another character that simply shows as a space in whatever software you're using to look at it. Could you post it in a ZIP file as an attachment for me to look at in detail. Multiple lines would be much better than a single one, but the whole file isn't necessary if you want or need to reduce the size.

Attach Database (or other work) may help. Ignore anything about databases as this wouldn't be of course.

[dowlingm815]

Attached is the file, that doesn't contain many records, it has 4470. The Product Description is the field that contains four records for today's run with the last character equal to "á". The fields will be equal to when imported into access:

SCRUBBRUSH SURG W/ CHLORHEX GLUá
SCRUBBRUSH SURG W/ CHLORHEX GLUá
SCRUBBRUSH SURG W/ CHLORHEX GLUá
CABLE PATIENT MONITOR LNCS - LNC-10á

I am truly grateful for your assistance.

[NeoPa]

While many instances of the text posted were fine, I did find a number of occurrences where the hex character A0 (á) was found. This indicates that the data is already there in your data, and whatever you were looking at it with is simply hiding that from you. I suspect if you find and remove these characters from your data you will find that it all imports properly.

If that works, you are just left with the jobs of finding why the characters are put into the data in the first place and stopping them in future.

Let us know how you get on.

[dowlingm815]

The data is exported from a web-base app. the web users appear to be place a space at the end of the field at times. therefore, when it is exported the value is in the CSV file. honestly, i believe the web developers need to look at their data to populate the export field correctly. would you agree?

[NeoPa]

DowlingM:
would you agree?

Probably, but I don't have enough information to know for sure. Certainly I would expect the web devs to ensure that non-(ASCII standard) data is not included.