In my last post (found at Pushing Great Data Into SQL Server), we quickly skimmed over the myriads of ways that I use to push data into SQL Server, or for that matter, almost any relational database. This time, I want to go a bit more in-depth into why it works and why it’s so simple.
One of the most important items in getting this data into the database is the correct setup of a table. If the table is not set up properly, then the data that you’re trying to work with will never fit into the database. For example, if the table showed that the FirstName column only allowed 50 characters, then if your spreadsheet has a row with over 100 characters in it, it will fail upon your attempt to insert the data. Or – and this is one of my favorites that I’ve observed – somehow the data within the spreadsheet got moved over one column after a certain point in the spreadsheet – making the data almost unusable.
I bring this point up to yet again stress how important getting the table structure is when you’re dealing with data. You can make your life more difficult than it needs to be very quick.
One of the items I do before creating the table in the database is to review all of the data that is in the spreadsheet to make sure that:
- I understand the data that is going into the database table.
- Nothing that is just obviously wrong is trying to be pushed into the database. For example, the data I was talking about earlier that was one column over from what it should have been. If you see data that is all 0’s and 1’s up until a certain row, then you have descriptions or names – you probably have some bad data.
The other important part of pushing the data into the database from a spreadsheet is working with the CONCATENATE function of Excel. Let’s go into that now.
First, here’s the formula I provided last time:
=CONCATENATE("INSERT INTO dbo.Person SELECT ", A2, ", '", B2, "','", C2, "','", D2, "','", E2, "'")
First – Notice the “=” sign at the very start of the formula. This signifies to Excel that what will be entered here should be treated as an Excel function.
The Concatenate function does exactly what it says on the tin. It allows the user to combine 2 or more strings into one string. All strings to combine will be separated by a comma. Any hard-coded data should be surrounded by double quotes.
Finally, notice the single quotes and double quotes in the CONCATENATE function above. The reason that there are so many of both is because of what the data looks like and how it will be inserted into the table.
Notice that around the A2 cell, there are no single quotes – only double-quotes. Around all other cells, there are single quotes. In the table that we’re inserting into, notice that we’re only allowing integers. And if you’ll notice the column in the spreadsheet, there are only numbers. When integers are inserted into a table, there is no need in having single quotes surrounding it. The others, though, do require a single quote on both sides of the data to insert it into the table.
I recommend reviewing the data after Excel works through the data to make sure that it truly is set up correctly and that the data will be inserted correctly. This is not just for the “rookie,” but for the more experienced person as well. The more columns that have data, the more chances for a mistake.
Once you’ve reviewed the data and everything looks great with the data – be sure to copy the formulas into every row of the spreadsheet. I don’t know how many times I’ve seen people think that they have data from every row, just to go back through it quickly and find that some rows were hiding from them! So be extra careful to make sure you truly have the formula on every row!
After that – go ahead and run your new queries in the database! Time to make sure your data really can be inserted into the database. If you find an error, you still have everything ready to quickly be changed or updated in Excel, so now is a great time to fix your data appropriately.
Once the data has been pushed to the server, you’ll be singing this song appropriately!
With that, you should feel even better about pushing your data to the database. So, until next time, when we’ll go over some more ways to push spreadsheets into the database.