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.

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!

About the Author

Adam Bertram is a 20-year veteran of IT. He's an automation engineer, blogger, consultant, freelance writer, Pluralsight course author and content marketing advisor to multiple technology companies. Adam also founded the popular TechSnips e-learning platform. He mainly focuses on DevOps, system management and automation technologies, as well as various cloud platforms mostly in the Microsoft space. He is a Microsoft Cloud and Datacenter Management MVP who absorbs knowledge from the IT field and explains it in an easy-to-understand fashion. Catch up on Adam's articles at adamtheautomator.com, connect on LinkedIn or follow him on Twitter at @adbertram or the TechSnips Twitter account @techsnips_io.


comments powered by Disqus
Most   Popular

Upcoming Training Events

0 AM
Live! 360 Orlando
November 17-22, 2024
TechMentor @ Microsoft HQ
August 11-15, 2025