CODEWITHSUNDEEP
phpmysqlcsv
Denoteone
Denoteone

Reputation: 4055

Update MySQL Table using CSV file

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.

Upvotes: 2

Views: 7277

Answers (2)

Manse
Manse

Reputation: 38147

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)

Upvotes: 10

Gntem
Gntem

Reputation: 7155

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();

Upvotes: 1

Related Questions