Import data from XLSX files conveniently like import-csv lets you do with simpler data.<\/span> <\/span><\/p>\n Excel is a mainstay of the business world at this point, which means a lot of the data you might have to work with will come at you as an XLSX file or need to be one. This can be a bit annoying when scripting. <\/span><\/p>\n If we’re just working in PowerShell-land and we can choose to use simple CSV data we have the handy import-csv and export-csv cmdlets, and those CSV files can open up in excel just fine. However, when we are forced to work with XLSX files it can lead to headaches. <\/span><\/p>\n If you search around online, or have worked with excel in PowerShell before, you have probably found solutions involving COM objects that tend to start a little like this:<\/span><\/p>\n \nThen there is lots of methods and properties we can interact with on that COM object. This lets us do what we need, but ultimately leverages excel and can cause huge performance issues when working with a lot of data. Often you might see the excel process stop responding for a while and then finally it finishes its work. This is clunky, confusing and un-fun. <\/span><\/p>\n I recently had a folder full of XLSX files that I needed to read in. I only cared about a couple columns, and I wanted to import them as objects like I could with import-csv and then pull only unique values out.<\/span><\/p>\n I leveraged the PowerShell Gallery and just searched for “Excel”. There is actually quite a few different options there now, but at the time I saw the description for a module called PSExcel<\/a>, by a fellow named RamblingCookieMonster. The description was simple: <\/span><\/p>\n Work with Excel without installing Excel<\/span><\/p><\/blockquote>\n That sounded good enough to me, so I figured I’d take it for a spin. <\/span><\/p>\nThe preamble:<\/h2>\n
$ExcelFile = New-Object -ComObject Excel.Application<\/pre>\n<\/div>\n
The motivation:<\/h2>\n
The meat:<\/h2>\n
Install-module PSExcel\r\nGet-command -module psexcel<\/pre>\n<\/div>\n
CommandType Name Version Source\r\n----------- ---- ------- ------\r\nFunction Add-PivotChart 1.0.2 psexcel\r\nFunction Add-PivotTable 1.0.2 psexcel\r\nFunction Add-Table 1.0.2 psexcel\r\nFunction Close-Excel 1.0.2 psexcel\r\nFunction ConvertTo-ExcelCoordinate 1.0.2 psexcel\r\nFunction Export-XLSX 1.0.2 psexcel\r\nFunction Format-Cell 1.0.2 psexcel\r\nFunction Get-CellValue 1.0.2 psexcel\r\nFunction Get-Workbook 1.0.2 psexcel\r\nFunction Get-Worksheet 1.0.2 psexcel\r\nFunction Import-XLSX 1.0.2 psexcel\r\nFunction Join-Object 1.0.2 psexcel\r\nFunction Join-Worksheet 1.0.2 psexcel\r\nFunction New-Excel 1.0.2 psexcel\r\nFunction Save-Excel 1.0.2 psexcel\r\nFunction Search-CellValue 1.0.2 psexcel\r\nFunction Set-CellValue 1.0.2 psexcel\r\nFunction Set-FreezePane 1.0.2 psexcel<\/pre>\n<\/div>\n