Export multiple Excel worksheets to a single SQL Server table

06 June,2014 by Jack Vamvas

Question:How do I export multiple Excel worksheets to a single SQL Server table? I’ve read Export Excel data to SQL Server with Powershell , but it exports data from one worksheet into a SQL Server table.

Answer: With a slight adjustment to the script on Export Excel data to SQL Server with Powershell , it is possible to iterate through multiple worksheets on a Excel spreadsheet .  

You’ll need to adjust the variable $worksheetTotal to the number of worksheets. This example starts with the 1st worksheet and works through to the total stated.

This example , takes the first two columns of the worksheet and inserts into a SQL table called ExcelImport

 

#some variables
$serverName = "SERVER1\INSTANCE1"; 
$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) 

#how many worksheets?
$worksheetTotal = 2 
#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"))

$worksheetCurrent=1
#loop through the worksheets
Do {
write-host $worksheetCurrent
$startRow = 1 
$Worksheet = $Workbook.Worksheets.Item($worksheetCurrent)

	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) 
$worksheetCurrent++
}
while ($worksheetCurrent -le  $worksheetTotal) 


$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)


Share:

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 on Export multiple Excel worksheets to a single SQL Server table


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