2

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.

Gordon
  • 312,688
  • 75
  • 539
  • 559
Steve
  • 153
  • 1
  • 2
  • 6

2 Answers2

1

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

Community
  • 1
  • 1
Bruno Flávio
  • 778
  • 10
  • 27
0

try the following may be helpful for youCSV TO MySQl

good luck

MR Gharibeh
  • 344
  • 2
  • 11