Load data directly in SQL Server from a xlsx file, without OLEDB or Excel, using Powershell

I don’t know you, but people from other BU are old fashioned guys that do everything by hand, and then send you an Excel file to “put this data in the database” or “find info related to this codes”.

This is an hassle, especially if you don’t have the Microsoft.ACE.OLEDB provider installed in your machine (or for some reason SSMS doesn’t recognize it or you have the wrong version) and hence you cannot use the Import Data task (which is garbage, anyway).
A quick solution that I used in the past is saving the xlsx file as CSV and then use the flat file import wizard to quickly import the data into my staging DB; Still, some characters may prove problematic in a CSV file and you have to choose the right separator, when you have an Excel file with more than one worksheet you have to save more than one CSV file and don’t get me started in how the Flat File Import Wizard analyzes only the first rows of the file and hence chooses datatypes and nullable columns in a non-reliable way (aka I cannot press Advance->Advance->Advance->Done without looking, unaccepptable)

Here is the code

Since I’m lazy, I scripted out a Powershell file that does the trick automatically for me, here it is for you:

$File = "C:\Users\emeazzo\WhereverIKeepMyFiles\MyFile.xlsx"
$Instance = "YourSQLInstance"
$Database = "TheDBWhereYouPutThisStuff"

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($File)

foreach($sheet in Get-ExcelSheetInfo $File)
{

$data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable
$tablename = $fileName + '-' + $sheet.Name

Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -AutoCreateTable -Table $tablename

}

In my case, it loads the data in the Database that I’ve specified using the Filename as the first part of the Table name and it concatenates the worksheet name as second part of the table name, nothing fancy.

Modules Info

As you can see, I’m using the dbatools and ImportExcel PS Modules, which are free, open source and you can install them like this:

Install-Module -Name ImportExcel
Install-Module -Name dbatools

#Or, If you don't have admin rights or don't want to install these for all users
Install-Module -Name ImportExcel -Scope CurrentUser
Install-Module -Name dbatools -Scope CurrentUser

The ImportExcel module is pretty neat for working with excel files in a programmatical way, without having to use COM Objects and all the usual mess. DBATools is a massive repository of scripts for all kind of SQL Server professionals, especially DBAs, but not limited to imho.

That’s it! You can implement this in whatever manual, automatic or semi-automatic file pipeline that you have, and enjoy not having to upload file manually.