More Fun Inserting Data with PowerShell

Up until now, we’ve talked about various ways to insert data into SQL Server using SSMS. If somehow you missed out on the excitement, you can revisit those posts at:

• More Fun Inserting Data Using Wizards
• More Fun Inserting Data using Values
• Quick and Simple Inserts

Those ways work great if you’re inserting one tab of an Excel spreadsheet. I don’t know about y’all, but I don’t want to have to do all of this work for each tab – especially if I have 30+ tabs to import into SQL Server (and yes, I’ve been asked to do that quite a few times.)

One of the easiest ways I’ve seen to insert a lot of data (once you’ve made sure that the data is how you want it) is to use the power of PowerShell.

Yes, if you have never had the chance to use PowerShell, it’s very different from SQL Server commands. But there is so much power (Get it… Power… PowerShell…) in just a few commands that you can do a lot of things in just a few steps.

To use PowerShell, just do the following:

1. Press Windows Key + X to bring up the Power Users Menu
2. Click Windows PowerShell or Windows PowerShell (Admin)

This gives you a small PowerShell app that should be suitable for what we’re going to do with it. Yes, multiple IDE tools can be used for this, but it may be a bit much for what we’re going to do today.

As a prerequisite for adding data into SQL Server, you’ll need 2 modules installed. To do that, you’ll need to run the following code:

Install-Module -Name ImportExcel
Install-Module -Name dbatools

Now, to insert all of the data from a spreadsheet, we’ll run the following code in PowerShell.

$File = "C:\temp\Mock Person Data.xlsx"
$Instance = "YourSQLInstance"
$Database = "YourDatabase"

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($File)

foreach($sheet in Get-ExcelSheetInfo $File)
{

$data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable
$tablename = $fileName + '-' + $sheet.Name

Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -AutoCreateTable -Table $tablename

}

What this does is the following:

• Using all of the tabs in the spreadsheet “Mock Person Data.xlsx”
• Cycle through all of them
  • Create a table named just like the file, a “-“, and the name of the tab. For example, if there is a tab called “Mock Data”, it will create a table called [Mock Person Data_Mock Data]
  • Insert all of the data from the tab into this new table.
  • Go to the next tab.

Now, there could be a lot of things that go wrong with this approach. For example,

• If the spreadsheet has lots of data in different places. For example, some data in A1 through C7 and G1 through L10. This will bring in all of the data just like you asked. Problem is that you may need to separate it into multiple tables. Sadly, you’ll need to do that by hand from the table you’re given.
• Sometimes I’ve found that extra columns are added on with “Column 27” in columns that I don’t see any data for. This is nothing you’ve done wrong. Just some data was once inserted there and then removed. Sadly, if you want to clean up your tables to no longer have this mess, you’ll need to drop the column yourself.
• Notice there is no error catching in this PowerShell. That’s because I’ve usually curated the data pretty well before I insert the data into the SQL Server table. If you feel confident that you have as well, you can use this PowerShell script as well.

And with that, we add one more way to get data inserted into our SQL Server. Will we be using this way frequently, probably not. But it is a great way to get data into SQL Server.