CODEWITHSUNDEEP
mysqlload-data-infile
Plummer
Plummer

Reputation: 6698

MySQL infile ignore header row

Trying to get MySQL to ignore a specific row when importing through an INFILE command. Essentially, it's the "header" row in the CSV file.

LOAD DATA LOCAL INFILE 'C:\myfile.txt' REPLACE INTO TABLE my_db.my_table;

I've looked in to the ignore command, but that's for ignoring duplicate keys. Is there a way to ignore a specific entry/row in the .txt file?

Upvotes: 14

Views: 23767

Answers (2)

Michel Feldheim
Michel Feldheim

Reputation: 18260

IGNORE has two meanings in a LOAD DATA INFILE statement. The first is replace / ignore when it comes to duplicate key errors. The second meaning is the one you're looking for. the complete statement is IGNORE n LINES

You can't skip a random line but you can skip n lines at the head of your inputfile like this:

LOAD DATA LOCAL INFILE 'C:\myfile.txt' REPLACE INTO TABLE my_db.my_table IGNORE 5 LINES;

This ignores the first 5 lines, where end of line EOL is defaulting to \n and can be set to any character using LINES TERMINATED BY 'CHR'

Upvotes: 22

Tom
Tom

Reputation: 6663

Add this option to your statement:

IGNORE 1 LINES

Upvotes: 11

Related Questions