CSV and Tabular Data with Import-Csv and Export-Csv

1. CSV is a table-shaped serialization, not a database

CSV means comma-separated values, but the important idea is broader: it is a text representation of rows and columns. Each row becomes one record and each column becomes one property when PowerShell imports the data. CSV is excellent for flat inventories, reports, handoffs to spreadsheets, and simple data exchange. It is a poor fit for deeply nested configuration because a cell is ultimately text.

Do not split CSV lines with -split ','. Quoted fields can legally contain commas, quotes, and line breaks. A CSV parser understands those rules; a simple string split does not.

$csv = @'
Name,Environment,Owner,Endpoint
api-01,prod,platform,"https://api.example.test/v1?mode=fast,strict"
worker-01,prod,data,"queue,critical"
'@

$rows = $csv | ConvertFrom-Csv
$rows | Select-Object Name, Environment, Owner, Endpoint

2. Import-Csv and Export-Csv turn rows into objects and back

Import-Csv reads a file and returns one custom object per data row. Export-Csv takes objects and writes their properties as columns. Their pipeline-only counterparts, ConvertFrom-Csv and ConvertTo-Csv, work with strings instead of files.

$lab = Join-Path ([System.IO.Path]::GetTempPath()) 'ps-ch11-csv'
New-Item -ItemType Directory -Path $lab -Force | Out-Null
$path = Join-Path $lab 'inventory.csv'

$inventory = @(
    [pscustomobject]@{ Name='api-01'; Environment='prod'; Port=443; Enabled=$true }
    [pscustomobject]@{ Name='worker-01'; Environment='prod'; Port=5672; Enabled=$false }
)

$inventory | Export-Csv -LiteralPath $path -Encoding utf8
$imported = Import-Csv -LiteralPath $path
$imported | Format-Table

3. Imported CSV values are usually strings

CSV has no portable type system for PowerShell integers, booleans, DateTime objects, or custom types. After import, values are normally strings. That matters because string comparison and numeric comparison are not the same operation.

$imported[0].Port.GetType().FullName
$imported[0].Enabled.GetType().FullName

# Convert deliberately at the boundary.
$typed = $imported | ForEach-Object {
    [pscustomobject]@{
        Name        = $_.Name
        Environment = $_.Environment
        Port        = [int]$_.Port
        Enabled     = [bool]::Parse($_.Enabled)
    }
}
$typed[0].Port.GetType().FullName

4. Delimiters, quotes, headers, and culture

A delimiter separates columns. Comma is the default, but semicolon and tab are common in regional or legacy exports. -Delimiter makes the delimiter explicit. -UseCulture uses the current culture's list separator; that can be useful for local spreadsheet interoperability, but explicit delimiters are often more predictable in CI.

-Header lets you supply column names when input has no header row. Keep the number and order of supplied names aligned with the source columns.

$semicolon = "api-01;prod;443`nworker-01;dev;8080"
$semicolon | ConvertFrom-Csv -Delimiter ';' -Header Name,Environment,Port

5. Encoding is part of the data contract

In current PowerShell, CSV cmdlets default to UTF-8 without a byte-order mark. Older Windows tooling may expect another encoding, so interoperability sometimes requires an explicit -Encoding. Do not change encoding casually: choose it because the consumer requires it.

$inventory | Export-Csv -LiteralPath $path -Encoding utf8
# For a known legacy consumer only, choose the required encoding explicitly.
# $inventory | Export-Csv -LiteralPath $path -Encoding ansi

6. NoTypeInformation: important history, usually unnecessary syntax now

Older Windows PowerShell versions wrote a #TYPE line by default. Starting in PowerShell 6, type information is omitted by default, so -NoTypeInformation is retained mainly for compatibility and readability of older scripts. -IncludeTypeInformation can opt back in, but CSV still stores property values as text and does not preserve methods.

7. Formatting belongs at the display boundary

Chapter 03 established that formatting is the end of the object pipeline. The same rule matters here. Format-Table creates formatting instructions; it does not select properties for serialization. If those formatting objects reach Export-Csv, the file contains formatting metadata instead of your intended records.

