PowerShell How-To

How To Process Multiple CSV Files at Once in PowerShell

Since CSVs are so easy to work with, it doesn't take long to organize and process large sets of data.

• By Adam Bertram
• 11/16/2017

CSVs are the catchall of data. Since CSVs are just text files in a specific structure, they're used in numerous contexts to store data. Due to their ease of use though, there are times when you may find yourself wrangling dozens or hundreds of these CSV fils at once! The most common situation is when you're in charge of managing an application that exports the same CSV file schema every night but with different data.

For example, an HR application does a nightly export of all new employees to the company to a CSV file that contains employee data like first name, last name, title and so on. Each CSV file is appended with a date. An export folder may look something like this:

PS> ls C:\Exports\

  

  

  Directory:  C:\Exports

  

  

  Mode                 LastWriteTime         Length Name

  ----                 -------------         ------ ----

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-1-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-10-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-11-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-12-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-13-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-14-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-15-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-16-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-17-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-18-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-19-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-2-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-20-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-21-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-22-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-23-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-24-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-25-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-26-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-27-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-28-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-29-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-3-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-30-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-31-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-4-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-5-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-6-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-7-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-8-17.csv

  -a----         11/3/2017   9:42 AM              2 EmployeeData10-9-17.csv

Each of these CSV files has four columns: FirstName,LastName,Title,HireDate. At the end of each month, management wants a report of who was hired that month. To find this information, you must read each CSV file and build a small report for them. First, you'll need to read all of the CSV files. We can do that with Import-Csv.

Get-ChildItem -Path C:\Exports\  -Filter '*.csv'

Next, we'll need to run Import-Csv on each of these files to return the data inside. Using the Import-Csv command's Path parameter which accepts a collection of paths, we can just pass all of the CSV to Import-Csv in one shot.

PS> Import-Csv -Path  (Get-ChildItem -Path C:\Exports\ -Filter '*.csv').FullName

FirstName LastName Title                HireDate

  --------- -------- -----                --------

  Adam       Bertram  System Administrator  9/23/17

  Joe        Jones    Developer            10/01/17

  Nancy      Smith    Accountant           10/03/17

Now we've got all employees in all of the CSV files but notice we've got one that's showing up that was hired last month. Management only wants those hired this month, so we'll need to filter that one out. To make the script dynamic which can be run at any time and will always have the last month, we'll get the current month with the Get-Date command and subtract one. Once we've got the target month, we'll then create a Where-Object filter and cast the HireDate value to a datetime object so we can perform some date calculations on it.

PS> $lastMonth =  (Get-Date).Month - 1

  PS> Import-Csv -Path (Get-ChildItem -Path  C:\Exports\ -Filter '*.csv').FullName | Where-Object {  ([datetime]$_.HireDate).Month -eq $lastMonth }

  

  FirstName LastName Title      HireDate

  --------- -------- -----      --------

  Joe        Jones    Developer  10/01/17

  Nancy      Smith    Accountant 10/03/17

Now you can see that we've got only the employees we need! At this point, we can take it one step further and export it to HTML as well.

PS> Import-Csv -Path  (Get-ChildItem -Path C:\Exports\ -Filter '*.csv').FullName | Where-Object {  ([datetime]$_.HireDate).Month -eq $lastMonth } | ConvertTo-Html | Set-Content  -Path 'C:\EmployeeReport.html'

Open up C:\EmployeeReport.html is a browser and you'll see an HTML representation of your report!