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.

chunfeng zhang · Accepted Answer · 2021-12-15 13:59:20Z

107

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);

edited Dec 15, 2021 at 13:59

chunfeng zhang

54 bronze badges

answered Jan 26, 2010 at 11:42

grapefrukt

27.1k6 gold badges52 silver badges74 bronze badges

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

5 Comments

Camsoft Over a year ago

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.

deemi-D-nadeem Over a year ago

can any one tell me how can i ignore the @dummy

Steve Pitchers Over a year ago

You need at least FIELDS TERMINATED BY ',' to avoid "Row 1 doesn't contain data for all columns"

Steve Pitchers Over a year ago

Also add IGNORE 1 LINES if your CSV file has a header row.

barlop Over a year ago

@StevePitchers what about what this suggests serverfault.com/questions/96401/… which is round brackets after IGNORE 1 LINES where within the round brackets all the columns are listed?

Community · Accepted Answer · 2023-09-20 23:00:49Z

5

step1.deal with awk.

cat file.txt |awk -F',' '{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);

edited Sep 20, 2023 at 23:00

CommunityBot

11 silver badge

answered Jan 22, 2014 at 7:01

brzhang

891 silver badge4 bronze badges

3 Comments

Paul Over a year ago

I like the use of awk here in showing the beauty of chaining multiple tools together.

deemi-D-nadeem Over a year ago

can any one tell me how can i ignore the @dummy

Lori Over a year ago

Need -F ',' switch for awk command for comma delimiter.

Community · Accepted Answer · 2017-05-23 10:31:17Z

-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, 2017 at 10:31

CommunityBot

11 silver badge

answered Nov 13, 2014 at 6:04

Ataboy Josef

2,1113 gold badges24 silver badges28 bronze badges

Comments

Ataboy Josef · Accepted Answer · 2014-11-13 06:10:17Z

-2

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'

answered Nov 13, 2014 at 6:10

Ataboy Josef

2,1113 gold badges24 silver badges28 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

5 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related