Is there such a script out there or way of importing data from a CSV into mysql that can match the first field so instead of overwriting data will just update missing fields or values in rows? Hope I have explained myself clearly enough!
Example:
1,john,doe,programmer
2,jane,doe,accountant
3,judy,doe,manager
1,john,doe,cto
Assuming the first field is an ID, I want the script to insert开发者_如何转开发 when a record does not exist but update when the ID already exists, so John Doe would first get inserted as a programmer, but then updated to be a CTO.
From the MySQL reference manual, in the LOAD DATA section:
The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values:
If you specify REPLACE, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row. See Section 12.2.7, “REPLACE Syntax”. If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. 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.
So I believe that if you set the field where you store the names as an primary key or unique index, you should be able to do:
LOAD DATA LOCAL INFILE 'file.csv'
REPLACE INTO TABLE yourtable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, surname, etc);
update:
just found some more resources on the subject:
mysql duplicates with LOAD DATA INFILE
http://support.modwest.com/content/6/253/en/how-do-i-import-delimited-data-into-mysql.html
http://codeinthehole.com/archives/35-How-to-sync-a-MySQL-table-between-two-remote-databases..html
try the following may be helpful for youCSV TO MySQl
good luck
精彩评论