How to handle multiple spreadsheets from Excel in Powershell

Posted in : Azure, Microsoft, Office 365, Powershell, Windows Av Johan Nilsson Översätt med Google ⟶

5 years ago

Background
Most of us working with Powershell has encountered imports and exports of a CSV-file in Powershell, and most of the times this works seamlessly. This is because the Import-CSV function is a great way to get the single Excel sheet imported in to Powershell. However, I got a request to merge several spreadsheets from a xlsx-file and having them exported into one CSV row. My first thought was I should just rename the file from “sheets.xlsx” to “sheet.csv”, but I quickly noticed that CSV-format was unable to handle multiple sheets.
With this information, I was stuck in a crossroad. I could either say that this wasn’t possible, or I could try to find a solution. But the thing that I enjoy by far the most with Powershell is to create a solution to a problem that requires you to create a script that requires logic and that has been thought through. This is why this was a no brainer, and why I really enjoyed coming up with this way to manage .xlsx-sheets. The final result is shown below.
Please read the comment based help for further understanding of the script

<#
.Synopsis
    Allows you to convert one or multiple spreadsheets from a .xlsx file to one .csv file
.DESCRIPTION
    Parameter:
        FilePath - This parameter is mandatory. Specify the exact path of the .xlsx-file
            Example: C:\temp\file.xlsx
        SheetName - This parameter is NOT mandatory. This is only if you want to specify one single spreadsheet.
        OutFilePath - This parameter is mandatory. Specify the path to the exported final file. If the path is not existing, it will be created.
            Example: C:\temp
                If C:\temp is non existing, it will be created
        OutFileName - This parameter is mandatory. Specify the name (WITHOUT FILE EXTENSION)
            Example: SuperCoolFile
.EXAMPLES
    1. Convert-ExcelSheets -FilePath C:\temp\Import.xlsx -OutFilePath C:\temp -OutFileName VeryCoolFile
        This will result in a CSV file with all the spreadsheets imported to one file, and the export will be located under C:\temp and will be named VeryCoolFile.csv
    2.  Convert-ExcelSheets -FilePath C:\temp\Import.xlsx -SheetName 'The only sheet I want' -OutFilePath C:\temp -OutFileName SingleCoolSheet
        This will result in a CSV file containing ONLY the data located in the 'The only sheet I want'-sheet. The file will be exported to C:\temp and be named SingleCoolSheet.csv
.NOTES
    Name: Script name Convert-ExcelSheets.ps1
    Author: Johan Nilsson
    Date Created: 2019-06-20
    Version History:
        2019-06-20 - Johan Nilsson
            Initial Creation
        2019-06-28 - Johan Nilsson
            Fixed issues with the progress bar
    Xenit AB
#>
function Convert-ExcelSheets {
    param(
        [parameter(
            Mandatory=$true,
            HelpMessage = 'Specify the full path of the Excel-file'
        )]
        [ValidateNotNullOrEmpty()]
        [string]$FilePath,
        [Parameter(
            Mandatory = $false,
            HelpMessage = 'Specify parameter for SPECIFIC sheet. If not specified, all spreadsheets will be imported'
        )]
        [string]$SheetName,
        [Parameter(
            Mandatory = $true,
            HelpMessage = 'Specify the CSV outfile path'
        )]
        [string]$OutFilePath,
        [Parameter(
            Mandatory = $true,
            HelpMessage = 'Specify the name of the CSV file'
        )]
        $OutFileName
    )
    if ('' -eq $FilePath) {
        throw "Please specify a valid file path"
        Start-Sleep -Seconds 2
        Exit
    }
    if ((Test-Path -Path $FilePath) -ne $true) {
        throw "Path not existing, please try again"
        Start-Sleep -Seconds 2
        Exit
    }
    if ((Test-Path $OutFilePath) -ne $true) {
        try {
            New-Item -Path $OutFilePath -ItemType Directory -Name ($OutFilePath -replace "^(.*[\\\/])") -ErrorAction Stop
        }
        catch {
            Write-Output "Unable to create filepath - Exiting script"
            Start-Sleep -Seconds 2
            Break
        }
    }
    ## Variables
    $FilePath = Resolve-Path $FilePath
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Workbook = $excel.Workbooks.Open($FilePath)
    $Object = @()
    ## If SheetName is not specified
    if ($SheetName -ne $true) {
        foreach ($worksheet in $workbook.sheets) {
            $SheetName = $worksheet.Name
            $columns = $worksheet.UsedRange.Columns.Count
            $rows = $worksheet.UsedRange.Rows.Count
            $row = 2
            $Sheet = 1
            Write-Output "Is on sheet $sheet named $SheetName"
            if (1 -lt $rows) {
                2..$rows | ForEach-Object{
                    $row = $_
                    $thisRow = [pscustomobject]@{}
                    1..$columns | ForEach-Object{
                        $thisRow | Add-Member -MemberType NoteProperty -Name $worksheet.Cells.Item.Invoke(1,$_).Value2 -Value $worksheet.Cells.Item.Invoke($row,$_).Value2
                    }
                    $object += $thisRow
                    $percents = [math]::round((($row/$rows) * 100), 0)
                    Write-Progress -Activity:"Importing from Excel file $FilePath on sheet $SheetName" -Status:"Imported $row of total $rows ($percents%)" -PercentComplete:$percents
                }
                $Sheet++
            }
            else {
                Write-Output "$($worksheet.name) has $rows rows - Skipping sheet"
            }
        }
    }
    ## If you only want one Sheet - Specify the exact name
    if ($SheetName) {
        $Worksheet = $Workbook.Sheets| Where {$_.Name -eq $SheetName}
        if ($Worksheet -eq $null) {
            Write-Output "Did not find any sheet named $SheetName - Exiting script"
            Start-Sleep -Seconds 3
            Break
        }
        $SheetName = $worksheet.Name
        $columns = $worksheet.UsedRange.Columns.Count
        $rows = $worksheet.UsedRange.Rows.Count
        $row = 2
        $Sheet = 1
        if (1 -lt $rows) {
            2..$rows | ForEach-Object{
                $row = $_
                $thisRow = [pscustomobject]@{}
                1..$columns | ForEach-Object{
                    $thisRow | Add-Member -MemberType NoteProperty -Name $worksheet.Cells.Item.Invoke(1,$_).Value2 -Value $worksheet.Cells.Item.Invoke($row,$_).Value2
                }
                $object += $thisRow
                $percents = [math]::round((($row/$rows) * 100), 0)
                Write-Progress -Activity:"Importing from Excel file $($FilePath.Path) on sheet $SheetName" -Status:"Imported $row of total $rows ($percents%)" -PercentComplete:$percents
            }
            $Sheet++
        }
        else {
            Write-Output "$($worksheet.name) has $rows rows - Skipping sheet"
        }
    }
    $Object | Export-Csv -Path $OutFilePath\$($OutFileName).csv  -Delimiter ";" -NoTypeInformation -Encoding UTF8 -Force
}

After running the script, it should look something like this:

I have tried to make this work in Azure, but without any luck. Most likely because this script uses the ComObject Excel application that comes with the Microsoft Office package required on your workstation or server. If anyone know a way to make this function viable in Azure, feel free to contact me.
If you found this script helpful, have any thoughts, or just want to discuss this, feel free to email me at johan.nilsson@xenit.se , or leave a comment below.

Tags : #Excel, #Files, #Office, #Sheets, #XLSX, CSV