Load Excel Data to SQL Server using PowerShell

Windows PowerShell is a tiny yet powerful utility tool used by DBAs & Microsoft developers all around the world. It can do everything from creating folders to moving files to restoring databases by using PowerShell Scripting: a compact scripting language that simplifies life for us developers.

In this blog, I would love to share my learning on this cool PowerShell feature for excel import using "ImportExcel" module.

Have you ever heard of the traditional Import/Export Wizard in MS-SQL? It's an add-on in MS-SQL that allows you to manually import flat files into your database. It's a manual work for loading files into SQL using a wizard.

Instead, imagine that you want to automate the process of loading Excel data into SQL Server. How would you do it? Some people might suggest using SSIS to load the data easily on SQL Server. They're entitled to their perspectives but in my opinion, SSIS can be a bit tricky when it comes to loading Excel data.

Imagine creating an Excel based SSIS package & loading new data into the database on a weekly or daily basis & every single time the dynamic Excel file is updated. It's possible to handle this in SSIS but it's more complex than it seems.

What else can we do? What are our other options? That's where Powershell comes in: it's a tool which can quite literally take care of almost everything. Think I'm exaggerating? Read on!

I don't mean to demean other options, or even SSIS in general. I have worked on SSIS & I do appreciate how it can simplify ETL tasks. These tasks can't be done in PowerShell that easily but I'm just here to provide an alternative way to load Excel data.

Let's start with a very interesting tool for executing PowerShell Scripts: PowerShell Integrated Scripting Environment (ISE). If you have not tried this & still execute commands in a command prompt, you should give PowerShell ISE a try. In this article, we will examine an alternate option for securely loading Excel files into the SQL Server database.

Let's get our hands dirty with PowerShell now, shall we?

Here's a module which you should be aware of when you are loading excel data into SQL: "ImportExcel". A fabulous module which has a bunch of functions of which the one most important function for us here is "ConvertFrom-ExcelToSqlInsert". This function basically accepts your target table name, & excel file path. It can also convert the Empty strings to NULL & can use MS-SQL Syntax which is pretty cool! Here's how you use it:

$ConvertToSqlParams = @{TableName = 'dbo.Test_Table'
Path = 'C:\\Users\\demo\\Documents\\test.xlsx
ConvertEmptyStringsToNull = $true
UseMSSQLSyntax = $true}
$SqlInsertStmts = ConvertFrom-ExcelToSqlInsert @ConvertToSqlParams
$SQLQuery = $SqlInsertStmts|Out-String

When you run this above piece of code, you'd get a bunch of insert statements written in a format suited for MS-SQL on a per-row basis. You would need headers in your excel spreadsheet. The header names should match the column names in the SQL Table.

What is a per-row basis? — Imagine in your excel spreadsheet you have the first row as a header and the following rows as data for those headers, you'll have something like this:

INSERT INTO dbo.Test_Table (header1, header2, header3) VALUES (value1, value2, value3);

Impressed? To be frank, so was I. You can then store this in a variable and run these SQL Insert statements using a single command as follows:

$result = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance "YourServerInstance" -Username "YourUserName" -Password "YourPassword" -Database "YourDatabase" -ErrorAction Stop

What is "Invoke-Sqlcmd"? It is a function developed by Microsoft just for running SQL Queries using PowerShell!

What's our final output going to be?

You'll generate your insert statements and will run those statements all at once in PowerShell using Invoke-Sqlcmd.

At this point, you're probably wondering how it will handle large amounts of data. How does the "Import-Excel" module compare to SSIS & which option is better for efficient utilization? I haven't yet tested it for large amounts of Excel data but it's important to remember this method focuses on efficiency & simplicity rather than quantity.

To answer the inevitable follow-up question- yes, you are able to dynamically select any files in PowerShell, rename it to something generic, and use that file to load data into SQL Server. It's ingeniously simple.

