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.
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.
Subscribe
