MySQL infile ignore header row

mysql load-data-infile
Plummer picture Plummer · Nov 26, 2012 · Viewed 18.4k times · Source

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?

Answer

Michel Feldheim picture Michel Feldheim · Nov 26, 2012

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'

Related questions

How do I import CSV file into a MySQL table?

I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column …

Read More
MYSQL import data from csv using LOAD DATA INFILE

I am importing some data of 20000 rows from a CSV file into Mysql. Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns? When I execute LOAD …

Read More
Import CSV to mysql table

What is the best/fastest way to upload a csv file into a mysql table? I would like for the first row of data be used as the column names. Found this: How to import CSV file to MySQL table …

Read More