How to LOAD DATA INFILE in mysql with first col being Auto Increment?

Question

Currently, We have a table similar to this:

---------------------
ID | AField | BField|
---------------------

The ID is Auto Increment

How do I create a CSV that can let the database auto populate the ID field with auto increment number?

We've tried the following CSV but it doesn't work:

afieldvalue, bfieldvalue (With Column definition but still doesn't work)
0,afieldvalue,bfieldvalue 
NULL,afieldvalue,bfieldvalue

Deneendenegation

Answer 1

The best thing to do is just include the 2 non-auto-increment columns in the CSV, and then explicitly set the ID column to NULL in the load data infile statement.

Something like this:

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(AField, BField)
SET ID = NULL;

Rahr

Use (your_table.AField, your_table.BField) instead if your CSV has the same column names. – Banal

@Banal LOAD DATA INFILE does not read column names from the CSV. That's why you must set the columns to the exact order of the CSV column data. – Adoration

Maybe worth noting that if the data has been exported including the ID column it can be import with ... (@UnusedVariable,AField,BField) – Francinefrancis

Answer 2

you can "omit" ID field in data row by placing delimiter sign in the beginning of a row, like this (for table schema ID,AField,BField):

,afieldvalue,bfieldvalue
...

SQL engine will assign NULL to ID field while reading such csv file

Connubial

What worked for me was to provide "NULL" or "\N" (note the capital N) in the CSV file for the auto increment field. Providing a blank value resulted in "ErrorCode: 1366 Warning: Incorrect integer value: '' for column `database`.`table`.`the_id_column`". 10.5.16-MariaDB on an InnoDB table. – Dustydusza

Answer 3

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r'
(AField, BField);

Lindsy

Answer 4

Try to use NULL as the value in the csv file for the ID field.

Captious

Answer 5

NULL in CSV file is read as a STRING in MySQL. If you want to have null values in a MySQL column instead use \N in place of `NULL. This will fix the issue and you will get the required result.

Danyluk

None of the other solutions worked - this worked for my ID column that is an INT – Venable

FYI using the string NULL did work for me today, for an int auto increment primary key field, but to my surprise the string \N also worked, but it has to be uppercase N, \n did not work. – Dustydusza

The sed command to replace emtpy values with \N sed 's/,,/,\\N,/g; s/,,/,\\N,/g; s/,$/,\\N/g;' – Scimitar

Answer 6

If you have only a small number of columns, you can just name them: LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE mytable (AField); (assuming only ID & AField)

Indo

Answer 7

LINES TERMINATED BY '\r\n' 

That was the key to success for me when using a text file

Overstrain

love it when you get a down vote without a comment lol. I am sure others will also need to terminate the line as I mentioned in order to get the solution to work, if you are going to downvote, explain why so others can comprehend / learn from the wisdom of your insight. – Overstrain

Doesn't seem to explain how to skip the ID field; nor does it explain what it does. What if your lines aren't terminated by \r\n? – Peursem

Clearly the question was changed or merged, my answer was in relation to issues with terminating a line to have separate results in the document show line by line, it must have been expanded to include sql info later. – Overstrain

I don't see anything in the edit history, but it is from a while ago, and I think not everything was preserved. – Peursem