Mysql Importing new codes from excel.

Question

I have a CSV file with mappings from project ids to some new category codes.

e.g.

Project Id, New Code 
1, 035 
2, 029 
3, 023 
4, 035 
5, 029 
....

The above is in a CSV file/excel file

I have a table of projects with these project ids and I want to add a new column with the releavan new code.

Is there any way I can do that with mysql?

Answer 1

Here is a quick-and-dirty solution, using the CONCATENATE functionality of Excel:

Assuming you have Project ID in column A and New Code in column B, enter the following for column C:

=CONCATENATE("update projects set new_code = ",B1, " where project_id = ", A1, ";")

Then copy and paste that for all the rows in your excel spreadsheet. That generates SQL statements you can then use to bulk update your table. Copy out the text into a script and then let mysql execute it.

You will end up with a script that looks something like this:

update projects set new_code = 35 where project_id = 1;
update projects set new_code = 39 where project_id = 2;
update projects set new_code = 23 where project_id = 3;

This of course assumes you already have the new column in your table. If not, use an alter table statement to add it:

alter table projects add column new_code int;

Note: I do not reccomend this method if you want to do it repeatedly - but if its just a once-off thing, then a quick solution like this works just fine.

Answer 2

First problem is to bring your csv into the database. There are som solutions for mysql, see for example http://dev.mysql.com/doc/refman/5.1/en/mysqlimport.html

There are also Third-Party-Tools which do such things.

When you have the CSV-Data in a new table in your database and you created a new column in your projects table, just do this statement:

UPDATE projects p 
SET new_code = 
   (select distinct new_code from temp_table where project_id = p.project_id)