开发者

CSV to Mysql import script to match fields

开发者 https://www.devze.com 2023-04-12 19:01 出处:网络
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

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

0

精彩评论

暂无评论...
验证码 换一张
取 消