Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

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

Use this script to combine multiple CSVs into one 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.

17 responses to “Combine multiple CSVs into one XLSX via PowerShell”

On 8 August 2016, oCyrus wrote:

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?

On 27 December 2017, Raj wrote:

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

On 23 May 2018, Ed wrote:

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

On 8 June 2018, Scott wrote:

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?

On 29 June 2018, Francesco wrote:

The script is fantastic but every value in my Excel file has ” before and after.
Any way to get rid of it?

On 21 August 2018, Massimo wrote:

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

On 25 July 2019, pwhitake wrote:

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++ } Reply On 27 July 2018, TestGuy wrote: 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? Reply On 3 September 2018, Bob wrote: 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 Reply On 3 September 2018, Bob wrote: 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 ? Reply On 28 December 2018, Ratheesh wrote: 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”

On 1 May 2019, Anshuman Bajaj wrote:

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.

On 1 May 2019, Anshuman wrote:

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.

On 30 July 2019, Ambi wrote:

How can I overwrite the file to the exiting file automatically?

On 31 July 2019, zmui wrote:

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?

On 5 November 2019, ramesu wrote:

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

On 18 April 2020, DudeManDeuce wrote:

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!

<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>