Update MySQL Table using CSV file

Question

my Current MySQL table employee_data has 13k rows with 17 columns. The data in the table came from a CSV file Employees.csv. after importing my csv data I added a new column 'password' (so its not in the csv file) Password is edited and accessed via a web portal. I now have an updated csv file and I want to update my main table with that data but I don't want to lose my password info.

Should I import my new CSV file into a temp table in my database and some how compare them? I am not sure where to start and I am open to recommendations.

I am now realizing I should have kept my password info in a separate table. Doh!

I guess I could created a php file that compares each row based on the employee_id field but with 13k rows I am afraid it would time out possibly.

I think the easiest thing would be to get the mysql query (code) that would compare and update what is needed. — Denoteone
– Denoteone, Commented Jun 13, 2012 at 16:37

Manse · Accepted Answer · 2012-06-13 16:39:48Z

10

I would do it like this :

Create a temp table using CREATE TABLE new_tbl LIKE orig_tbl; syntax
use LOAD DATA INFILE to import the data from the CSV into the table
Use UPDATE to update the primary table using a primary key / unique column (perhaps employee_id)

I have worked with tables containing 120 million lines and imported CSV files containing 30 million lines into it - this is the method I use all of the time - much more efficient than anything in PHP (and thats my server side language of choice)

answered Jun 13, 2012 at 16:39

Manse

38.1k11 gold badges88 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Denoteone Over a year ago

I think this is the option I am going to try first. Thanks.

GDP Over a year ago

I do the same thing every day with multiple 60GB files...this is always the cleanest/safest way to get it done

zhihong Over a year ago

Before, I thought maybe this is not a good idea for daily update, as it needs to create some temp table in the database server. But it is true this is the easiest way comparing to use php to update the rows.

Gntem · Accepted Answer · 2012-06-13 16:36:54Z

1

Try other tools other than php based ones phpMyAdmin MySQL workbench is a great tool, based on you connection it will take a while to query the database with your data.
There are workarounds with php timeout limit, set_time_limit();

answered Jun 13, 2012 at 16:36

Gntem

7,2052 gold badges37 silver badges48 bronze badges

Collectives™ on Stack Overflow

Update MySQL Table using CSV file

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related