Using PowerShell Filter functions to filter out header rows during CSV file merges

by Klaus Graefensteiner 27. June 2009 09:23

The Problem

I needed to merge 365 CSV files that represent daily weather data sets into one CSV file that contains all the data accumulated during one year. Each of the daily CSV files had a header row. The yearly file should only have one. Filtering out rows is a perfect application of the PowerShell filter functions.

Inside a Filter

Figure 1: Inside a Filter

The Solution

This is where PowerShell’s pipeline programming is shining. It only takes a Filter function definition, inserting it into an pre-existing pipeline and you are done.

Here is the sample script:

   1: #Filter Function filters out records that start with "Time"
   2: Filter Filter-Header
   3: {
   4:     if($_ -match "^Time")
   5:     {
   6:         $_ | out-null
   7:     }
   8:     else
   9:     {
  10:         $_
  11:     }
  12: }
  13:  
  14: cd "C:\Users\Klaus\Desktop\TO PLANET\WeatherStation\FilterTest"
  15:   
  16: #Initializing yearly file
  17: $YearlyFileName = "Weather2009.csv"
  18: #Set the header only once in the yearly file
  19: Set-Content -Path $YearlyFileName -value "Time,TemperatureF,DewpointF,PressureIn,WindDirectionDegrees,WindSpeedMPH,WindSpeedGustMPH,Humidity,HourlyPrecipIn" -force -encoding "UTF8"
  20:         
  21: #Open all csv files get-content and add it to the yearly file       
  22: $Files = dir -Path "C:\Users\Klaus\Desktop\TO PLANET\WeatherStation\FilterTest" -Filter "*.csv"
  23:  
  24: #Filter in Action
  25: $Files | ForEach-Object `
  26: {
  27:     Get-Content -Path $_.Name -Encoding "UTF8" | Filter-Header | Add-Content -path $YearlyFileName -Encoding "UTF8"
  28: }

Download

The script file and some sample csv files can be downloaded here: FilterCSVHeaders.zip

Ausblick

Filter are your friends.

Tags: ,

Tips & Tricks | PowerShell

About Klaus Graefensteiner

I like the programming of machines.

Add to Google Reader or Homepage

LinkedIn FacebookTwitter View Klaus Graefensteiner's profile on Technorati
Klaus Graefensteiner

Klaus Graefensteiner
works as Developer In Test and is founder of the PowerShell Unit Testing Framework PSUnit. More...

Open Source Projects

PSUnit is a Unit Testing framwork for PowerShell. It is designed for simplicity and hosted by Codeplex.
BlogShell is The tool for lazy developers who like to automate the composition of blog content during the writing of a blog post. It is hosted by CodePlex.

Administration

About

Powered by:
BlogEngine.Net
Version: 1.6.1.0

License:
Creative Commons License

Copyright:
© Copyright 2014, Klaus Graefensteiner.

Disclaimer:
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Theme design:
This blog theme was designed and is copyrighted 2014 by Klaus Graefensteiner

Rendertime:
Page rendered at 10/23/2014 3:58:56 AM (PST Pacific Standard Time UTC DST -7)