How to read Excel files in PowerShell.

I picked up a post of Lee Holmes’ entitled Does Your Hard Work Advance the Ecosystem; which  joins a discussion that started with what Lee terms a “fairly chewy piece” on the Lync PowerShell blog. That was about reading stuff from the cells of an Excel spreadsheet to set up users. Over here Jason basically says “very clever and all that, but wouldn’t you just convert to .CSV ?”  Lee’s piece is worth reading in full, but the key points are

1. Some people will do things the hard way.  A few lines of PowerShell can replace hundreds in something like C#. [Side note: I’ve been working in C# recently and I know this only too well]. The experience of some hard-core programmers stops them imagining an easy way.
2. There isn’t anything built into PowerShell to read XLS / XLSx files, so showing people how Excel can be used does move things forward. And PowerShell is about automating: starting a process by manually loading files into Excel and converting isn’t ideal.
3. It is easy to write up and share what you did for a specific case. But a generic is better –providing an “Import-XLS” command is more useful than CreateLyncUsersFromXLS.PS1

I have to agree with both Jason and Lee – one probably would load the file in the Excel, and that isn’t the ideal. I’ve been manipulating the Office object model on and off since the early 1990’s so I really get Jason’s point that there is more to learn there than makes sense for an admin to pick up to get round the problem of people not saving in .CSV format when asked.

So here, for any admin confronted with that problem is ConvertFrom-XLx – it accepts a path to a file, or a file object from the pipeline ,  (so dir *.xlsx | convertFrom-XLx will work). It checks that the file exists, and ends with .XL<something> – that’s the little regular expression used with the -match and -replace operators. The -replace puts .CSV into the save name and the file is then opened, saved as CSV (that’s type 6 in a SaveAs operation), and closed (false says don’t stop to ask the user anything on closing)
Finally if a –PassThru parameter is specified the CSV file can be read in and passed to the next step in a pipeline.  

function ConvertFrom-XLx {
  param ([parameter(             Mandatory=$true,
                         ValueFromPipeline=$true,
           ValueFromPipelineByPropertyName=$true)]
         [string]$path ,
         [switch]$PassThru
        )

  begin { $objExcel = New-Object -ComObject Excel.Application }
Process { if ((test-path $path) -and ( $path -match ".xl\w*$")) {
                    $path = (resolve-path -Path $path).path
                $savePath = $path -replace ".xl\w*$",".csv"
              $objworkbook=$objExcel.Workbooks.Open( $path)
              $objworkbook.SaveAs($savePath,6) # 6 is the code for .CSV
              $objworkbook.Close($false)
              if ($PassThru) {Import-Csv -Path $savePath }
          }
          else {Write-Host "$path : not found"}
        }
   end  { $objExcel.Quit() }
}