≡ Menu

Merging multiple CSV files into one using PowerShell

I came across a question in Technet Forums about how to merge multiple CSV files into one single CSV with additional column added to the output file that indicates the file name from where it imported. You can read full question at http://social.technet.microsoft.com/Forums/en-US/c9470c3e-37a2-48e0-8170-a235fbee2d2e/merging-several-csv-files-in-one-csv-file?forum=winserverpowershell

To address the need, I prepared a quick PS function. Posting it here for my blog readers reference.

CODE:

function Merge-CSVFiles {            
[cmdletbinding()]            
param(            
    [string[]]$CSVFiles,            
    [string]$OutputFile = "c:\merged.csv"            
)            
$Output = @();            
foreach($CSV in $CSVFiles) {            
    if(Test-Path $CSV) {            
                    
        $FileName = [System.IO.Path]::GetFileName($CSV)            
        $temp = Import-CSV -Path $CSV | select *, @{Expression={$FileName};Label="FileName"}            
        $Output += $temp            
            
    } else {            
        Write-Warning "$CSV : No such file found"            
    }            
            
}            
$Output | Export-Csv -Path $OutputFile -NoTypeInformation            
Write-Output "$OutputFile successfully created"            
            
}            
            

INPUT FILES AND OUTPUT:

File1.CSV:

csvfile1

File2.CSV

csvfile2

Run the below command to merge above two CSVs.

Merge-CSVFiles -CSVFiles C:\temp\file1.csv,C:\temp\file2.csv -OutputFile c:\temp\output.csv

Output CSV will be like this.

outputcsv

Happy learning…

Comments on this entry are closed.

  • Mary October 17, 2015, 12:34 am

    The csv merge works to a point, as the new output file is created. However, the data is not populated in the new csv file, nor is the additional column with the file name from where it was imported. Any thoughts on what I’m doing wrong? Thanks!

    • TechiBee October 17, 2015, 7:41 am

      Mary, please post the command you are using. I will be able to help you validate that.

  • Brandon November 11, 2015, 8:29 pm

    I don’t understand the purpose of the entire code above when the code below the two examples works. I’m VERY new to PowerShell and I’ve tried to no aval to get the larger code to work. I’m not sure what I’m missing. The second code worked like a charm.

  • Scott T March 21, 2016, 6:49 pm

    Nice script – thank you!! For my purposes, I did not need the file name to appear in my final results and changed this line from:
    $temp = Import-CSV -Path $CSV | select *, @{Expression={$FileName};Label=”FileName”}

    to:
    $temp = Import-CSV -Path $CSV

  • Rick August 25, 2016, 10:29 pm

    Great script it save me a lot time as I had to merge several cvs files.

    Thank you

    • Wintel Rocks September 24, 2016, 10:16 am

      Glad to know that it helped!!. Thanks Rick.

  • ankit September 7, 2016, 12:29 pm

    i get the error -Ther term merge-csv is not regonized as the as the name of the cmdlet

    • Wintel Rocks September 24, 2016, 10:10 am

      Hi,

      Did you copy paste the code into your powershell window?

  • Srikanth Baskaran December 17, 2016, 3:27 am

    Great script, helped a lot , thank you very much

  • Ryan March 15, 2017, 8:43 pm

    Is it possible to import an entire directory into this function?

    I attempted the following, but received an error

    Merge-CSVFiles -CSVFiles “C:\temp\*.csv” -OutputFile “C:\temp\MERGED.csv”

    “Cannot perform operation because the path resolved to more than one file. This command cannot operate on multiple files.”

    • Wintel Rocks April 9, 2017, 7:27 pm

      Hi Ryan,

      This functionality is not available in current version of the script. Needs some modifications.

      • Andra April 3, 2019, 11:48 pm

        Hi Wintel,

        This, what Ryan asked i exactly what I need also. Could you please tell us which modifications needs to be implemented?
        Thanks upfront.

        • Wintel Rocks April 6, 2019, 2:49 pm

          You can try this assuming all your csv files are in c:\files filder.

          Merge-CSVFiles -CSVFiles (Get-ChildItem -Path c:\files -Filter *.csv).FullName -OutputFile c:\files\merged.csv

  • Djamel March 6, 2018, 3:25 am

    Hello
    thank’s it help me in my work it’s exactelly what I want ,i remove the filename colomne ,thanks Scott T.
    Thank you very much

  • Srikanth Baskaran June 5, 2018, 2:37 am

    Hi

    Could you please help me figure out adding a piece of code to remove / avoid duplication of same data. If any of the files have the same data then do not duplicate during the merge.

    I have tried Sort, Unique but did not work.

    Thanks

    • Wintel Rocks June 10, 2018, 8:33 pm

      Hi, Please share what you have tried. We can try helping based on that.