I’ll be the first to say, the switch from DTS to SSIS was painful. DTS would go through a lot of implicit conversions so you didn’t have to worry or think about them. But SSIS was not as merciful. From different fields lengths to different encodings, SSIS wants you to be specific. Sometimes, it is just quicker to use some excel trickery than figuring out that you should have defined a column as a String 65 instead of the default String 50.
We will use the CountryList.csv file and CountryList table from the original Importing Data in SQL Server post. When we open up the file in Excel, we have a lot of built in functions at our disposal. The function that interests me most is the CONCATENATE function.
Now, the strategy here is to :
- Build the key portions of the INSERT string once
- Use the data in the current row to build an INSERT statement
- Use the fill handle (the small black square in the lower right side of a cell) to create the remaining INSERT statements.
Now, I will refer to explicit cells in the file, so it is important that you follow along. However when using this on your own, you will substitute the Cell references as needed.
Navigate to cell O2 in the CountryList.csv file. Type in the following text:
INSERT INTO CountryList(CountryName,CountryCode2,CountryCode3) VALUES ('
As you can see, we have the INSERT keyword, the table name, the VALUES keyword and the first apostrophe. This creates slot for the countryName column. Navigate to cell P2 and cell Q2 and insert the following text:
'','
Don’t be fooled. Those are two apostrophes, not a quotation mark. The reason we have to use 2 apostrophes is because a leading apostrophe tells Excel to treat anything after the apostrophe as a string. This is useful when you’re dealing with data that may contain a leading zero, such as a zip code. As you can see, we have the double apostrophe, which will translate into one apostrophe, a comma, to separate the two fields, and another apostrophe, which starts out the next field. These will be used as the divisions between the CountryName, countryCode2 and CountryCode3.
Next, Navigate to Cell R2 and add the following text:
'')
Once again, we see the double apostrophe, which translates into a single apostrophe and the closed parenthesis. This string will close off the CountryCode3 column as well as close the insert statement. Now, we will see the power of the CONCATENATE statement.
Navigate to the column S2. From there you will type the following command:
=CONCATENATE(O2,B2,P2,K2,Q2,L2,R2)
You will see this text in the Formula bar above the current sheet. Lets take a closer look at this string. B2 represents the CountryName, K2 represents the 2 digit Country Code and L2 represents the 3 Digit country code name. O2 is the INSERT command string, P2 and Q2 are the apostrophe, comma, apostrophe strings and R2 is the apostrophe, closed parenthesis string. It looks a little confusing, however, when you hit enter, Cell S2 will now contain the following text:
INSERT INTO CountryList(CountryName,CountryCode2,CountryCode3) VALUES ('Afghanistan','AF','AFG')
Which is exactly what we wanted. Now, for the final step: creating the INSERT statement for all of the records in the file. Instead of copy and paste, simply highlight Cells O2 to S2. You will see a small black dot appear in the lower right side of the highlighted cells. This is called the Fill Handle.
All you need to do is double click the Fill Handle and the formula and text will self-replicate until the end of the file!
All that is left to do is copy everything from the S Column and paste the results into SQL Management Studio and execute the commands. All this takes about 2 minutes to do. Try it out!