Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 34 ยท Projects

Project: Bulk User Report from CSV

Build a PowerShell report from a CSV of users: import, validate rows, collect rejects, add calculated properties, then write JSON and HTML output.

Sooner or later someone hands you a spreadsheet and says "can you check this and send me a summary?" It might be new hires to onboard, customers to import or accounts to review. The file is never clean. There is a bad email on line three, a duplicate on line five and a salary column that says "abc". A good script does not stop at the first problem and it does not silently skip rows either. It separates the rows it trusts from the rows it rejects, says why, and produces a report you can hand to a human.

That is this project. You will read a CSV with Import-Csv, validate every row, keep two lists (good rows and rejected rows with reasons), add calculated properties such as years of service and a salary band, summarize by department, and write the results as CSV, JSON and HTML. It combines ideas from CSV, JSON and XML, Where-Object, Select-Object and Sort-Object, errors and formatting output.

PowerShell cannot run on this site, so the real script is read-only. Beside it you get Python stand-ins that run in your browser and do the same work step by step: the same rules, the same sample data, the same shape of results. The Python code is not PowerShell, but the thinking carries over one to one, and each stand-in says which PowerShell line it mirrors.

Five steps from file to report

Import Import-Csv gives one object per row.
Validate Check every field, record every problem.
Split Good rows in one list, rejects in another.
Enrich Calculated properties add the new columns.

Reports come last. The data work comes first.

1 / 2

The input file

Save the following as users.csv. It has nine rows, and five of them are broken on purpose.

TEXT
Name,Email,Department,Hired,Salary
Ana Torres,[email protected],Sales,2021-03-15,52000
Ben Okafor,ben.okafor@example,IT,2019-11-02,61000
Chen Wei,[email protected],IT,2022-07-01,abc
Dana Smith,[email protected],HR,2020-01-20,48000
Eli Cohen,[email protected],Sales,2023-02-30,50000
Fay Nguyen,[email protected],,2018-05-09,70000
Gus Lopez,[email protected],IT,2020-09-14,58000
Hana Kim,[email protected],HR,2021-08-23,47500
Ivan Petrov,[email protected],Sales,2017-04-03,66000

Look at the problems before reading on: a malformed email (no dot after the @), a salary that is not a number, an email that repeats an earlier one in different letter case, a date that does not exist (February 30) and an empty department.

Import-Csv turns each line into an object whose property names come from the header. Every value is a string, even 52000. That is the first trap of this project: if you sort or compare the Salary strings, "9000" sorts after "52000". You must convert to numbers yourself, and the validation step is the perfect place to do it.

Step 1: validate every row

The plan for one row is: start with an empty list of problems, run each check, add a short message for each failure, then decide at the end. Collecting every problem (instead of stopping at the first) tells the person fixing the file everything that is wrong with a row in one pass.

Real PowerShell for this step (read-only):

POWERSHELL
$asOf         = [datetime]'2025-01-01'   # fixed date: reports stay reproducible
$emailPattern = '^[^@\s]+@[^@\s]+\.[^@\s]+$'

$good = [System.Collections.Generic.List[object]]::new()
$bad  = [System.Collections.Generic.List[object]]::new()
$seen = [System.Collections.Generic.HashSet[string]]::new(
            [System.StringComparer]::OrdinalIgnoreCase)
$line = 1                                 # the header is line 1

foreach ($row in Import-Csv -Path .\users.csv) {
    $line++
    $problems = [System.Collections.Generic.List[string]]::new()
    $hired    = [datetime]::MinValue
    $salary   = 0.0

    if ($row.Email -notmatch $emailPattern) {
        $problems.Add('bad email')
    }
    elseif (-not $seen.Add($row.Email)) {
        $problems.Add('duplicate email')
    }

    if ([string]::IsNullOrWhiteSpace($row.Department)) {
        $problems.Add('missing department')
    }

    $ok = [datetime]::TryParseExact($row.Hired, 'yyyy-MM-dd',
            [cultureinfo]::InvariantCulture, 'None', [ref]$hired)
    if (-not $ok) { $problems.Add('bad hire date') }

    $ok = [double]::TryParse($row.Salary, 'Float',
            [cultureinfo]::InvariantCulture, [ref]$salary)
    if (-not $ok) { $problems.Add('bad salary') }

    if ($problems.Count -eq 0) {
        $good.Add([pscustomobject]@{
            Name       = $row.Name
            Email      = $row.Email
            Department = $row.Department
            Hired      = $hired
            Salary     = $salary
        })
    }
    else {
        $bad.Add([pscustomobject]@{
            Line     = $line
            Name     = $row.Name
            Problems = $problems -join '; '
        })
    }
}

