PoshCode Logo PowerShell Code Repository

Import-ExcelToSQL by Chad MIller 4 years ago (modification of post by Chad MIller view diff)
diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1098"></script>download | new post

Imports an Excel spreadsheet to a SQL Server table using OLEDB

  1. #Change these settings as needed
  2. $filepath = 'C:\Users\u00\Documents\backupset.xlsx'
  3. #Comment/Uncomment connection string based on version
  4. #Connection String for Excel 2007:
  5. $connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
  6. #Connection String for Excel 2003:
  7. #$connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"
  8. $qry = 'select * from [backupset$]'
  9. $sqlserver = 'Z002\SQLEXPRESS'
  10. $dbname = 'SQLPSX'
  11. #Create a table in destination database with the with referenced columns and table name.
  12. $tblname = 'ExcelData_fill'
  13.  
  14. #######################
  15. function Get-ExcelData
  16. {
  17.  
  18.     param($connString, $qry='select * from [sheet1$]')
  19.  
  20.     $conn = new-object System.Data.OleDb.OleDbConnection($connString)
  21.     $conn.open()
  22.     $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn)
  23.     $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
  24.     $dt = new-object System.Data.dataTable
  25.     [void]$da.fill($dt)
  26.     $conn.close()
  27.     $dt
  28.  
  29. } #Get-ExcelData
  30.  
  31. #######################
  32. function Write-DataTableToDatabase
  33. {
  34.     param($dt,$destServer,$destDb,$destTbl)
  35.  
  36.     $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
  37.     $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
  38.     $bulkCopy.DestinationTableName = "$destTbl"
  39.     $bulkCopy.WriteToServer($dt)
  40.  
  41. }# Write-DataTableToDatabase
  42.  
  43. #######################
  44. $dt = Get-ExcelData $connString $qry
  45. Write-DataTableToDatabase $dt $sqlserver $dbname $tblname

Submit a correction or amendment below (
click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me