Simon Harriyott

Pulling data from Excel

I spent much of today pulling data from Excel to put into SQL Server tables. The data was pretty well formatted, but each row of spreadsheet data traverses many database tables.

I saved the spreadsheet to CSV, and then used my trusty friend, the world's simplest code generator (javascript edition), to generate SQL statements from the many rows of CSV which I pasted into SQL Manager, e.g.

INSERT INTO People (FirstName, LastName) VALUES ('$2', '$3')

Some of them became quite convoluted, as once I'd inserted rows into the People table, I had to look up the IDs for the link tables. Also, there were a couple of Irish names with apostrophes in them, so I used a regular expression to double them up:

~(:Wh)'~([,|:Wh]|\))

n.b. I think this is a Visual Studio style regex, as it seems a little different to what I vaguely remember (not that I do remember, that is).
27 February 2008