[thelist] excel migration to database

[Simon Willison]

Jay Blanchard wrote:

> I am migrating several hundred excel spreadsheets to MySQL, a time
> consuming process. Is anyone aware of a tool where I can batch save all
> of the sheets as tab delimited files?

Doesn't Excel have a "save as CSV" or "export to CSV" option?

An interesting alternative method is to dynamically construct SQL insert 
statements using Excel formulas. It's years since I last used a 
spreadsheet, but the basic idea is that you build a formula in a cell 
that does something like this (pseudocode):

= "insert into tablename values('" + escapeslashes(D4) + "', '" + 
escapeslashes(D5) + "');"

The above syntax is probably wrong, and I don't know how you would go 
about constructing an escapeslashes function (which is essential if some 
of your cells contain text that may include quote marks) but that should 
give you the general idea. You construct the formula once, then use 
"fill down" to apply it to every row in the spreadsheet. Then you copy 
and paste the result out of the column and in to a SQL file for 
exporting in to the database.

I've never used this technique for database population, butI once used 
it to use it to construct HTML tables containing information from a 
spreadsheet.

Hope that helps,

Simon Willison
http://simon.incutio.com/