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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |