THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

PowerShell: Read Excel to Create Inserts

I’m writing a series of articles on how to migrate “departmental” data into SQL Server. I also hold workshops on the entire process – from discovering that the data exists to the modeling process and then how to design the Extract, Transform and Load (ETL) process. Finally I write about (and teach) a few methods on actually moving the data.

One of those options is to use PowerShell. There are a lot of ways even with that choice, but the one I show is to read two columns from the spreadsheet and output statements that would insert the data using a stored procedure. Of course, you could re-write this as INSERT statements, out to a text file for bcp, or even use a database connection in the script to move the data directly from Excel into SQL Server.

This snippet won’t run on your system, of course – it assumes a Microsoft Office Excel 2007 spreadsheet located at c:\temp called VendorList.xlsx. It looks for a tab in that spreadsheet called Vendors. The statement that does the writing just uses one column: Vendor Code. Here’s the breakdown of what I’m doing:

In the first block, I connect to Microsoft Office Excel. That connection string is specific to Excel 2007, so if you need a different version you’ll need to look that up.

In the second block I set up a selection from the entire spreadsheet based on that tab. Note that if you’re only after certain data you shouldn’t get the whole spreadsheet – that’s just good practice.

In the next block I create the text I want, inserting the Vendor Code field as I go.

Finally I close the connection.

Enjoy!

$ExcelConnection= New-Object -com "ADODB.Connection"
$ExcelFile="c:\temp\VendorList.xlsx"
$ExcelConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;`
Data Source=$ExcelFile;Extended Properties=Excel 12.0;")

$strQuery="Select * from [Vendors$]"
$ExcelRecordSet=$ExcelConnection.Execute($strQuery)

do {
Write-Host "EXEC sp_InsertVendors '" $ExcelRecordSet.Fields.Item("Vendor Code").Value "'"
$ExcelRecordSet.MoveNext()}
Until ($ExcelRecordSet.EOF)

$ExcelConnection.Close()

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Published Thursday, March 18, 2010 6:09 AM by BuckWoody

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter said:

Does this handle mixed data types within a column well? e.g. float and character data mixed  That's a huge pain point w/ Excel right now and wondering if this might handle it in a more expected manner.

March 18, 2010 11:14 AM
 

BuckWoody said:

Oh yes - by making a statement like that, you have full control over what kind of data is inserted into SQL Server, regardless of what Excel stores it as. Best of all worlds, for me.

March 18, 2010 12:36 PM
 

Alexander Kuznetsov said:

I would be very careful with such approaches. In the past we have been burned multiple times by such imports - they claim that they succeed, raise no errors, but sometimes they silently fail to import some cells.

We switched to using C# to read from Excel and write into the database, and do not experience such problems. It takes less than an hour of simple C# programming to develop an import from Excel that actually works: reports success only if it does import all cells, or raises an error if it encounters a problem.

March 18, 2010 12:38 PM
 

BuckWoody said:

Very good point Alexander - you did read my disclaimer, correct? Nobody seems to read those anymore. Anyway, I clearly state that you should test the script and understand it completely. I NEVER recommend a single approach for anything - this is just another way to skin the cat.

Thanks for posting!

March 18, 2010 12:50 PM
 

Chad Miller said:

You could take this a step further and actually import the data without having to write any C# code:

#Change these settings as needed

$filepath = 'c:\temp\VendorList.xlsx'

#Connection String for Excel 2007:

$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"

#Connection String for Excel 2003:

#$connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"

$qry = 'select * from [Vendors$]'

$sqlserver = "Z002\SQLEXPRESS"

$dbname = 'Vendors'

#Create a table in destination database with the with referenced columns and table name.

$tblname = 'VendorList'

$conn = new-object System.Data.OleDb.OleDbConnection($connString)

$conn.open()

$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn)

$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)

$dt = new-object System.Data.dataTable

[void]$da.fill($dt)

$conn.close()

$connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

$bulkCopy.DestinationTableName = "$destTbl"

$bulkCopy.WriteToServer($dt)

March 18, 2010 5:27 PM

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement