How to merge two or multiple csv files into one consolidated file using powershell (Automation)

1. Below Powershell script can be used to merge the multiple files into one consolidated file.

# Define file paths

$file1 = "C:\Users\admin\Desktop\automation\file1.csv"

$file2 = "C:\Users\admin\Desktop\automation\file2.csv"

$outputFile = "C:\Users\admin\Desktop\automation\consolidated.csv"

# Get header from file1 and write to output file

Get-Content $file1 | Select-Object -First 1 | Out-File -FilePath $outputFile -Encoding utf8

# Append data (excluding header) from file1

Get-Content $file1 | Select-Object -Skip 1 | Out-File -FilePath $outputFile -Append -Encoding utf8

# Append data (excluding header) from file2

Get-Content $file2 | Select-Object -Skip 1 | Out-File -FilePath $outputFile -Append -Encoding utf8

Write-Output "Merge completed. Output file: $outputFile"

 

This can be executed as below:

PS C:\Users\admin> C:\Users\admin\Desktop\automation\merge-script.ps1

C:\Users\admin\Desktop\automation\merge-script.ps1 : File C:\Users\admin\Desktop\automation\merge-script.ps1 cannot be

loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at

https:/go.microsoft.com/fwlink/?LinkID=135170.

At line:1 char:1

+ C:\Users\admin\Desktop\automation\merge-script.ps1

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : SecurityError: (:) [], PSSecurityException

    + FullyQualifiedErrorId : UnauthorizedAccess

Note: If you see above error use below command to fix the same.

PS C:\Users\admin> Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned

Execution Policy Change

The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose

you to the security risks described in the about_Execution_Policies help topic at

https:/go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy?

[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): y

Then try again it will workfine.

PS C:\Users\admin> C:\Users\admin\Desktop\automation\merge-script.ps1

Merge completed. Output file: C:\Data\consolidated.csv

2. Below script can be used if you have multiple files:

# Define source folder and output file

$sourceFolder = "C:\Users\admin\Desktop\automation"

$outputFile = "C:\Users\admin\Desktop\automation\consolidated_rpt.csv"

# Get list of CSV files in the folder

$csvFiles = Get-ChildItem -Path $sourceFolder -Filter "*.csv"

# Check if there are any files to merge

if ($csvFiles.Count -eq 0) {

    Write-Output "No CSV files found in $sourceFolder"

    exit

}

# Get header from the first file and write to output file

Get-Content $csvFiles[0].FullName | Select-Object -First 1 | Out-File -FilePath $outputFile -Encoding utf8

# Loop through each CSV file

foreach ($file in $csvFiles) {

    # Skip header and append data to output file

    Get-Content $file.FullName | Select-Object -Skip 1 | Out-File -FilePath $outputFile -Append -Encoding utf8

}

Write-Output "Merge completed. Output file: $outputFile"