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.

Answers


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)


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


Need Your Help

How to style first paragraph <p> of the content differently without using css class , ID or javascript, with IE6 compatibility?

jquery css xhtml internet-explorer-6

How to style first paragraph &lt;p&gt; of the content style differently without using inline css, css class , ID or javascript. ? with IE 6 compatibility too.

Can you tune C runtime heap segment reservation size on XP?

c windows memory-management heap vc6

When the VC6 C runtime on XP can't serve an allocation request within an existing heap segment, it reserves a new segment. The size of these new segments increase by factors of 2 (until there are not