Latest Post

Error: No input file specified.

While trying to run a codeigniter app the other day I came across this error just after I had finished uploading the files and making the ne...

Monday, June 21, 2010

Tuesday, June 8, 2010

How to generate mysql update sql statement from an excel document

This assumes that you have a unique identifier in at least one of the columns.

1) In you source excel document insert a column and in that column we want to concatenate an update string similar to the example below:
="update mysql_table_name set fieldname = '"&A1&"',fieldname2='"&B1&"' where id = "&C1&";"

2) Copy this formula down to generate all the required update statements

3) Create another column, copy the recently generated column of fomulas, right click in the new column, choose "paste special" and select the "values" option

4) Copy the recently pasted values, i.e. the update sql statements into you mysql db query engine e.g. phpMyAdmin and run the queries. Your mysql table is now updated with the values from the excel sheet.

Not the most elegant way of doing things i know, but it worked for me on several occasions :)

Chitika Ads