Importing Excel into SQL Server using PowerShell

Ah T-SQL Tuesday, is it that time again? And the 94th one at that! Wow!

Words: 797
Time to read: ~4 minutes

Update: 2017-09-20 Thanks to Rick Fraser for pointing out I showed a $ServerConnection but hadn’t defined it in the function or separately or at all! Thanks Rick!

This month we have the Rob Sewell ( Blog | Twitter ) asking us to “get all PoSh” (and yes, I just got the pun there). So a T-SQL Tuesday about PowerShell, how could I say no! Especially since I had a blog post on PowerShell in the pipeline (See! You’re not the only one who can make puns Rob 😀 )

Ever get given a Excel file to import into SQL Server for “further analysis”?

I’m talking about very nearly unstructured data, a generic “hey I’ve created this excel file for you” which you look at and cringe slightly? A spreadsheet sprinkled with more commas in the fields than there are actual letters in the names!

Not so bad…

I had this recently and was tasked with importing the data into SQL Server.


First off, we have our table:

CREATE TABLE dbo.DiscCharacters (
DiscId int IDENTITY(1, 1) NOT NULL,
Person varchar(50) NULL,
Job varchar(75) NULL,
Notes varchar(300) NULL

Now this import isn’t normally a problem since I just get lazy efficient and use the “Import Data Wizard”:

Right here!
Short Post, no?

However, in this case, I couldn’t because of an unexpected error message…

When providers don’t provide…

Okay, scratch that idea, how about we use nothing but T-SQL then, no GUI gimmicks?

We could try and use OPENROWSET() to retrieve the data but when we try that

USE Pantheon;

‘Excel 12.0 Xml;
Database=C:\Users\soneill\Desktop\In Progress\Blogs\ImportingExcelIntoSqlServerUsingPowerShell\DiscCharacters.xlsx’,
Msg 15281, Level 16, State 1, Line 3
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’
because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure.
For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

Now let’s say that we are working in an environment that does not allow us to change the server configurations, meaning that OPENROWSET() is closed for us.

Being completely honest, my spreadsheet is only 8 rows so I would just manually insert the data but what happens if it’s a few thousand rows big? Not a viable option.

So with most of my T-SQL ideas have been exhausted, let’s look at PowerShell!


Straight away this seems super easy since PowerShell has the cmdlet Import-Csv that should help us out here, as long as the files that are given to us are either a .csv file or can be changed/saved into .csv files.

Lovely, it works!

Now that I have the results off of the file and into memory, it’s a simple enough process to load it into my instance.

Should I stop there? I mean, I’m okay with some manual  processes but this seems like a lot of work…

Opening the .xlsx file, changing the format to a .csv file, and saving it as a .csv file seems like a bit too much effort, especially with those annoying messages about formatting comes up.

Wait, do I say “Yes” or “No” to this?

Hmm, can we use Import-Csv on an xlsx file?

You can’t tell but that’s computer speak for “NO”

Right, so in an effort to get the data out of the Excel file, and into a System.Data.DataTable so I can bulk insert it, I’ve created a function and used that instead.

Check it out here:

$FolderPath = '.';

$CsvFile = 'DiscCharacters.csv';

$XlsxFile = 'DiscCharacters.xlsx';

$Server = '.\SQLServer2K16';

$Database = 'Pantheon';

$ServerConnection = "Data Source='$Server';Integrated Security=true;Initial Catalog=$Database";

$Table = 'dbo.DiscCharacters'

function Import-Excel ($FolderPath, $XlsxFile, $Server, $Database, $Table) {
# Create an Excel workbook…
$Excel = New-Object ComObject Excel.Application;
$Workbook = $Excel.WorkBooks.Open((Join-Path Path (Convert-Path Path $FolderPath) ChildPath $XlsxFile));
$WorkSheet = $Workbook.WorkSheets.Item(1); # Thankfully only 1 sheet so this doesn't need to change…
$StartRow = 2; # …ignore headers…
# Insert into a System.Data.DataTable…
$DataTable = New-Object TypeName System.Data.DataTable;
$null = $DataTable.Columns.Add('DiscId', 'System.Int32');
$DataTable.Columns['DiscId'].AutoIncrement = $true;
$null = $DataTable.Columns.Add('Person', 'System.String');
$null = $DataTable.Columns.Add('Job', 'System.String');
$null = $DataTable.Columns.Add('Notes', 'System.String');
# Load the DataTable…
do {
$Person = $WorkSheet.Cells.Item($StartRow, 1).Value();
$Job = $WorkSheet.Cells.Item($StartRow, 2).Value();
$Notes = $WorkSheet.Cells.Item($StartRow, 3).Value();
$Row = $DataTable.NewRow();
$Row.Person = $Person;
$Row.Job = $Job;
$Row.Notes = $Notes;
} while ($WorkSheet.Cells.Item($StartRow, 1).Value() -ne $null); #…until a gap in values…
$Excel.Quit(); # …then exit…
# Bulk load it…
$BulkCopy = New-Object TypeName Data.SqlClient.SqlBulkCopy ArgumentList $ServerConnection;
$BulkCopy.DestinationTableName = $Table;

Running this function,

Import-Excel -FolderPath . -XlsxFile 'DiscCharacters.xlsx' -Server '.\SqlServer2k16' -Database Pantheon -Table 'dbo.DiscCharacters';

should hopefully populate our table, so we do a quick check of our table…




The hardest part about this was the Excel object, finding out about it was difficult and required straining my Google-fu.

It’s a problem that I find with all Com objects.

It’s also not a great function, hard-coded column names and zero to no validation. If it wasn’t for the fact that I had to run this on a couple of spreadsheets and then relegate to the depths of my “old functions” folder, I wouldn’t have bothered upgrading the script into a function.

If only there was a way that this work could be done for me…

Wait a second…

I’ve talked before about leveraging the hard work done by community members, and this case is no different. Enter Doug Finke ( Blog | Twitter ) and his Import-Excel Module (yeah, you read that right…MODULE, not script. Module).

Here’s a brief snippet from one of the commands in his module: ConvertFrom-ExcelToSqlInsert

That’s just one of the many ((Get-Command -Module ImportExcel).Count; ) 38 commands that I have available from this module and leveraging this, means that I don’t have to play around with ComObjects and can spend my time on other things.
Nice to know that, not only the SQL, but also the PowerShell community has my back 🙂
And remember:
Wisdom comes from experience. Experience is often a result of lack of wisdom
– Terry Pratchett.

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

13 thoughts on “Importing Excel into SQL Server using PowerShell”

  1. Hi, Thanks for this tutorial. This really helped a lot.
    Also, can you please give a tutorial on how to import Multiple Excels along with Multiple Worksheets in Azure Blob Storage to Sql Server.

  2. Computed columns not allowing
    Exception calling “WriteToServer” with “1” argument(s): “The column “GLCode” cannot be modified because it is
    either a computed column or is the result of a UNION operator.”

      I haven’t actually tried to write to an inserted column but I would say that, without seeing your code, it’s going to be hard to troubleshoot this.
      Throw a question up on StackOverflow, tag it with PowerShell and SqlServer and you’re bound to get an answer!
      Hope that helps!

  3. You need to set $ServerConnection
    $ServerConnection = “Data Source=$Server;Integrated Security=true;Initial Catalog=$Database;”

  4. How often did I hear people say…”I have got an Excel ‘database'”…
    Your blog post is very entertaining. If I can’t fight having to import some excel file I gonna try your solution.
    Having handled Excel (mostly) exports with SSIS has frustrated me a lot in the past.

    1. I’ve lost track of the amount of times that I’ve heard that phrase…

      I think you can figure out how much SSIS experience that I have when I didn’t even think of that as an option 😀
      That being said, if you want to create a post on importing Excel to SQL Server using SSIS, I’d be one of the first to read it!

      1. hmm… I like EPPlus.dll (or NPOI.dll). Borh work directly with .xlsx files without requiring Excel to be installed, or dealing with the “help” that the Jet/ACE engines do to the data in the file.

Leave a Reply

%d bloggers like this: