0

I have .csv file data like this:

"UPRR 38 PAN AM "M"","1"

and I loaded data into table using below command which is having two columns (a and b).

LOAD DATA LOCAL INFILE 'E:\monthly_data.csv'
INTO TABLE test_data_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

But when I select table, it's giving unexpected results which is shown below.

a contains:

UPRR 38 PAN AM "M","1

... and b is NULL.

Thanks

Bryan Ash
  • 4,385
  • 3
  • 41
  • 57
sagarpavan
  • 85
  • 2
  • 14
  • 1
    Maybe this http://stackoverflow.com/questions/17053530/load-data-from-csv-file-where-doublequote-was-used-as-the-escape-character can help? – favoretti May 06 '14 at 15:05

2 Answers2

2

You can replace all the instances of "Double quote double quote" in your file

either A. open the files and find replace them or B. make a script to open the files and replace the extra quote that is messing it up

Brad Larson
  • 170,088
  • 45
  • 397
  • 571
hanco ike
  • 285
  • 2
  • 14
1

You have this:

ENCLOSED BY '"'

Thus " is not a regular character any more. It's a special character that has a special meaning: it highlights the start and end of a column value. If you want to type a " that does not behave that way you need to escape it. The RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files document explains how to do that:

If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote

a;b
"UPRR 38 PAN AM ""M""";1

As they say, garbage in, garbage out ;-)

Álvaro González
  • 142,137
  • 41
  • 261
  • 360