Today I had a task of taking a bunch of data supplied to me in tab delimited format and plugging this into a SQL database. There are of course a few ways to handle this sort of data, however one of things I do regularly is use string concatenation in Excel to build the insert statements required for a task like this.

So next time you are given a list of some sort and need to insert it into a database, try this:

• Import the data into excel in whatever way you can. This might include copy and pasting, or just opening the file.
• Parse the data. This is a fairly easy in excel, in fact there’s a little wizard to help you out. Under the data menu choose ‘text to columns’ and then follow the steps. The cool part is that at about step 3 you can choose to not import columns that contain useless data (as far your objective is concerned anyway).
• Here’s the cool part, build your insert statement as a formula. Excel can do string concatenation by using the ‘&’ character in a formula, you then just need to join the text together to make up the rest of the statement.

The only tricky part is that excel expects the string literals (the textual part) of your formula to be enclosed in double quotes, and as you probably know SQL expects them to be in single quotes. So when it comes to joining this stuff together you get some odd looking statements. Once you get it right though, just copy and paste to do it over and over.

Of course, once you’ve done this bit you can then just auto fill down your spreadsheet for all your data then copy and paste across to query analyzer and execute.