Excel to CSV with Powershell utility

Nov 26, 2014 at 3:49 PM
Edited Nov 26, 2014 at 5:50 PM
.\ExcelToCSV.ps1 <somefile.xlsx> | out-file -filepath <somefile.csv> -Encoding ASCII
# ExcelToCSV.ps1
# 11/26/2014 rscarseth
param($inputfile)
Write-Host ("ExcelToCSV.ps1`n--------------")

$PSScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition

# The wonderful DLL...
# http://epplus.codeplex.com/
$RequiredDLL = "EPPlus.dll"
$DLLpath = ($PSScriptRoot + "\" + $RequiredDLL)

if(!(Test-Path $DLLpath)){
    Write-Host ($RequiredDLL + " must be in same directory as this script.")
    exit
}

if(!($inputfile)){
    Write-Host ("You must provide an XLSX file to use.")
    exit
}

if(Test-Path $inputfile){
    [System.IO.FileInfo]$fileinfo = $inputfile
    [Reflection.Assembly]::LoadFrom($DLLpath) | Out-Null

    $E = New-Object OfficeOpenXml.ExcelPackage $fileinfo 
    $WS = $E.Workbook.Worksheets[1]
    $cols = $WS.Dimension.End.Column
    $rows = $WS.Dimension.End.Row

    for($i = 1;$i -lt $rows;$i++){
        $line = ""
        for($j = 1;$j -le $cols-1;$j++){
            [string]$val = $WS.getvalue($i,$j)
            if($i -eq 1){$val = $val.replace(" ","_")}
            $line += ($val + ";")
        }
        [string]$val = $WS.getvalue($i, $cols)
        if($i -eq 1){$val = $val.replace(" ","_")}
        $line += $val
        Write-Output $line
    }
    $E.Dispose()
}