Dollar amount fields parsing after comma

[jrothey]

Morning everyone,

I have a csv file that I'm trying to import that has cost values, e.g. 1,456. When I run an import from phpMyAdmin2.6.1 the data after the comma is stripped. If I view the csv with notepad the cost value has " " around it.

Below is the sql query results:
LOAD DATA LOCAL INFILE 'd:\\PHP\\uploadtemp\\php380D.tmp' INTO TABLE `usage_test` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

Inserted rows: 5477 (Query took 0.0716 sec)

I also tried running the import from the CLI, but recieve the same results.

Any suggestions or has someone seen this before? I checked the forum, but don't find anything specifically relating to the same scenario.

 

[johnwm]

Looks like you are importing string (text) values into a numeric field.

... FIELDS TERMINATED BY ',' ...

Numeric fields don't have commas in them and you are marking the field as being terminated by a comma. CSV stands for Comma Separated Values, so you are specifying that 1,456 is 2 separate values.

Solution - strip the commas inside the individual fields in the CSV before importing

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints