Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to Export Excel data to SQL Server with Powershell

04 January,2013 by Tom Collins

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()
    While ($Worksheet.Cells.Item($startRow,1).Value() -ne $null) 

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

 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

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to Export Excel data to SQL Server with Powershell | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer