ITisOpen.net

Merging .csv files with Powershell

25 January 2012

I needed to merge multiple csv files containing exports from multiple VMware vCenter Servers environments so I decided to test Powershell to do this. I quickly found a solutions on the web but none of them worked exactly as I expected.

  1. Powershell csv file merging
  2. Merging identical csv files
  3. Remove Unwanted Quotation Marks from CSV Files by Using PowerShell

The first link found explains how to merge file by joining lines using a matching join condition. This will be really usefull for some future work but I don't needed this matching capability. I only want to append the content of all files into a single file without duplicating the header line.

Here we go!

So I came up with this script.

# Author: Patrice LACHANCE

$workdir = ".";
$directory = "$workdir\*.*";

# Get the csv files
$csvFiles = Get-ChildItem -Path $directory -Filter *.csv;

# container for csv files contents
$content = @();

# Process each file
foreach($csv in $csvFiles) {
    $content += Import-Csv $csv;
}

# Write a datetime stamped csv file
$datetime = Get-Date -Format "yyyyMMdd-hhmmss";
$content | Export-Csv -NoTypeInformation -Path "$workdir\merged_$datetime.csv";

But the problem was that all the lines in the generated file were surrounded by quotes. So after reading 2 and 3, I came up with this script working perfectly!

# Author: Patrice LACHANCE
# Inspired by the following posts
#   - Merging identical csv files
#     http://www.youdidwhatwithtsql.com/merging-csv-files-with-powershell/330
#   - Remove Unwanted Quotation Marks from CSV Files by Using PowerShell
#     http://blogs.technet.com/b/heyscriptingguy/archive/2011/11/02/remove-unwanted-quotation-marks-from-csv-files-by-using-powershell.aspx

$workdir = ".";
$directory = "$workdir\*.*";

# Get the csv files
$csvFiles = Get-ChildItem -Path $directory -Filter *.csv;

# container for csv files contents
$content = @();

# Process each file
foreach($csv in $csvFiles) {
    $content += Import-Csv $csv;
}

# Write a datetime stamped csv file
$datetime = Get-Date -Format "yyyyMMdd-hhmmss";
$content | ConvertTo-Csv -NoTypeInformation | % { $_ -replace  '^"(.*)"$', '$1' } | out-file "$workdir\merged_$datetime.csv" ;
blog comments powered by Disqus