Powershell CSV file to Excel into multiple worksheets-CodePudding

I have a CSV that has 3 pieces of information (Name, Metric, Metric Data) for each server

Server1,Disk/,Disk Metrics
Server1,Disk/logs,Disk Logs Metrics
Server1,MEM/,CPU Metrics
Server2,Disk/,Disk Metrics
Server2,Disk/logs,Disk Logs Metrics
Server2,MEM/,CPU Metrics

I want to use powershell to take that CSV file and create a an excel spread sheet where each metric, ServerName, Metric, and Metric Data is written to its own worksheet.

I have added Install-Module -Name ImportExcel

CodePudding user response：

Exporting to multiple WorkSheets using the ImportExcel Module could be a bit hard to do if it's your first time using it, normally we would ask for, at least, and attempt at solving this problem but since I already have the code at my disposal I will share a minimal example and leave it to you to do further research on this awesome module.

$csv = Import-Csv 'path\to\file.csv'
$path = '\path\to\file.xlsx'

$params = @{
    AutoSize   = $true
    TableStyle = 'Medium11'
    PassThru   = $true
    AutoFilter = $true
}

Remove-Variable xlsx -ErrorAction SilentlyContinue

$csv | Group-Object Name | ForEach-Object {
    $params.WorkSheetName = $_.Name
    $params.TableName = $_.Name
    if(-not $xlsx) {
        $params.InputObject = $_.Group
        $xlsx = Export-Excel @params -Path $path
        return
    }
    $params.InputObject = $_.Group
    $params.ExcelPackage = $xlsx
    $null = Export-Excel @params
}

Close-ExcelPackage $xlsx

Now, if we inspect the Xlsx:

PS /> Get-Item ./file.xlsx | Get-ExcelFileSummary | Format-Table

ExcelFile WorksheetName Rows Columns Address Path
--------- ------------- ---- ------- ------- ----
file.xlsx Server1          4       3 A1:C4   /home/user/Documents/test
file.xlsx Server2          4       3 A1:C4   /home/user/Documents/test

PS /> Import-Excel ./file.xlsx -WorksheetName Server2

Name    Metric    Metric Data
----    ------    -----------
Server2 Disk/     Disk Metrics
Server2 Disk/logs Disk Logs Metrics
Server2 MEM/      CPU Metrics