[thelist] excel migration to database
Simon Willison
cs1spw at bath.ac.uk
Fri Oct 10 08:15:06 CDT 2003
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/
More information about the thelist
mailing list