July 21st, 2011

Use a PowerShell Module to Easily Export Excel Data to CSV

Doctor Scripto
Scripter

Summary: Learn how to use a Windows PowerShell module to easily import and export Excel data.

 

Microsoft Scripting Guy Ed Wilson here. Jeremy Engel has joined us today to talk about using a Windows PowerShell module to easily import and export Excel data.

Take it away, Jeremy!

 

I started coding at the age of 10, writing games in BASIC for my lightning-fast Commodore 64. From there I ventured into the high-tech world of text-based online gaming, affectionately known as MUDs—specifically EverDark, where I learned to write C++ code. Later, I made the jump to systems engineering, and it was there that I discovered scripting. I loathe repetitive tasks and have a great love for efficiency (I’m lazy). Therefore, I can find ways to incorporate Windows PowerShell into almost every project and task I do as a systems engineer. I am also known to write the occasional application or service in Visual C#, which is mutually reinforcing with Windows PowerShell because they are essentially different expressions of the same language. Now for today’s blog post. Hope you like it.


Excel to Windows PowerShell and Back Again
 

Ever wish you could import from and export to Excel just like you can using CSV files? So did I, and so did a lot of other people, as I discovered. 

Back during Tech∙Ed this year, it seemed like I spent an entire day griping about this blatant disparity between reality and my imagination. Not that I kept bringing it up (I swear I did not!), but that it kept coming up in conversation. You know, like one of those weird days where the same esoteric word finds its way into a multitude of conversations, only to then never be heard of again until the next cosmic kink in the space-time continuum. 

Anyway, during dinner that night, it came up again. I casually blurt out that it is ridiculous that there are not any such functions, and how I could code them in a single night! Then it struck me—I actually think I could code them in a single night. Before I realized it, I was announcing my plans to the group of fellow Windows PowerShell scripters. So realizing I was losing hours, I dashed home by 8:30 P.M. and set to work. By 1:00 A.M., I had two fairly well fleshed-out cmdlets, but more importantly, they worked! 

The complete Excel module can be downloaded from the Scripting Guys Script Repository. 

As a basis for understanding how the conversion between Excel and Windows PowerShell works, think of each row in an Excel spreadsheet as an object in Windows PowerShell with the names of each column in Excel being a property name of the object in Windows PowerShell. 

Image of how Excel spreadsheets correlate to objects in Windows PowerShell

So if the value for A1 is Example1 and the value for A2 is test, in PowerShell: 

$row.Example1 = “test” 

If the value for A3 is ubu, in Windows PowerShell: 

$row.Example1 = “ubu” 

Each row object is then appended to an array of these objects, eventually representing the entire spreadsheet in Windows PowerShell object form.

Image of spreadsheet in Windows PowerShell object form

Got it? When you understand this, it becomes easy to see how we could move from Excel to Windows PowerShell and back again. All that is left then is to work out the mechanics. 

I really wanted this to be Excel-independent, meaning that I wanted to read an Excel document as an XML file and vice versa. I was able to get the export cmdlet to work fine writing to XML, but I was not able to figure out how to get the Excel XML to do an import—it was all just a jumbled mess. So unfortunately, I had to stick with the “wonderful” COM object. 

Honestly, COM objects are not that bad. They just look really bad when you’re expecting them to be as flexible, transparent, fast, and disposable as Windows PowerShell objects. That aside, they do have a fair amount of useful methods and properties, which I make use of in these two cmdlets. I will not elucidate them all here, but just show you some of the important ones. 

$excel = New-Object -ComObject Excel.Application

$workbook = $excel.Workbooks.Open($Path)

$sheet = $workbook.Worksheets.Item(1) 

As you can see, I am creating an instance of the Excel.Application object and then opening the workbook located at $Path and from there retrieving the first worksheet. What do you mean you have Excel workbooks with more than one worksheet? Okay, I’ll work on an enhancement later. When, you ask? Later. 

Here is another quirk about COM objects. If you take those first two lines of Windows PowerShell code shown above and open up Excel workbook, you’ll notice that typing $workbook.Worksheets in Windows PowerShell looks like it shows you an array of worksheet objects. Alas, no, it is actually another COM object. So I couldn’t just pull the object for sheet 1 by doing $workbook.Worksheets[0]; I had to use a COM method called Item(). Fun, huh? 

One last bit on COM objects. Apparently, COM objects are like that quasihomeless guy who sleeps on your couch. They are extremely hard to get rid of. I alluded to the problem in the preceding paragraph, which is that COM objects like to return more COM objects, rather than property values. So you think you may be querying a value, when what you’re getting is a sneaky cockroach of a COM object. 

Because of that, in each cmdlet I had to go through and try to repeatedly wipe out each object of the COM before finally killing the mothership. And wow, in doing my previous example on the worksheets, I just realized that even though I wasn’t saving the COM object references for workbooks and worksheets to Windows PowerShell variables, they were still lying out there. That is why I was sometimes having trouble killing the Excel process. So I had to adjust my script slightly to release those as well! 

$workbooks = $excel.Workbooks

$workbook = $workbooks.Open($Path)

$worksheets = $workbook.Worksheets

$sheet = $worksheets.Item(1)

 

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($columns) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($rows) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($range) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheets) } while($o -gt -1)

$workbook.Close($false)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbooks) } while($o -gt -1)

$excel.Quit()

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) } while($o -gt -1)

 

It isn’t pretty, but it gets the job done. 

I tried to make the Import-Excel cmdlet as flexible as possible, knowing how people often have messy spreadsheets. So I needed to account for situations when all of the columns were missing headers (the NoHeaders switch), or if only some of the columns had no headers. Under the latter situation, could also fall stray cells out in the middle of nowhere. I didn’t want to make value-judgments on any of that data; I just wanted to make sure I could logically ingest it into Windows PowerShell. To do that, the Import-Excel script creates generic property names for missing headers. 

In the previous screen shots, did you notice how the Excel document had no header for column B? The cmdlet noticed that too and gave it a generic property name, in this case: Column2

After you have imported the spreadsheet, you can manipulate the objects just like any other in Windows PowerShell. In the following figure, I fill in item 3’s Example1 value. 

Image of manipulating the object

In the following figure, I add an entirely new item to the object array. 

Image of adding new item to object array

Converse to Import-Excel is Export-Excel, which takes an array of objects and writes them to an Excel file. For a little flare, I added in the ability to make the header row bold, and also gave you three choices of bottom border, if so desired. 

Image of writing objects to an Excel file

And that, my friends, is how a self-imposed dare is won!

 

Thank you, Jeremy, this is a great post, and I look forward to using your new Excel module.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

 

 

Author

The "Scripting Guys" is a historical title passed from scripter to scripter. The current revision has morphed into our good friend Doctor Scripto who has been with us since the very beginning.

0 comments

Discussion are closed.