Exporting data to Excel

This post is to provide some background into a business solution I had to solve recently. While the code block isn't something I derived myself it definitely came to my help, thanks nixda! I want to ensure others can benefit from my troubles in finding a nice way of exporting data from PowerShell to Excel.

Due to a system upgrade, auditors required SQL data comparisons on a frequent basis - my customers life was going to be very painful doing this manually...

The process was to retrieve SQL data from two systems, compare the data to find any mismatches, export the comparison results into an Excel spreadsheet, provide some traffic lights for easy identification of which data sets matched up, and finally email the Excel file to the customer so it could be used as both an internal check and something that could be forwarded to the auditors. All in all I think the end result was great, a 5 minute script the customer could run rather than a multi-day manual comparison.

However, in writing this script I found the process to export arrays into Excel was actually quite painful! My initial thought was to format the data from PowerShell using format-table, then pipe this into the clipboard (using 'clip.exe') so the data was available for pasting. #FAIL. Well it did work, except the data was (in hindsight, obviously) copied simply as text. No formatting, no nice way of placing this data into excel columns easily.

Next!

Perhaps I could simply process each row/column of excel and write the data from the arrays in, one record at a time. I've used this process before so I knew it would work - however I've only ever needed to do this using VBA, not from PowerShell using Excel com objects.

I coded it and it worked, and would've provided the required result for the customer; however it was terribly slow. The writing of data averaged out to around one cell of data every second! Would it provide the correct outcome? Yes! Would the performance be good enough for business? No. There was no room in my mind for this script to be a multi-hour process.

Next!

At this point I started hunting around the internet to see what other people had coded to see if there was anything to jump start my thinking: "powershell export data to excel", "powershell paste clipboard to excel", perhaps "export powershell csv to excel" <- winner!

From that search I came across a very useful response to a question on stackoverflow.com by the user nixda. Here is the code as posted:

### Set input and output path
$inputCSV = "C:\somefolder\input.csv"
$outputXLSX = "C:\somefolder\output.xlsx"

### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()

Well this definitely looks positive! I already had a bunch of this code; to create the Excel object, a new WorkBook and WorkSheet, however the section from lines 10 through 27 looked to be exactly what I was after. The end result was to export my hashtable to a .csv file, then import that file using Excel's QueryTables functionality to perform this really quickly.

I know this block isn't my code but I wanted to share it again in case anyone else had a similar problem to solve.

Since proving my solution to business, I saw a tweet from Doug Finke's (@dfinke) linking to his PowerShell module for creating Excel spreadsheets (with pivot tables and charts) without Excel being installed. Here are the links to Doug's tweet and github repository for this module. While I haven't tried this module yet I'm sure it could've made my life easier.