"{0} valid, {1} rejected" -f $good.Count, $bad.Count

A few choices deserve a closer look.

  • foreach statement with a counter. You need the line number for the reject report, and foreach keeps it easy. See Conditionals and Loops.
  • TryParseExact and TryParse. They return $true or $false instead of throwing, so a bad value becomes a message and not an error. The [ref] argument is how a method hands back its second result; see Variables and Types. The invariant culture makes 1,5 or 01/02/2025 mean the same thing on every machine, which matters when the script runs on servers in different regions. February 30 fails because TryParseExact checks the calendar.
  • A case-insensitive HashSet. Add returns $false when the value is already there, so one call both tests and records. That is how [email protected] is caught as a duplicate of the first Ana.
  • Typed lists instead of +=. Appending to an array with += copies the whole array every time. For a few rows that is fine, for 100,000 it is slow. See Arrays and Hashtables.
  • [pscustomobject]. The good rows leave this loop as clean objects with real types ([datetime], [double]), not strings. Everything after this step can trust them.

The Python stand-in below follows the same recipe. csv.DictReader plays the role of Import-Csv, the problems list is the same, and seen is a set of lower-cased emails.

Python Stand-in: validate rows and collect rejects

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The run prints 4 valid, 5 rejected, one line per reject with its reason, and the four valid names. Try changing a row: fix Ben's email and watch him move into the valid list. Then give Dana a completely different address.

Do not try to write the perfect email regex. The pattern above only catches obvious mistakes (no @, no dot, spaces). The only real proof that an address works is sending a message to it. Validation here is a sanity check, not a guarantee.

Step 2: calculated properties and a summary

Now the good rows get new columns. In PowerShell the tool is a calculated property: a hashtable with a Name (or N) and an Expression (or E) script block, used inside Select-Object. Inside the block, $_ is the current object.

POWERSHELL
$report = $good |
    Sort-Object Department, @{ Expression = 'Salary'; Descending = $true } |
    Select-Object Name, Department,
        @{ Name = 'Years';  Expression = { [math]::Floor(($asOf - $_.Hired).TotalDays / 365.25) } },
        @{ Name = 'Band';   Expression = {
               if     ($_.Salary -ge 60000) { 'High' }
               elseif ($_.Salary -ge 50000) { 'Mid' }
               else                         { 'Entry' } } },
        @{ Name = 'Domain'; Expression = { $_.Email.Split('@')[1] } },
        Salary

$summary = $good |
    Group-Object Department |
    Sort-Object Name |
    ForEach-Object {
        [pscustomobject]@{
            Department = $_.Name
            Count      = $_.Count
            AvgSalary  = [math]::Round(($_.Group | Measure-Object Salary -Average).Average, 2)
        }
    }

Notes:

  • $asOf - $_.Hired subtracts two dates and gives a [timespan]. Its TotalDays property is a number you can divide. Using a fixed $asOf instead of Get-Date makes the output the same today and next year, which is what you want while testing. Switch to Get-Date when the script goes live.
  • Sorting by two keys uses a comma-separated list; the hashtable form @{ Expression = 'Salary'; Descending = $true } sets the direction per key. See Where-Object, Select-Object and Sort-Object.
  • Group-Object returns objects with Name, Count and Group (the original rows). Measure-Object -Average computes the mean. See Group-Object, Measure-Object and ForEach-Object.
  • Because Salary is a [double] (we converted it in step 1), sorting and averaging are numeric. With the raw CSV strings the sort order would be alphabetical.

Step 3: write the reports

POWERSHELL
$bad     | Export-Csv  -Path .\rejected.csv -NoTypeInformation -Encoding utf8
$summary | ConvertTo-Json -Depth 3 | Set-Content -Path .\summary.json -Encoding utf8

