PowerShell: Read Excel to Create Inserts

BuckWoody — Thu, 18 Mar 2010 12:09:36 GMT

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.

Tools and Processes for “Fitting it all in”

BuckWoody — Mon, 18 Jan 2010 14:42:05 GMT

Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done – it’s optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may “change state” such that I need to give them some attention.

So how do I meld the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.

I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town.

For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines.

Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.

So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?

Search results matching tags 'PowerShell', 'Tips', 'Web', and 'Scripts'

PowerShell: Read Excel to Create Inserts

Tools and Processes for “Fitting it all in”