How to Export Excel data to SQL Server with Powershell

I work with various techniques as part of ETL , such as bcp,SSIS and BULK INSERT.  I’m using Powershell increasingly as a method to export \ import data from Excel spreadsheets to SQL Server databases.

In the example below , an object is created to access the Excel worksheet. The worksheets consists of two columns – Musicians and Instruments.

The System.DataTable is created with two columns, which acts as the data store to temporarily place the Excel data.

A connection is created to a SQL Server and a  bulk copy object is instantiated to export the data into SQL Server.

This is a simple example, but you can see the flexibility available in manipulating data  before exporting to SQL Server using Powershell

Note: This script exports Excel data into a SQL Server table

the $filepath variable should reference an Excel file with a worksheet and data

#some variables
$serverName = "SERVER1\MY_INST1"; 
$databaseName = "MY_DB" ; 
$tableName = "excelImport" ; 
$filepath = "C:\excelimport.xls"; 

#create object to open Excel workbook
$Excel = New-Object -ComObject Excel.Application 
$Workbook = $Excel.Workbooks.Open($filepath) 
$Worksheet = $Workbook.Worksheets.Item(1) 
$startRow = 1 

#create System.DataTable
$dt = new-object "System.Data.DataTable"
[void]$dt.Columns.Add("Musician", [System.Type]::GetType("System.String")) 
[void]$dt.Columns.Add("Instrument", [System.Type]::GetType("System.String"))
Do { 
    $ColValues1 = $Worksheet.Cells.Item($startRow, 1).Value()
    $ColValues2 = $Worksheet.Cells.Item($startRow, 2).Value()
    $startRow++
    $dt.Rows.Add($ColValues1,$ColValues2)
    }
    While ($Worksheet.Cells.Item($startRow,1).Value() -ne $null) 
   
$Excel.Quit() 

#connect to SQL Server and import the system.data.table 
$SQLServerConnection = "Data Source=$serverName;Integrated Security=true;Initial Catalog=$databaseName;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $SQLServerConnection 
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.WriteToServer($dt)

 Read More on  working with Powershell & Excel 

Powershell , Excel charts and data presentation - SQL Server DBA

Powershell – adding multiple worksheets to Excel ... - SQL Server DBA

Export-CSV Powershell - SQL Server DBA

SQL Server - Generate SQL INSERT from Excel - SQL Server DBA