Mysql CSV load infile

Question

I've got a CSV file with 9 columns and I have a MySQL table with 11 columns.

The CSV file looks like:

col1, col2, col3, col4, col5, col6, col7, col8, col9

and the MySQL table looks like:

col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11

I need to get the script to ignore the two erroneous (but required) MySQL columns.

The mysql columns that need to be ignored in the import are: db_id & nice_date =)

This is what I have so far:

$sql = 'LOAD DATA LOCAL INFILE "../csvtemp/test.csv" 
        INTO TABLE sample 
            FIELDS TERMINATED BY "," 
            OPTIONALLY ENCLOSED BY """" 
            IGNORE 1 LINES'
;

Have you thought of using PHP fgetcsv() to read each line of the CSV file into an array? Then you can implode the array into a query. It's a bit more code than LOAD DATA, but it's sure to work right. — Ray Paseur
– Ray Paseur, Commented Dec 23, 2012 at 17:09
Another alternative - ALTER TABLE to remove the extraneous columns, LOAD DATA, then ALTER TABLE to add the columns back. — Ray Paseur
– Ray Paseur, Commented Dec 23, 2012 at 17:10
And I guess just for clarification, which has nine and which has eleven. I think the narrative does not match the examples ;-) — Ray Paseur
– Ray Paseur, Commented Dec 23, 2012 at 17:11
The definition of your table is missing. Also don't you get an error back? That information is useful, too. — hakre
– hakre, Commented Dec 23, 2012 at 17:12
Sorry @RayPaseur , miles away! The mysql has 11 and the csv has 9 =) unfortunately the CSV is over 100mb in size and no code I've seen can process as effectively as just straight mysql dump and the altertable is vaguely out of the question as the table can be updated at any time with more csv imports and it would be nice to keep the index_id the same — Keith Whelpton
– Keith Whelpton, Commented Dec 23, 2012 at 17:12

Bill Karwin · Accepted Answer · 2012-12-23 17:59:37Z

4

$sql = 'LOAD DATA LOCAL INFILE "../csvtemp/test.csv" 
        INTO TABLE sample 
            FIELDS TERMINATED BY "," 
            OPTIONALLY ENCLOSED BY """" 
            IGNORE 1 LINES
            (col1, col2, col3, col4, col5, col6, col7, col8, col9)'
;

The missing columns will be given their DEFAULT values, or else you can specify fixed values this way:

$sql = 'LOAD DATA LOCAL INFILE "../csvtemp/test.csv" 
        INTO TABLE sample 
            FIELDS TERMINATED BY "," 
            OPTIONALLY ENCLOSED BY """" 
            IGNORE 1 LINES
            (col1, col2, col3, col4, col5, col6, col7, col8, col9)'
            SET col10 = 'abc', col11 = 'xyz'
;

answered Dec 23, 2012 at 17:59

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

5 Comments

Pradeep Kumar Over a year ago

I didn't inserted auto-increment field but when i insert for next time, it excludes some numbers. Ex: when i insert 4 rows, at first 1,2,3,4 and when i do it again the auto-increment fields are 8,9,10,11

Bill Karwin Over a year ago

Failed inserts still use id values. In general. don't worry about gaps between id values. Auto-increments are required to be unique, but are not required to be consecutive. They are not row numbers.

Pradeep Kumar Over a year ago

That's ok but is that an issue in our query..? Can it be corrected..?

Bill Karwin Over a year ago

It can be corrected if you specify values of your ids instead of letting the auto-increment generate them.

Pradeep Kumar Over a year ago

if so, there is an issue in "auto-increment" on "load data local infile".. because auto-increment doesn't need ids to be inserted(optional) when inserting and it won't give gaps on normal.. and if so it's better to read the file and insert mulitple values if i don't need gaps in ids..!!!

Michael Irigoyen · Accepted Answer · 2015-03-27 17:20:46Z

0

Looks like you can use LINES TERMINATED BY (perhaps with PHP_EOL).
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

To quote the manual page:

If a line does not contain all fields, the rest of the columns are set to their default values.

edited Mar 27, 2015 at 17:20

Michael Irigoyen

23.1k18 gold badges92 silver badges132 bronze badges

answered Dec 23, 2012 at 17:45

Ray Paseur

2,2042 gold badges14 silver badges17 bronze badges

Collectives™ on Stack Overflow

Mysql CSV load infile

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related