Click here to Skip to main content
15,613,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am working on a script where I am trying to merge multiple csv into single excel file with csv filename as different sheet.

All the csv's are having same number of columns and name.

I don't have excel installed in my server so written below code using ImportExcel

What I have tried:

#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 generating.

Please can you help me with the issue in the script.
Updated 30-Jul-21 2:01am
Dave Kreskowiak 30-Jul-21 9:21am    
Generating a CSV file has nothing to do with Excel. It's not an "Excel file". Excel just knows how to read and interpret CSV files.

CSV is just a text file, with a few rules to represent data. CSV just means "Comma Separated Values". You don't need Excel at all to write to a text file.

1 solution

Share this answer
Maciej Los 30-Jul-21 8:19am    
Richard MacCutchan 30-Jul-21 8:36am    
Thanks Maciej. More points for being able spell 'Google'. :)
Maciej Los 31-Jul-21 2:32am    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900