export CSV to MySQL

Question

I want to do something really simple. I just want to export excel data to a CSV in order to put it in a Mysql DB.

It is a huge excel so I can't manage exceptions easily.

My problem is that when I save my xslx into csv, it put me the " to separate columns only if there is a comma in the column or if there is already a " so I have something like that in my CSV :

col1 | col2 | col3 => col1,col2,col3
col1 | co,l2| col3 => col1,"co,l2",col3 
col1 |"col"2| col3 => col1,"""col""2",col3 
col1 |      | col3 => col1,,col3

So I tried a lot of tings to convert it for SQL but i don't find a good way.

I work in Excel 2007 and SQL want to CSV to have this config:

Columns separated with: ,
Columns enclosed with: "
Columns escaped with: \
Lines terminated with: auto

MySQL LOAD DATA LOCAL INFLILE has a COLUMNS SEPARATED BY argument you can use without even converting. — Kevin
– Kevin, Commented Oct 8, 2012 at 3:44
MySQL should not be looking at commas inside quotes if you use the correct params — Adrian Cornish
– Adrian Cornish, Commented Oct 8, 2012 at 3:45
Thats perfectly normal/sane CSV rules. If there is no reason to added quotes, then it doesn't. (Quotes must be added if the data contains either quotes or commas - the separator. See the relevant wikipedia article.) — user166390
– user166390, Commented Oct 8, 2012 at 3:52
thank you. It helped me. I succeed thanks to Kevin solution. — BastienSander
– BastienSander, Commented Oct 8, 2012 at 6:07

MvG · Accepted Answer · 2012-10-08 06:09:47Z

1

So you want to load your data using

LOAD DATA FROM INFILE 'filename.csv'
INTO TABLE tablename
COLUMNS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

The OPTIONALLY is actually ignored when loading a file, or to phrase it a better way, even if you don't specify OPTIONALLY, the enclosing will still be treated as optional. Which means that a column value may be enclosed in quotation marks, but need not be. Note that afaik. Excel does not use \ to escape values.

If you need some other format for purposes other than importing into MySQL, then you may re-export the data from MySQL afterwards, with the settings you prefer. When writing a file, omitting the OPTIONALLY will turn quotes on for every value.

answered Oct 8, 2012 at 6:09

MvG

61.8k25 gold badges158 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

export CSV to MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related