$style = '<style>table{border-collapse:collapse}td,th{border:1px solid #999;padding:4px 8px}</style>'
$report |
    ConvertTo-Html -Property Name, Department, Years, Band, Domain `
                   -Title 'User report' -Head $style `
                   -PreContent "<h1>User report as of $($asOf.ToString('yyyy-MM-dd'))</h1>" |
    Set-Content -Path .\report.html -Encoding utf8
  • -NoTypeInformation removes the #TYPE header line in Windows PowerShell 5.1. PowerShell 7 never writes it, and the switch is accepted for compatibility.
  • ConvertTo-Json -Depth matters when objects are nested: the default depth is 2, and deeper levels are flattened into text. This summary is flat, but make a habit of setting the depth. See CSV, JSON and XML.
  • ConvertTo-Html turns each object into a table row. The report holds names typed by humans, so check how your PowerShell version treats characters such as < and & before you publish a page to others, or encode them yourself with [System.Net.WebUtility]::HtmlEncode(). The stand-in below shows what encoding looks like.
  • The -Encoding utf8 choice is deliberate. Windows PowerShell 5.1 defaults differ between cmdlets, and names with accents are the first victims of a wrong guess.
flowchart LR
  A["users.csv"] --> B["Import-Csv"]
  B --> C{"Valid row?"}
  C -->|"yes"| D["good list"]
  C -->|"no"| E["rejected list"]
  D --> F["Select-Object<br/>calculated properties"]
  F --> G["JSON summary"]
  F --> H["HTML report"]
  E --> I["rejected.csv"]

Every row ends in exactly one list; the reports are built from the lists, never from the raw file.

The second stand-in does steps 2 and 3 on a small set of valid rows. One name contains <Admin> on purpose, so you can see why the HTML output must be escaped.

Python Stand-in: calculated properties, JSON and HTML

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The summary shows HR with one person at an average of 47500.0, IT with two at 65000.0 and Sales with two at 59000.0. In the HTML line, the name appears as Jo &lt;Admin&gt; Reyes: the angle brackets became harmless text. Remove the escape(...) call and the name would turn into a (broken) tag, which in a real page is the doorway to injection problems. The lesson on security covers that family of mistakes.

Making the script reusable

Once it works, turn the script into a tool other people can run safely.

  1. Parameters. Replace the hard-coded paths with a param() block: -Path (mandatory, validated with Test-Path), -OutputFolder and -AsOf. See Advanced Functions.
  2. Exit codes. Return a non-zero exit code when any row was rejected, so a scheduler or CI job can notice. exit 1 at the end of a script does that.
  3. Tests. A tiny validation function is easy to test with Pester: one row per rule, plus a row that passes everything.
  4. No secrets, no surprises. Reports of people contain personal data. Write them to a folder with proper permissions, and do not email them in the clear.

Make the validation a function that takes one row and returns the list of problems. The loop then becomes three lines, the function can be tested alone, and adding a rule later (say, "salary must be positive") touches one place.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What type are the values that Import-Csv puts in each property?

    Import-Csv creates string properties only. Convert to [int], [double] or [datetime] yourself, otherwise sorting and comparing behave like text.
  2. 2. Why does the validation loop collect all problems for a row instead of stopping at the first one?

    A complete list of problems per row saves several round trips of fix, rerun and fail again.
  3. 3. What does HashSet.Add() return when the value is already in the set?

    Add() returns $true for a new value and $false for a duplicate, which makes it a compact duplicate detector. With OrdinalIgnoreCase the comparison ignores letter case.
  4. 4. How do you add a new column named Band to objects in a Select-Object pipeline?

    A hashtable with Name and Expression keys defines a calculated property. Inside the script block, $_ is the current object.
  5. 5. Why should the HTML report escape names such as 'Jo <Admin> Reyes'?

    Data typed by people must be treated as text, not markup. Encoding turns < and & into harmless entities.

Summary

  • Import-Csv yields one object per row with string properties; convert types during validation.
  • Validate each row into a list of problems, then route the row to a good list or a rejected list. Never lose a row silently.
  • Use typed lists and a case-insensitive HashSet for speed and for duplicate detection.
  • Calculated properties (@{ Name = ...; Expression = { ... } }) add columns; Group-Object and Measure-Object build the summary.
  • Write rejects as CSV, the summary as JSON (set -Depth) and the report as HTML, and escape human-typed text.
  • Pin the reference date while testing so output is reproducible, and wrap validation in a function you can test.

Next, test your skills with the practice exercises.