import to access 2002 from csv file-number conversion problem

[sg]

Hi,
I have a csv file exported from our main system every month which contains
customer info and sales numbers. I have a table in access 'tb1', same fields
that csv file has. Sales column is set as number data type.
When I import csv file into 'tb1', if the number in csv file is 23,500.00 or
2,300 which is with comma ','. access will create an error table and won't
let me import those columns unless I change the format before importing. But
sometime the data is too big to be opened in excel. I need help on this.
Appreciate any idea,

Thanks,
sarah

 

[John Nurick]

Hi Sarah,

The problem is in the way your main system exports the data. By
definition, a csv file uses commas to separate the fields, so things
like this
23,500.00
and
2,300
are read as two separate fields
23 500.00
2 300
so Access will interpret the csv file as having more fields than your
table, which is an error.

If a field in a csv file contains a comma, it must be enclosed in quote
marks. So
"23,500.00"
is read as a single field - but the quote also define it as a text
field, not a numeric one. So that's no solution to the problem.

The best thing to do is to get the other system to export a well-formed
CSV file, without the commas within numbers.

 

[sg]

Hi John,

Thanks for your explaination. I will look for some solution for exporting
the original file.
Sarah