You Want that Same Excel File Imported How Often?8 min read

For this week I am going to go through a process that I developed and am pretty proud of.

We the DBA Team are responsible for getting data in to our Integration environments and higher.  We do not trust the developers with this responsibility as they would probably break something.


Recently one of our development teams has increased the request of importing an Excel file with 20 sheets in to 20 tables in a database from about once a quarter to multiple times a week and this past Monday was three times in one day.  I have been the lucky DBA to get these requests as of late and after Monday I was determined to fix the process.  The current procedure is to use the good ol’ Import/Export Wizard since this was a rare request. (This included a lot of point and click and possibility for manual error)  With increased requests and increased table counts I knew there had to be a better way to get this accomplished without grimacing each time I see the request.


So with my increased interest in PowerShell and trying to learn and merge PowerShell and SQL I thought first how can I do this with PowerShell?  So I took to the #PowerShellHelp channel on Slack.  Needless to say the first couple suggestions were that sounds great for SSIS.  Well, I did not ask in the #SSISHelp channel is my thought in my head.  Then a suggestion to look at the ImportExcel module in the PSGallery.  Then my buddy Constantine Kokkinos (b | t) endorsed the suggestion and gave me encouragement.  So off I went to build a solution to import my Excel sheets in to a SQL database.

First I installed the ImportExcel module from the PowerShell Gallery.  Then I did an Import-Module so I could take a look at the functions and help.  Needless to say the function I was using did not have a lot of help so I ended up looking at the PowerShell code a little to better understand what the function could do.

As I started putting the code together I got more and more excited.  My wife was pre-occupied with something and my son was with his nanny, so I took the free time to just flow with it.

As I was going through and testing I think I found a bug in the  ConvertFrom-ExcelToSQLInsert in that it put single quotes around the columns of the insert statements that were built.  After getting failures in my testing I was able to figure that out.  Luckily I have the source code and can easily fix that and that is what I did.  Now all my imports would work and I did not have to have use the replace function to fix it.

What I had when I was done was the working crewed code you see below:

So as you can see, very redundant code blocks.  The next day Friday, I was determined to make the code follow better practices.

So what I did first was figure out if the ImportExcel module had a function that would read all the Sheets inside of my Workbook.  Sure enough there was a  Get-ExcelSheetInfo that I was able to read the Sheet names in the Workbook and store them in a variable.  Now I can look through Sheets and perform my actions.

Now since I had the code that needed to be used with the  ConvertFrom-ExcelToSQLInsert and had all the values that changed for each table I built my  foreach loop.  Then I hit an issue where some of my tables had an identity column that needed to be accounted for.  So after thinking about it I made a variable with the  SET IDENTITY_INSERT value for the tables that needed it and a different variable for ones that did not.

Then I added output so that I could see the record count loaded in to each table to send to the development team for validation.

See the improved code below:


As I am still building my development skills, this is kind of my working process.  Get a working model, then come back and improve code.  I still have a couple of additions I would like to add, but I have a working procedure that will save time for me and anyone on my team that gets this request in the future.





Leave a Reply

Your email address will not be published. Required fields are marked *