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