CODEWITHSUNDEEP
mysqlbashshimport-csv
newit1
newit1

Reputation: 1

Load data infile - Upload a CSV-file

sorry for my english..

I wrote a shell-script to update a table in a local mysql database by csv-file.

#! /bin/sh
mysql --user=root --password=12345 \
-e "USE database one;" \
-e "CREATE TABLE IF NOT EXISTS xxxx LIKE xxx;" \
-e "TRUNCATE TABLE xxxx;" \
-e "LOAD DATA INFILE \"/var/lib/mysql-files/data.csv\" INTO TABLE xxxx FIELDS TERMINATED BY ';' ;" \

CSV-file:

1;Müller;Max
2;Wayne;Roney

The field in my database "id" is integer and primary key. If i change in csv this field like 

a;Müller;Max 
2;Wayne;Roney

the script load data in csv in table. I would like to have an error because the csv is not correct.

What i have to do?

Thank you!

Upvotes: 0

Views: 475

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

A string like 'a' will automatically convert to a numeric value if you try to insert it into an integer column. The numeric value of 'a' is 0, because it has no leading digits.

I found a way to force an error in this case.

First, you must not use AUTO_INCREMENT for your id column. This will allow 0 because it causes a new id to be generated.

Second, you must pre-insert a row with the value 0 for the primary key before you load data. Then if the value like 'a' is converted to 0, it'll result in a UNIQUE KEY violation.

mysql --user=root --password=12345 \
-e "USE database one;" \
-e "CREATE TABLE IF NOT EXISTS xxxx LIKE xxx;" \
-e "TRUNCATE TABLE xxxx;" \
-e "INSERT INTO xxxx (id) VALUES (0);" \
-e "LOAD DATA INFILE \"/var/lib/mysql-files/data.csv\" INTO TABLE xxxx FIELDS TERMINATED BY ';' ;" \
-e "DELETE FROM xxxx WHERE id = 0;"

Note that this doesn't work if you use LOAD DATA LOCAL INFILE...

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

If the conditions don't work for you, then you'll have to write some extra code in your script to check the csv file, and either fix the bad data, or else print an error and exit.

Upvotes: 1

Related Questions