Combine multiple CSVs into one XLSX via PowerShell
A convenient PowerShell script that will detect all of the CSV files in a directory and merge them into a single XLSX file. Each CSV becomes a worksheet in the XLSX.
I recently needed to combined a large number of comma-separated values (CSV) files into a single XLSX file as part of a data analysis pipeline. To achieve this I employed PowerShell to combine the CSVs into separate worksheets within the one Microsoft Excel Workbook, which is saved as an XLSX file.
This script is based on the work of realgsuseethernet, but with some key differences.
With this version, the target folder is pre-determined (via $path
at the top of the script) and the output file is named “date_username_combined-data.xlsx” where date = yyyyMMdd and username is the name of the account accessing Windows. (These parameters can be changed if so desired). The first change obviates any requirement to cd
to the target folder in PowerShell (a task that I find annoying) and pre-setting the filename removes the need for a prompt.
Simply copy the code below, change the $path
in line 1 and execute it in Windows PowerShell.
$path="c:\path\to\folder" #target folder
cd $path;
$csvs = Get-ChildItem .\* -Include *.csv
$y=$csvs.Count
Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs)
{
Write-Host " "$csv.Name
}
$outputfilename = $(get-date -f yyyyMMdd) + "_" + $env:USERNAME + "_combined-data.xlsx" #creates file name with date/username
Write-Host Creating: $outputfilename
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet=1
foreach ($csv in $csvs)
{
$row=1
$column=1
$worksheet = $xlsx.Worksheets.Item($sheet)
$worksheet.Name = $csv.Name
$file = (Get-Content $csv)
foreach($line in $file)
{
$linecontents=$line -split ',(?!\s*\w+")'
foreach($cell in $linecontents)
{
$worksheet.Cells.Item($row,$column) = $cell
$column++
}
$column=1
$row++
}
$sheet++
}
$output = $path + "\" + $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()
cd \ #returns to drive root
A text to columns operation will still need to be run on each worksheet within the new CSV.
Comments
19 responses to “Combine multiple CSVs into one XLSX via PowerShell”
I am using something very similar to this which works great with small csv’s but when attempting to merge a single csv ~160MB it appears to hang indefinitely. Is there a way to optimise this for large files or would a different solution be preferred?
hello,
thanks for this great script. that saved a lot of time. appreciate this help.
I was trying to get the data of 2 csv file with same format in a single sheet (not different tab)..
example–1 csv contain data i below format.
CI name space in MB Space in GB
a 8 4
2nd csv also having the same format.
I need help in append them in 1 tab of excel or csv with below format
CI name space in MB Space in GB space in MB Space in GB
a 8 4 3 1
Hi all,
I’m currently using a variation of your script to combine multiple csv files into an xlsx. The only part I seem to not-get is to have the columns autosize themselves. Reason: we filter through about 500 servers and while tabbing is easy on the bottom of the workbook, it’s having to open the contents of each row in order to see the value is whats taking extra time. I’ve considered adding a function to extract only the “needed” data, but it removes the possibility getting other pertinent info. Thanks in advance.
-Ed
This does create the correct number of tabs, but each tab is called Sheet 1, Sheet 2, etc. it is not calling the Sheet the name of the CSV file. What am I missing?
The script is fantastic but every value in my Excel file has ” before and after.
Any way to get rid of it?
After script is run, open file in Excel and run a “Find and Replace”.
Find what: ” and Replace with: (leave blank)
Select Options and change to search Within: Workbook
Select Replace All
All Quotes around characters will then be removed
if you add the two lines seen below it will remove the “” from the beginning and end for you.
foreach($cell in $linecontents)
{
$cell = $cell.TrimStart(‘”‘)
$cell = $cell.TrimEnd(‘”‘)
$worksheet.Cells.Item($row,$column) = $cell
$column++
}
Question – I was trying to run this on three different excel files that are between 3 – 5kb each. This script completes successfully, but it takes like 5 minutes to complete. Any idea why it takes so long to complete when it is only process 3 different small .CSV files?
Script works like a dream. the only issue is that if i have a number such as 2,736.433
it seperates the numbers into seperate rows.
Example from the above number is the the 2 will be in row C and the 736.433 ends up in row D
Also is there a way to prevent the “” from ending up in the output as well once the import of all th csv is done
I need some help
there is an issue with column formatting.
When a number such as 7,456.233 is in a csv file and it gets imported to the excel worksheet
the 7 ends up in say column C and the rest ends up in Column D
how do i get around this issue ?
I have attached this script at end of my report to merge my report in a single excel sheet. Script is working fine for me if Excel is installed. But in some servers excel is not installed. So some errors are coming like below, while running report query. Please help friends.
“You cannot call a method on a null-valued expression.
At line:31 char:1
+ $worksheet.Cells.Item($row,$column) = $cell”
Great Script , but getting one issue during merging of CSV’s
If any cell contains New Line character . As per this script its coming into next row of spreadsheet.
Can you please help to resolve this
Great Script , but getting one issue during merging of CSV’s
If any cell contains New Line character . As per this script its coming into next row of spreadsheet.
Can you please help to resolve this
How can I overwrite the file to the exiting file automatically?
Hi the script works, but how do i tweak it – such that, all .csv files will be in 1 sheet only? which part of the loop should i change so i wont create a new sheet?
Script is working But If any one of the cell has big values(more than one line) then it is adding to separate line rather than single line
It took me quite a while to find a script that would combine several CSV files into one Excel file, with each CSV having its own tab. This worked perfectly, I only had to change the path. Thanks so much!
Hello,
The script works fine, i only have a problem that I am using this script to add 97 call queues from Teams into 1 file, the problem is that some of the names of the csv files i have are longer than 31 characters, which stops adding those names in and errors out for this name. Is there a way to truncate the name at 25 or 30 characters.
greetz,
Louis Laan
Perfectly worked