Search code examples
mysqlload-data-infile

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?

Solution

  • 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'