Powershell – Combine CSV files with different headers

This script takes a folder of .csv files, combines all the headers, then combines the data from the .csv files into one file.  For any header that did not exist in a particular .csv file originally, there will now be a null value in that column.  In essence this combines both the data and headers, keeping everything lined up correctly and not causing formatting issues.  

				
					# Set the directory where the CSV files are located
$csvDirectory = "C:\Files\Consolidate-CSV"

# Get all CSV files in the directory
$csvFiles = Get-ChildItem $csvDirectory -Filter *.csv

# Initialize an array to hold all headers
$headers = @()

# Loop through all CSV files and collect headers
Write-Host "Collecting headers from CSV files..."
foreach ($csvFile in $csvFiles) {
    Write-Host "Processing file $($csvFile.FullName)"
    # Read in the CSV file and get headers
    $csvContent = Import-Csv $csvFile.FullName
    $csvHeaders = $csvContent | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name

    # Add any new headers to the $headers array
    foreach ($csvHeader in $csvHeaders) {
        if ($headers -notcontains $csvHeader) {
            $headers += $csvHeader
        }
    }
}
Write-Host "Headers collected."

# Create an empty array to hold all data
$data = @()

# Loop through all CSV files again and add data to the $data array
Write-Host "Collecting data from CSV files..."
foreach ($csvFile in $csvFiles) {
    Write-Host "Processing file $($csvFile.FullName)"
    # Read in the CSV file and add any missing headers with blank values
    $csvContent = Import-Csv $csvFile.FullName | Select-Object $headers | ForEach-Object {
        foreach ($header in $headers) {
            if (-not $_.PSObject.Properties.Match($header)) {
                $_ | Add-Member -MemberType NoteProperty -Name $header -Value ""
            }
        }

        $_
    }

    # Add the CSV data to the $data array
    $data += $csvContent
}
Write-Host "Data collected."

# Output the combined data with all headers
Write-Host "Exporting combined CSV file..."
$data | Export-Csv -Path "C:\Files\Consolidate-CSV\output\combined.csv" -NoTypeInformation
Write-Host "Export complete."
Tags