SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Export Excel data to SQL Server with Powershell

04 January,2013 by Jack Vamvas

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

 

#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

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: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

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

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer