The Proper Way Of Importing Data Into Mysql

[prophecym]

This is bugging me for some time now. I would love some advise.

 

Here is the situation:

 

I have my Contacts table in my database. And using a CSV file, I need to import more people into this table time to time. The problem is, how would you check to see whether you already have the record in the Contacts table? If so, how would you update the record's some or all fields(basically the fields need to be updated) based on the record in the CSV file.

 

This actually gets more complicated when the Companies table gets into the picture. If you have a record in the CSV file which has a company not in the database yet, how would you tell it to create that company, and use that company's id# to add into that record in the Contacts table.

 

I can elaborate this as I get your questions. But right now, I can't logically think of any way of doing this with just using MySQL. It feels like it has to have a PHP script to handle such a job. And I don't know that script.

[Barand]

You're not going to do it with a single query but you could load the csv into a table first then run a script with a series of queries to establish new companies and insert them then find new contacts and insert them with a join to the companies on name to get ids

[requinix]

My preferred way is to import the data into a temporary table then craft an INSERT... SELECT statement, or for more complex logic a loop+cursor with individual INSERTs.

[prophecym]

I appreciate your answers. But, how exactly you would execute this process. Would you share the steps and the codes?

[Barand]

Hope this helps. It uses my test tables so you'll need to modify quite a bit

 

Start data (I added unique key on email column as that is being imported)

mysql> SELECT * FROM contact;
+----+--------------+------------+----------------+-------------+
| id | contact_name | company_id | email		  | phone	   |
+----+--------------+------------+----------------+-------------+
|  1 | Fred		 |		  1 | fred@abc.com   | 01614567890 |
|  2 | Mary		 |		  2 | mary@def.com   | 01614567890 |
|  3 | John		 |		  3 | john@jkl.co.uk | 01614567890 |
+----+--------------+------------+----------------+-------------+

mysql> SELECT * FROM company;
+----+---------+
| id | company |
+----+---------+
|  1 | abc	 |
|  2 | def	 |
|  3 | jkl	 |
+----+---------+

CSV Data  (mytest.txt)

name,company,email,phone
john,jkl,john@jkl.co.uk,07784561239
mark,xyz,mark@xyz.com,01215557777
luke,abc,luke@abc.com,02553691478
matthew,qrs,matt@qrs.co.uk,01613216547
mary,qrs,mary@qrs.co.uk,01613216547

 

This the import script I used

DROP TABLE tmp;
CREATE TEMPORARY TABLE tmp (
name varchar(50),
company varchar(50),
email varchar(50),
phone varchar(15));

LOAD DATA LOCAL INFILE 'mytest.txt'
   INTO TABLE tmp
   FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n'
   IGNORE 1 LINES;

/* insert new companies */
INSERT INTO company (company)
   SELECT DISTINCT tmp.company
   FROM tmp
   LEFT JOIN company ON tmp.company = company.company
   WHERE company.company IS NULL;

/* import contacts */    
INSERT INTO contact (contact_name,company_id,email,phone)
SELECT t.name, c.id, t.email, t.phone
FROM tmp t
INNER JOIN company c ON t.company = c.company
ON DUPLICATE KEY UPDATE
   contact.contact_name = t.name,
   contact.phone = t.phone;

 

And the final results

mysql> SELECT * FROM contact;
+----+--------------+------------+----------------+-------------+
| id | contact_name | company_id | email		  | phone	   |
+----+--------------+------------+----------------+-------------+
|  1 | Fred		 |		  1 | fred@abc.com   | 01614567890 |
|  2 | Mary		 |		  2 | mary@def.com   | 01614567890 |
|  3 | john		 |		  3 | john@jkl.co.uk | 07784561239 |  <- updated
|  4 | luke		 |		  1 | luke@abc.com   | 02553691478 |  +
|  5 | mark		 |		  4 | mark@xyz.com   | 01215557777 |  +
|  6 | matthew	  |		  5 | matt@qrs.co.uk | 01613216547 |  +
|  7 | mary		 |		  5 | mary@qrs.co.uk | 01613216547 |  +
+----+--------------+------------+----------------+-------------+

mysql> SELECT * FROM company;
+----+---------+
| id | company |
+----+---------+
|  1 | abc	 |
|  2 | def	 |
|  3 | jkl	 |
|  4 | xyz	 |  +
|  5 | qrs	 |  +
+----+---------+

[fenway]

Yeah -- it's always easiest to get in to the DB first, and then manipulate it.

 

FWIW, there is a CSV engine type.