Export multiple Excel worksheets to a single SQL Server table

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 1^st 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