Now, let's talk about another key aspect of writing code: security. It's important to remember that we're all prone to cyberattacks when writing a PowerShell Script or any other code. In this case, a cyberattack is definitely possible but if proper encryption & decryption logic is followed, the risk can be mitigated or even eliminated. If you are going to connect to SQL Server, you need to guarantee security. How do we do this? Here is a sample encryption-decryption method which you can follow.

If we are going to execute SQL queries using PowerShell, we need to encrypt the SQL credentials by using Powershell & SQL Agent job.

Why SQL Agent Job:

Suppose you want to encrypt a password used in PowerShell and you want to schedule this PowerShell script, then you have to encrypt the password using SQL Agent job.

Why? The reason being: "the person who encrypts the password is the only one who can run the decryption on it" — at least in PowerShell. To avoid user-dependent processes, we should always use SQL Agent jobs for encryption & decryption. If you want to encrypt it as a Windows user, you run the PowerShell script in ISE (using the following script) and can run the decryption script with the same Windows credential you use to log into your system.

Here's how you can do it using SQL Agent job:

Step 1: Encryption

Below is a PowerShell script with a sample name: encryptPassword.ps1

param(  
 \[Parameter(Mandatory=$true)\]  
 \[String\]$password  
)  
   
$SecurePassword=ConvertTo-SecureString $password –asplaintext –force  
$SecurePassword |ConvertFrom-SecureString |Out-File C:\test\test\Password.txt

The above script accepts the password as a parameter & encrypts the password in a particular location. You can run this Powershell script in a PowerShell Integrated Scripting Environment (ISE) or a SQL Agent job.

When you use PowerShell ISE, it will encrypt the password sent in as a parameter. Remember- only your login will be able to decrypt it, so if you end up leaving the company and someone else wants to decrypt the password, they're out of luck.

A good way to deal with this problem is by creating a one time SQL Agent job: "Encrypt Password job".

Then create a new step called "Test Encryption" and use the above PowerShell script to pass the password as a plain text:

Click OK & create the job. Next, execute the job and watch a successful password encryption file appear at the location you chose. (NOTE: This location has to be provided in PowerShell script & it has to be accessible by the SQL Agent Job user).

Remember: This is a one-time thing. You should delete this job once the encrypted password file is generated.

STEP 2: Decryption

Now that you have successfully encrypted the file using a SQL Agent job, you can easily decrypt it using another Powershell script (the main script that uses SQL commands)

$EncryptedSQLPass= Get-Content -Path C:\test\test\Password.txt
   
$Password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $EncryptedSQLPass)))  
   
#//sample SQL code//
$SQLQuery = 'select * from dbo.Test;'  
   
$result = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance YourServerInstance -Username YourUserName -Password $Password -Database YourDatabase -ErrorAction Stop  
   
#//sample SQL code//  
#……..some more of your code……..

The above PowerShell script is a sample that executes SQL Command. I have used Invoke-Sqlcmd here but you can use any relevant SQL command. The only difference is the first two lines of code, which are essential for decrypting the password.

We use "Marshal" Class in our script as this class uses a method "SecureStringToBSTR(SecureString)". This method allocates an unmanaged binary string (BSTR) and copies the contents of a managed SecureString object into it.

The other method, "PtrToStringAuto" allocates a managed String and copies all or part of an unmanaged string into it.

The below query is used for user-specific decryption- so only the specific user who encrypted the password can use this command:

[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $EncryptedSQLPass)))

Marshal Class Reference: https://docs.microsoft.com/en-us/dotnet/api/system.runtime.interopservices.marshal?view=netframework-4.8

Since our password was encrypted in SQL Agent job, this PowerShell script should also be passed in an existing SQL Agent job as a step. These methods will allow you to successfully decrypt the password at runtime without other users knowing the actual password for the file.

Thank you doug finke for this super cool module "ImportExcel", as well as Casey Catasus for helping me identify the importance of encryption in PowerShell.