Importing Excel into SQL Server using PowerShell

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!

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

T-SQL

First off, we have our table:

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'

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…

Wisdom comes from experience. Experience is often a result of lack of wisdom

– Terry Pratchett.