How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?

Question

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

The CSV file looks like:

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

and the MySQL table looks like:

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

I need to map the columns 1-8 of CSV file directly to the first 8 columns of the MySQL table. I then need to skip the next two columns in the CSV file and then map column 11 of CSV file to column 9 of MySQL table.

At the moment I am using the following SQL command:

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'

But the above code maps the first 9 columns of CSV file to the 9 columns in the MySQL table.

score 94 · Accepted Answer · answered Jan 26 '10 at 11:42

94

From Mysql docs:

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'  
INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);

answered Jan 26 '10 at 11:42

grapefrukt

26,815
4
46
71

13

FYI: I had to add the `(column1, @dummy, column2...` field statement to the end of my example SQL. Initially I had added it directly after the table name as shown the above example but I kept getting a MySQL error. – Camsoft Jan 26 '10 at 17:30
can any one tell me how can i ignore the @dummy – deemi-D-nadeem Aug 05 '14 at 14:17
You need at least `FIELDS TERMINATED BY ','` to avoid "Row 1 doesn't contain data for all columns" – Steve Pitchers Apr 21 '15 at 12:31
Also add `IGNORE 1 LINES` if your CSV file has a header row. – Steve Pitchers Apr 21 '15 at 12:35
@StevePitchers what about what this suggests http://serverfault.com/questions/96401/how-does-one-list-warnings-from-the-mysqlimport-utility which is round brackets after `IGNORE 1 LINES` where within the round brackets all the columns are listed? – barlop Oct 21 '16 at 17:47

score 4 · Answer 2 · answered Jan 22 '14 at 07:01

4

step1.deal with awk.

cat file.txt |awk '{print $1,$2,$5...}'>new_file.txt

step2.load into mysql.

load data local infile 'new_file' into table t1(...)

the method below is simple,but not allowed in lower version of mysql.

LOAD DATA INFILE 'file.txt'  
INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);

answered Jan 22 '14 at 07:01

brzhang

79
1
4

I like the use of awk here in showing the beauty of chaining multiple tools together. – Paul Feb 04 '14 at 17:54
can any one tell me how can i ignore the @dummy – deemi-D-nadeem Aug 05 '14 at 14:18

score -1 · Answer 3 · edited May 23 '17 at 10:31

-1

@deemi:

The only way to ignore the @dummy is by setting the field's Default to AUTO INCREMENT. So you can skip the field and just code like this,

LOAD DATA INFILE 'file.txt'  
INTO TABLE t1 (column2, column3, column4, column5);

//assumes that the fieldname column1 is set to AUTO INCREMENT by default.

edited May 23 '17 at 10:31

Community

1
1

answered Nov 13 '14 at 06:04

Ataboy Josef

1,855
2
18
27

score -1 · Answer 4 · answered Nov 13 '14 at 06:10

I think there is one more change in the code:

The following SQL command:

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'

-will probably result in an data truncation error.

So it is better to use LINES TERMINATED BY '\r\n' instead of LINES TERMINATED BY '\n'

SO the code will be:

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\r\n'

How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?

4 Answers4

Linked

Related