开发者

load csv file content into mysql table with validation

i want to import a large csv file (about 12MO) into mysql table , first i tried with LOAD DATA INFILE it work perfectly , but in my case i want firstly test the开发者_开发百科 csv rows to determine if i want to update data or insert new records so the solution is to read the file and compare the content of each row with the data already in the table , and make the right action this methode also works but takes lot of time and resources

now my question is

1 : can i use the import functions of PHPMYADMIN ( open source ) and my project is comercial

2 : if yes i can , do you know some tutorials about this ( any idea )

3 : if no i can't , is there such commercial frameworks for exporting/importing

thanks


This is actually pretty common SQL: you either want to insert or update, yes? So you need two statements (one for update, one for insert) and a way to tell if it should be inserted. What you really need is a unique key that will never be duplicated for the individual record (can be a composite key) and two statements, like this:

UPDATE right SET
   right1 = left1,
   right2 = left2,
   etc
FROM the_import_table right
LEFT JOIN the_existing_data left ON left.key = right.key
WHERE right.key IS NOT NULL --note that I write in TSQL

INSERT INTO right (
   right1,
   right2,
   etc
) SELECT
   left1,
   left2,
   etc
FROM left
LEFT JOIN right on left.key = right.key
WHERE right.key IS NULL

Note that you can use composite keys using a set of ANDed values in the WHERE, and note that you're not updating the composite keys but you are probably inserting the composite keys. This should be good to get you a start. Before you ask for clarification update your question with actual code.


MySQL has specific insert syntax to deal with duplicate rows.

0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