PowerShell: Merge CSV Files Into One Workbook-Ready File

Quick Answer: Pipe every file through Import-Csv and send the combined objects to a single Export-Csv call: Get-ChildItem *.csv | ForEach-Object { Import-Csv $_.FullName } | Export-Csv merged.csv -NoTypeInformation. Because PowerShell reads each header row as property names, the output gets exactly one header. Add a calculated property to record which file each row came from, then open merged.csv in Excel.

PowerShell can merge any number of CSV files into one file that Excel opens as a workbook, using only two built-in cmdlets. Import-Csv turns each row into an object whose property names come from the header, and Export-Csv writes those objects back out with a single header line. The work is in the details: mismatched column names, delimiters, encodings and knowing which file a row came from.

What does PowerShell actually produce here?

Be clear about the output before you start. The built-in cmdlets write CSV text, not an .xlsx file. When people search for a way to merge CSV files "into one workbook" with PowerShell, the practical result is one merged CSV that Excel opens as a one-sheet workbook, which you can then save as .xlsx from Excel's Save As dialog. If you want each source file on its own tab instead of stacked rows, that is a different job; see combining CSV files into separate Excel tabs.

The approach below works the same on Windows PowerShell 5.1 and PowerShell 7 on Windows, macOS or Linux, with one difference covered in the type-information note.

The basic merge, line by line

Put the files in their own folder (for example twelve monthly exports from your bank) and run this from that folder's parent:

Get-ChildItem -Path .\exports -Filter *.csv |
    ForEach-Object { Import-Csv -Path $_.FullName } |
    Export-Csv -Path .\merged.csv -NoTypeInformation

What each stage does:

1. Get-ChildItem lists the .csv files. Filtering by extension keeps stray .xlsx or .txt files out.
2. Import-Csv reads one file at a time. Microsoft's reference explains that the column header row determines the property names, and that the first row is treated as the header unless you pass -Header. So header lines never arrive as data.
3. Export-Csv receives all the row objects as one stream and writes a header once, followed by the values.

About -NoTypeInformation: in Windows PowerShell 5.1, Export-Csv writes a #TYPE line at the top of the file unless you include it. The Export-Csv documentation says this became the default behavior in PowerShell 6.0 and the switch is kept for backward compatibility, so it is harmless to leave in scripts that may run on either version.

One trap: write the merged file outside the input folder. If merged.csv sits next to the source files, the next run picks it up as an input and you double every row.

How do you add a column that shows the source file?

When twelve statements end up in one sheet, you will want to know which month a suspicious row came from. Add a calculated property with Select-Object while each file is being read:

Get-ChildItem -Path .\exports -Filter *.csv | ForEach-Object {
    $name = $_.Name
    Import-Csv -Path $_.FullName |
        Select-Object *, @{ Name = 'SourceFile'; Expression = { $name } }
} | Export-Csv -Path .\merged.csv -NoTypeInformation

Capturing $_.Name into $name first matters. Inside the inner pipeline, $_ refers to the current row, not the file, so referencing $_.Name there would return an empty value or a column called Name from your data.

The SourceFile column lands as the last column. In Excel you can filter on it, or build a PivotTable with it as a row field to total each month.

Why do some columns disappear in the merged file?

This is the failure people hit most often, and the Microsoft reference explains it directly: when you send several objects to Export-Csv, it organizes the file around the properties of the first object. If later objects lack one of those properties, the value is left empty; if later objects have extra properties, those values are not written at all.

In practice that happens when a bank changes its export layout partway through the year, or when you mix a checking export with a credit card export. Typical differences:

The robust fix is to state the columns you want, in the order you want, and map each variant onto them:

Import-Csv -Path $_.FullName | Select-Object `
    @{ Name = 'Date';        Expression = { $_.Date ?? $_.'Posting Date' } },
    @{ Name = 'Description'; Expression = { $_.Description } },
    @{ Name = 'Amount';      Expression = { $_.Amount ?? $_.'Transaction Amount' } }

The ?? operator needs PowerShell 7. On 5.1, use an if expression inside the script block instead.

If you build the output with repeated Export-Csv -Append calls rather than one pipeline, the cmdlet refuses to append an object whose properties do not match the existing columns. Adding -Force makes it continue, but the documentation notes that mismatched properties are then discarded, which is the same silent data loss in a different form.

Delimiters, encodings and regional settings

Three switches cover most cross-country problems:

• -Delimiter sets the separator. Semicolon-separated exports are common in Europe; quote the character as ';'.
• -UseCulture uses the list separator of the current Windows region. It is convenient on a machine set up for that region, and surprising on a colleague's machine that is not.
• -Encoding controls character encoding. Both cmdlets default to utf8NoBOM in PowerShell 7. If merchant names with accents look garbled when Excel opens the file by double-click, export again with -Encoding utf8BOM, which gives Excel a marker to detect UTF-8.

Microsoft's guide to importing text and CSV files into Excel also notes a ceiling of 1,048,576 rows per sheet, which a few years of business card exports can approach.

Sorting the merged rows by date

Import-Csv returns every value as a string, so sorting the Date column directly sorts 10/01 before 2/01. Convert first:

... | Sort-Object { [datetime]::ParseExact($_.Date, 'MM/dd/yyyy', $null) } |
    Export-Csv -Path .\merged.csv -NoTypeInformation

Change the format string to match your bank, for example 'dd/MM/yyyy' for most UK exports. If the parse throws, at least one row has a different format, which is worth knowing before the data reaches your books.

Opening the result in Excel

Invoke-Item .\merged.csv opens the file with whatever program owns .csv on that machine, usually Excel. Double-clicking lets Excel guess the column types, which can turn long reference numbers into scientific notation. For more control, open a blank workbook, go to the Data tab and pick From Text/CSV in the Get & Transform Data group, choose the delimiter in the preview, and select Transform Data if a column needs a different type before loading. Once it looks right, save it as an .xlsx workbook.

From there the usual next step is analysis: a PivotTable by month or category, as in this guide to pivot tables for bank statements.

When the bank only gives you PDFs

The script assumes you already have CSV exports. Many banks only offer CSV for recent months and PDF statements for older periods, and some business accounts offer PDF only. In that case you need a conversion step before PowerShell can help. One option is QuickBankConvert's PDF to CSV conversion: you upload the statement file (no bank login), it reads the transactions on its servers and gives you rows of date, description, category and signed amount. A free account downloads CSV for 3 statements a month; heavier volumes and batch upload sit on the paid plans listed on the pricing page.

If your downstream tool wants structured data rather than a spreadsheet, the same rows can go out as JSON; see converting a bank statement to JSON.

A pre-flight checklist

• All input files in one folder, nothing else in it.
• Output path outside that folder.
• Headers inspected with Get-Content -TotalCount 1 on each file.
• Explicit column list if any header differs.
• Delimiter and encoding set for the source.
• Row count of the output equals the sum of the inputs: (Import-Csv merged.csv).Count.

That last check is the cheapest way to catch a skipped file or a doubled one.

Related guides

• Merge multiple CSV bank statements without code
• Combine CSV files into separate Excel tabs
• Bank statement CSV format explained