# Wrong: serializes formatting objects.
$bad = Join-Path $lab 'bad.csv'
$inventory | Format-Table Name,Environment | Export-Csv -LiteralPath $bad

# Correct: project properties, then serialize.
$good = Join-Path $lab 'good.csv'
$inventory | Select-Object Name,Environment | Export-Csv -LiteralPath $good
Import-Csv -LiteralPath $good

8. The first object establishes the exported column shape

When a stream contains objects with different properties, CSV is awkward because the format is rectangular. Export-Csv uses the first object's properties to establish columns. Later extra properties are not automatically added. Normalize records before export so every row has the same schema.

$normalized = $inventory | Select-Object Name,Environment,Port,Enabled
$normalized | Export-Csv -LiteralPath (Join-Path $lab 'normalized.csv')

9. Validate imported rows before trusting them

External CSV is untrusted input. Validate required fields, allowed environments, numeric ranges, and uniqueness before using records to drive deployments. Validation should produce clear evidence about which row failed.

$errors = foreach ($row in $imported) {
    if ([string]::IsNullOrWhiteSpace($row.Name)) { "Missing Name" }
    if ($row.Environment -notin 'dev','staging','prod') { "Invalid environment for $($row.Name)" }
    $port = 0
    if (-not [int]::TryParse($row.Port, [ref]$port) -or $port -notin 1..65535) {
        "Invalid port for $($row.Name): $($row.Port)"
    }
}
$errors

10. Lab: transform and validate an application inventory

Create a disposable inventory with a comma inside one quoted field, import it, validate and type the values, then export only the clean object contract.

$lab = Join-Path ([System.IO.Path]::GetTempPath()) 'ps-ch11-csv-lab'
New-Item -ItemType Directory -Path $lab -Force | Out-Null
$source = Join-Path $lab 'apps.csv'
@'
Name,Environment,Port,Enabled,Owners
api,prod,443,true,"platform,security"
worker,staging,5672,false,"data"
'@ | Set-Content -LiteralPath $source -Encoding utf8

$apps = Import-Csv -LiteralPath $source
$clean = foreach ($app in $apps) {
    if ($app.Environment -notin 'dev','staging','prod') { throw "Invalid environment: $($app.Environment)" }
    $port = 0
    if (-not [int]::TryParse($app.Port,[ref]$port)) { throw "Invalid port: $($app.Port)" }
    [pscustomobject]@{
        Name        = $app.Name
        Environment = $app.Environment
        Port        = $port
        Enabled     = [bool]::Parse($app.Enabled)
        Owners      = $app.Owners
    }
}

$target = Join-Path $lab 'validated.csv'
$clean | Export-Csv -LiteralPath $target -Encoding utf8
Import-Csv -LiteralPath $target | Format-Table
Remove-Item -LiteralPath $lab -Recurse -Force

Expected observations

• The quoted Owners field containing a comma imports as one field, not two columns.
• The validated export contains exactly two application rows with the selected property contract.
• After a second Import-Csv, cell values are text again; that demonstrates why consumers must convert types deliberately.

11. Verification checklist

• I can explain why quoted CSV fields defeat naive comma splitting.
• I can prove imported CSV cells require deliberate type conversion.
• I can export selected object properties without running a formatter first.
• All files were created only under the temporary lab directory, and cleanup removed that directory.

12. Common mistakes

• Splitting CSV with string operators instead of a CSV parser.
• Assuming imported numbers and booleans retain their original types.
• Formatting before exporting.
• Mixing records with inconsistent property sets.
• Depending on the machine culture when the file contract should be explicit.

13. Knowledge check

14. Summary and next bridge

CSV is a flat, interoperable representation. Use the parser, normalize rows, convert types explicitly, validate at the boundary, and serialize objects rather than formatting instructions. Next, JSON adds nested objects and arrays—the shape used by most REST APIs and modern configuration exchanges.

15. Authoritative references

• Microsoft Learn — Import-Csv
• Microsoft Learn — Export-Csv
• Microsoft Learn — ConvertFrom-Csv
• Microsoft Learn — ConvertTo-Csv