Words: 797
Time to read: ~4 minutesUpdate: 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!

I had this recently and was tasked with importing the data into SQL Server.
T-SQL
First off, we have our table:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE dbo.DiscCharacters ( | |
DiscId int IDENTITY(1, 1) NOT NULL, | |
Person varchar(50) NULL, | |
Job varchar(75) NULL, | |
Notes varchar(300) NULL | |
); | |
GO |
Now this import isn’t normally a problem since I just get lazy efficient and use the “Import Data Wizard”:


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

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;SELECT *
FROM OPENROWSET(
‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0 Xml;
Database=C:\Users\soneill\Desktop\In Progress\Blogs\ImportingExcelIntoSqlServerUsingPowerShell\DiscCharacters.xlsx’,
[Sheet1]);
————————*/
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!
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.

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.

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

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'
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
$DataTable.Rows.Add($Row); | |
$StartRow++; | |
} 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; | |
$BulkCopy.WriteToServer($DataTable); | |
}; |
Running this function,
Import-Excel -FolderPath . -XlsxFile 'DiscCharacters.xlsx' -Server '.\SqlServer2k16' -Database Pantheon -Table 'dbo.DiscCharacters';
Yes!
Inline-OutSourcing:
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
…

(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.Wisdom comes from experience. Experience is often a result of lack of wisdom– Terry Pratchett.
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.
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.”
Hi REJITHKUMAR
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!
You need to set $ServerConnection
$ServerConnection = “Data Source=$Server;Integrated Security=true;Initial Catalog=$Database;”
Thank you twice!
Once for catching what I left out and twice for actually reading my code!
I should have it updated in a few minutes! 🙂
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.
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!
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.
Interesting!
Again I am forced to proclaim my ignorance on these though, do you have any details on how to use these? 🙂