0

Suppose, my csv file contains

"item_name","price","description"
mobile,500$,It has many features (e.g., camera, big display, etc)  

I want to load this csv data file in mysql database using mysql command like

load data local infile 'file.csv' into table table
 fields terminated by ','
 lines terminated by '\n'
 (column1, column2, column3,...)  

You can also say to use enclosing each value by " and using enclosed by '"' option of mysql. But enclosing each value is overhead to me.

I can enclose only description value if there is a solution to read this type of csv where one column is enclosed by " but not all.

alhelal
  • 1,301
  • 3
    Strictly speaking, that is not a CSV file, exactly because the unquoted field contains the field delimiter. A properly formatted CSV file would double quote the problematic field. No dedicated CSV parser would be able to parse the file as it is currently presented. The MySQL loading is not a CSV parser however, and even quoting the last field would not help. – Kusalananda Mar 30 '18 at 09:59

2 Answers2

2

Change the field delimiter from commas to something that is not otherwise present in the file. If you have control over what creates the CSV file, then this should not be too difficult.

With csvkit, this can be done if the last field is properly quoted:

$ cat file.csv
"item_name","price","description"
mobile,500$,"It has many features (e.g., camera, big display, etc)"

$ csvformat -D@ file.csv
item_name@price@description
mobile@500$@It has many features (e.g., camera, big display, etc)

Or, without csvformat, assuming that the first two commas on each line are true delimiters:

$ sed -e 's/,/@/' -e 's/,/@/' file.csv

This does not require the last field to be quoted.

Redirect this to a new file, and then use

load data local infile 'newfile.csv' into table table
 fields terminated by '@'
 lines terminated by '\n'
 (column1, column2, column3,...)  
Kusalananda
  • 333,661
1

I'd call it a complex field and I'm personally for encoding them, for example, in base64,

$ echo "It has many features (e.g., camera, big display, etc)"  | base64
SXQgaGFzIG1hbnkgZmVhdHVyZXMgKGUuZy4sIGNhbWVyYSwgYmlnIGRpc3BsYXksIGV0YykK

But there is of course pros and cons of doing so.