What can I do if delimiter of csv is appear in value?

Question

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.

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

Kusalananda · Accepted Answer · 2018-03-30T10:08:32.187

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,...)

edited Mar 30 '18 at 10:08

answered Mar 30 '18 at 10:06

Kusalananda

333,661

1

Without csvkit, on the OP's input, one can always do sed 's/,/@/;s/,/@/' to replace the first two , with @ – Stéphane Chazelas Mar 30 '18 at 10:07
Is there any problem if I will use | as delimiter instead of @? – alhelal Mar 30 '18 at 10:34
@alhelal You may choose any character that is not already part of the file's data. – Kusalananda Mar 30 '18 at 10:38

score 1 · Answer 2 · answered Mar 30 '18 at 10:37

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.

answered Mar 30 '18 at 10:37

Baris Demiray

130

Then how do I extract the original data? – alhelal Mar 30 '18 at 17:15

What can I do if delimiter of csv is appear in value?

2 Answers2