Access 2010 Import Issue: Comma problem

Question

In Access 2010, I'm trying to import a tab delimited file (either csv or txt format) and when one of the fields is numeric with a comma (e.g. 1,234 not 1234) that value will not import, it gets nulled out.  This problem occurs with minimal rows and columns, and a field formatted as long integer or double.  I cannot alter the import file to remove the comma.  Any suggestions to load the file as is?

Answer 1

Yup, I can confirm this bug. This only shows up for me when importing into an existing table that was created in Access 2010 (2003 tables are not affected). 

Instead of writing custom code to work around it, I found a much easier way.  Import the data into a new table, setting the proper data types in the import process.  Formatted numbers will be imported correctly.  Then make an append query to add to the table you wanted the data actually imported into in the first place.

Our shop uses access extensively to reformat and combine data into many reports.  This is the fastest way we have found to work around the regression.

Answer 2

In Access 2010, I'm trying to import a tab delimited file (either csv or txt format) and when one of the fields is numeric with a comma (e.g. 1,234 not 1234) that value will not import, it gets nulled out.  This problem occurs with minimal rows and columns, and a field formatted as long integer or double.  I cannot alter the import file to remove the comma.  Any suggestions to load the file as is?

Import the field as text.  Then use the CLNG function in an access query to convert it to an integer.

Answer 3

I had the same problem. I installed "access developer extensions 2010". If I desinstall it I can now import files with numeric numbers.

I don't know if there is something to correct this issue.

Have a nice day.

Answer 4

You could write VBA code to read the text file line by line, parse each line into fields and add them to a table.