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:
File2.CSV
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.
Happy learning…
Comments on this entry are closed.
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!
Mary, please post the command you are using. I will be able to help you validate that.
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.
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
Great script it save me a lot time as I had to merge several cvs files.
Thank you
Glad to know that it helped!!. Thanks Rick.
i get the error -Ther term merge-csv is not regonized as the as the name of the cmdlet
Hi,
Did you copy paste the code into your powershell window?
Great script, helped a lot , thank you very much
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.”
Hi Ryan,
This functionality is not available in current version of the script. Needs some modifications.
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.
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
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
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
Hi, Please share what you have tried. We can try helping based on that.