Click here to Skip to main content
15,123,851 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:

PowerShell
#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.
Posted
Updated 30-Jul-21 3:01am
Comments
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

   
Comments
Maciej Los 30-Jul-21 8:19am
   
5ed!
Richard MacCutchan 30-Jul-21 8:36am
   
Thanks Maciej. More points for being able spell 'Google'. :)
Maciej Los 31-Jul-21 2:32am
   
:D

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