Merging multiple csv into one Excel file without having Excel installed

Question

I am working on a script where I am trying to merge multiple csv into a single Excel file with csv filename as different sheet.

All the csv files have the same number of columns and name.

I don't have Excel installed on my server, so I've written this code using ImportExcel:

#Install-Module ImportExcel -scope CurrentUser
$path="C:\Scripts" #target folder
cd $path;
$csvs = Get-ChildItem .\* -Include *.csv
$csvCount = $csvs.Count
Write-Host "Detected the following CSV files: ($csvCount)"
foreach ($csv in $csvs) {
    Write-Host " -"$csv.Name
}

$excelFileName = $path + "\" + $(get-date -f yyyyMMdd) + "_combined-data.xlsx"
Write-Host "Creating: $excelFileName"

foreach ($csv in $csvs) {
    $csvPath = $path + $csv.Name
    $worksheetName = $csv.Name.Replace(".csv","")
    Write-Host " - Adding $worksheetName to $excelFileName"
    Import-Csv -Path $csvPath | Export-Excel -Path $excelFileName -WorkSheetname $worksheetName

The script is taking time and executing without any issue but the Excel sheet is not being generated.

Please can you help me find and fix the issue in this script?

[1] $csvPath = $path + $csv.Name is missing the backslash. Use Join-Path instead. [2] $worksheetName = $csv.Name.Replace(".csv","") --> $worksheetName = $csv.BaseName, but remember that Microsoft Excel does not allow worksheet name longer than 31 characters. [3] Change $csvs = Get-ChildItem .\* -Include *.csv to $csvs = Get-ChildItem .\* -Filter '*.csv' -File — Theo
is the excel FILE not being created or is the file being created but the sheet you want is not there? — Lee_Dailey

Santiago Squarzon Santiago Squarzon · Accepted Answer · 2021-07-30T13:54:40

If you want to add a worksheet per CSV to your Excel file the code should look something like this:

$path = "D:\Testing"
$csvs = Get-ChildItem $path -Filter *.csv
$excelFileName = Join-Path $path -ChildPath "$(Get-Date -f yyyyMMdd)_combined-data.xlsx"

foreach ($csv in $csvs)
{
    $props = @{
        WorksheetName = $csv.BaseName
        Path = $excelFileName
    }

    Import-Csv -Path $csv |
    Export-Excel @props
}

If you want to do all in memory (more efficient than the example above), it's a bit more complicated. You need to use -PassThru.

Note, this works on ImportExcel 7.1.2, make sure you have the last up to date version.

$path = "D:\Testing"
$csvs = Get-ChildItem $path -Filter *.csv
$excelFileName = Join-Path $path -ChildPath "$(Get-Date -f yyyyMMdd)_combined-data.xlsx"

foreach ($csv in $csvs)
{
    $content = Import-Csv -Path $csv

    $props = @{
        WorksheetName = $csv.BaseName
        PassThru = $true
        InputObject = $content
    }

    if(-not $xlsx)
    {
        $props.Path = $excelFileName
        $xlsx = Export-Excel @props

        continue
    }

    $props.ExcelPackage = $xlsx
    Export-Excel @props > $null
}

Close-ExcelPackage $xlsx

If you want all CSV files on the same worksheet, assuming they all have the same headers:

$path = "D:\Testing"
$csvs = Get-ChildItem $path -Filter *.csv
$excelFileName = Join-Path $path -ChildPath "$(Get-Date -f yyyyMMdd)_combined-data.xlsx"

Import-Csv -Path $csvs |
Export-Excel -WorksheetName 'Merged' -Path $excelFileName

Merging multiple csv into one Excel file without having Excel installed

1 Answers