Skip to content

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.

Three CSV icons with arrows pointing to a single XLSX icon.

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.

   

Comments

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

On 8 August 2016, oCyrus wrote: Hyperlink chain icon

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?

Reply

On 27 December 2017, Raj wrote: Hyperlink chain icon

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

Reply

On 23 May 2018, Ed wrote: Hyperlink chain icon

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

Reply

On 8 June 2018, Scott wrote: Hyperlink chain icon

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?

Reply

On 29 June 2018, Francesco wrote: Hyperlink chain icon

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

Reply

    On 21 August 2018, Massimo wrote: Hyperlink chain icon

    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

    Reply

      On 25 July 2019, pwhitake wrote: Hyperlink chain icon

      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: Hyperlink chain icon

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: Hyperlink chain icon

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: Hyperlink chain icon

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: Hyperlink chain icon

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”

Reply

On 1 May 2019, Anshuman Bajaj wrote: Hyperlink chain icon

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

Reply

On 1 May 2019, Anshuman wrote: Hyperlink chain icon

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

Reply

On 30 July 2019, Ambi wrote: Hyperlink chain icon

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

Reply

On 31 July 2019, zmui wrote: Hyperlink chain icon

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?

Reply

On 5 November 2019, ramesu wrote: Hyperlink chain icon

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

Reply

On 18 April 2020, DudeManDeuce wrote: Hyperlink chain icon

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!

Reply

Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.