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!

SpreadsheetSample
Not so bad…

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”:

ImportDataMenu
Right here!
ImportDataWizard
Short Post, no?

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

ImportDataMenuError
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;

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.

ImportCsv
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.

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

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

ImportXlsx
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'

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…

ItWorks

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

dougcommand.PNG
Thud!
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)...

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

  1. 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.

    Liked by 1 person

    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!

      Like

      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.

        Liked by 1 person

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s