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 an independent consultant, technical writer, trainer and presenter. Adam specializes in consulting and evangelizing all things IT automation mainly focused around Windows PowerShell. Adam is a Microsoft Windows PowerShell MVP, 2015 powershell.org PowerShell hero and has numerous Microsoft IT pro certifications. He is a writer, trainer and presenter and authors IT pro course content for Pluralsight. He is also a regular contributor to numerous print and online publications and presents at various user groups and conferences. You can find Adam at adamtheautomator.com or on Twitter at @adbertram.

comments powered by Disqus

SharePoint Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.