Solved Importing text file with number comma causes type conversion error

[zhibin1993]

Hi, I have a text file with 'Quantity' that consist of comma.

I am trying to import it into MS Access as text file. But it always throw 'Type conversion error'.
For the Quantity field on the table i have tried different variation such as text, int, number with standard format but to no avail.

Does anyone have any workaround or solution for this?
I do not want to open the text file and remove the comma.

 

[arnelgp]

it is because the Quantity Column is Text.
you Import it into a Temporary table with Text Quantity column.
then Insert it to the final table converting Text to any number format.

 

[zhibin1993]

it is because the Quantity Column is Text.
you Import it into a Temporary table with Text Quantity column.
then Insert it to the final table converting Text to any number format.

I have tried importing it as a text quantity column and number quantity column but both have the same error.

 

[June7]

Defining as text works for me.

Otherwise, scrub the file - remove commas.

 

[zhibin1993]

Defining as text works for me.

Otherwise, scrub the file - remove commas.

Can try out with this 2 file. Currently defined as text, import as text and first row contain header

 

[June7]

Ooops, I was importing to new table, not existing.

So again options:

1. import to new 'temp' table and then move records to permanent table

2. scrub the text file

3. VBA text file manipulation - read the file and parse one line at a time

 

[sonic8]

So again options:

1. [...]

Seriously?
The most sensible option, which should be top of this list, is to configure the import specification to treat the comma as decimal separator.

 

[June7]

I guess that would be sensible in some countries but since I am in the U.S., had not occurred to me. It works. Data imports to number type field but without the zeros. If want them back, run an UPDATE action that multiplies by 1000 - restricted to the newly imported records.

 

[sonic8]

Well, I have to admit, I missed that the file contains mixed number formats. Some with a comma as decimal separator and some with a dot.
That complicates matters significantly. Under these circumstances there are no alternatives than to used VBA to convert the numbers to a consistent format, either before the import in the file, or after the import in a temp table, before the data is inserted into the final target table.

However, the real, non-technical solution would be to tell the people creating these files to get their act together and use consistent number formats throughout any one file they create.

 

[sonic8]

Under these circumstances there are no alternatives than to used VBA to convert the numbers to a consistent format, [...]

Well, there is one.
Do a multi-step import.
1.) Import all columns with comma decimal separator into one (temp-)table.
2.) Import all columns with dot decimal separator into another (temp-)table.
3.) Run an append query joining both temp tables to write the data into the production table.

This approach does not need VBA to fix the data. So, it might be more compelling to someone not familiar with it.

 

[zhibin1993]

Hi everyone, thanks for helping on this issue. I found a temporary solution to import with first row as header unchecked. I am then able to import with the quantity successfully.

Then create a delete query to remove that header row from table.