PS Working with CSV XML and JSON

Modern systems exchange data in structured formats. CSV (Comma-Separated Values) stores tabular data like spreadsheets. JSON (JavaScript Object Notation) is the standard for APIs and web services. XML (Extensible Markup Language) is used for configuration files, web services, and enterprise systems. PowerShell has native cmdlets for all three formats — making data import, export, and transformation straightforward without external libraries.

Working with CSV Files

CSV files store rows of data with comma-separated columns. The first row is typically the header row with column names.

Sample CSV File – users.csv

Name,Department,Email,Salary
Alice,IT,alice@company.com,80000
Bob,HR,bob@company.com,55000
Carol,Finance,carol@company.com,70000
Dave,IT,dave@company.com,90000

Import-Csv – Read CSV into Objects

# Import CSV – each row becomes a PSCustomObject
$users = Import-Csv -Path "C:\Data\users.csv"

# Access all records
$users | Format-Table

# Access specific property
$users[0].Name         # Alice
$users[0].Department   # IT

# Filter rows
$users | Where-Object { $_.Department -eq "IT" }

# Sort by salary
$users | Sort-Object Salary -Descending

Output:

Name   Department  Email               Salary
----   ----------  -----               ------
Alice  IT          alice@company.com   80000
Bob    HR          bob@company.com     55000
Carol  Finance     carol@company.com   70000
Dave   IT          dave@company.com    90000

Export-Csv – Write Objects to CSV

# Create objects to export
$report = @(
    [PSCustomObject]@{ Server="Web01"; Status="Online"; CPU=12 },
    [PSCustomObject]@{ Server="DB01";  Status="Online"; CPU=45 },
    [PSCustomObject]@{ Server="App01"; Status="Offline"; CPU=0 }
)

# Export to CSV
$report | Export-Csv -Path "C:\Reports\servers.csv" -NoTypeInformation

# -NoTypeInformation removes the #TYPE header line from the file

Generated CSV content:

"Server","Status","CPU"
"Web01","Online","12"
"DB01","Online","45"
"App01","Offline","0"

ConvertTo-Csv and ConvertFrom-Csv (In-Memory)

# Convert objects to CSV text (no file)
$csvText = $report | ConvertTo-Csv -NoTypeInformation
Write-Host $csvText

# Convert CSV text back to objects
$objects = $csvText | ConvertFrom-Csv
$objects | Format-Table

Append to an Existing CSV

$newRecord = [PSCustomObject]@{ Server="Cache01"; Status="Online"; CPU=8 }
$newRecord | Export-Csv -Path "C:\Reports\servers.csv" -Append -NoTypeInformation

Working with JSON

JSON is the primary format for REST APIs, configuration files, and web data exchange.

Sample JSON – config.json

{
    "AppName": "eStudy247",
    "Version": "3.0",
    "Database": {
        "Server": "db.internal",
        "Port": 5432,
        "Name": "estudy_prod"
    },
    "Features": ["Dashboard", "Analytics", "Reports"],
    "Debug": false
}

Get-Content + ConvertFrom-Json – Read JSON

# Read JSON file and convert to PowerShell object
$config = Get-Content -Path "C:\Config\config.json" -Raw | ConvertFrom-Json

# Access properties
Write-Host $config.AppName                # eStudy247
Write-Host $config.Version               # 3.0
Write-Host $config.Database.Server       # db.internal
Write-Host $config.Database.Port         # 5432
Write-Host $config.Features[0]           # Dashboard
Write-Host $config.Debug                 # False

ConvertTo-Json – Convert Objects to JSON

$serverInfo = [PSCustomObject]@{
    Hostname    = "WebServer01"
    IP          = "192.168.1.10"
    Environment = "Production"
    Services    = @("IIS", "WinRM", "W3SVC")
    Healthy     = $true
}

# Convert to JSON
$json = $serverInfo | ConvertTo-Json
Write-Host $json

Output:

{
    "Hostname":  "WebServer01",
    "IP":  "192.168.1.10",
    "Environment":  "Production",
    "Services":  [
        "IIS",
        "WinRM",
        "W3SVC"
    ],
    "Healthy":  true
}

Control JSON Depth

# Default depth is 2 – use -Depth for nested objects
$data | ConvertTo-Json -Depth 5

Save JSON to File

$config | ConvertTo-Json -Depth 4 |
    Set-Content -Path "C:\Config\server_config.json" -Encoding UTF8

Update a JSON Configuration File

# Read existing config
$cfg = Get-Content "C:\Config\app.json" -Raw | ConvertFrom-Json

# Modify a value
$cfg.Version = "4.0"
$cfg.Database.Port = 5433

# Save back to file
$cfg | ConvertTo-Json -Depth 5 | Set-Content "C:\Config\app.json"

Write-Host "Config updated."

Working with XML

XML uses tags to define a hierarchical data structure. It is widely used in Windows configurations, web services, and enterprise software.

Sample XML – servers.xml

<?xml version="1.0" encoding="UTF-8"?>
<Infrastructure>
  <Server name="Web01" type="Web">
    <IP>192.168.1.10</IP>
    <Status>Online</Status>
    <CPU>12</CPU>
  </Server>
  <Server name="DB01" type="Database">
    <IP>192.168.1.20</IP>
    <Status>Online</Status>
    <CPU>45</CPU>
  </Server>
</Infrastructure>

Reading XML

# Load XML file
[xml]$xml = Get-Content -Path "C:\Data\servers.xml"

# Access root element
$xml.Infrastructure

# Access child elements
$xml.Infrastructure.Server

# Access specific node
$xml.Infrastructure.Server[0].name    # Web01
$xml.Infrastructure.Server[1].IP      # 192.168.1.20

# Loop through all servers
foreach ($server in $xml.Infrastructure.Server) {
    Write-Host "Name: $($server.name) | IP: $($server.IP) | Status: $($server.Status)"
}

Output:

Name: Web01 | IP: 192.168.1.10 | Status: Online
Name: DB01  | IP: 192.168.1.20 | Status: Online

Modifying XML

[xml]$xml = Get-Content -Path "C:\Data\servers.xml"

# Update a value
$xml.Infrastructure.Server[1].Status = "Maintenance"

# Add a new element
$newServer       = $xml.CreateElement("Server")
$newServer.SetAttribute("name", "Cache01")
$newServer.SetAttribute("type", "Cache")

$ipNode          = $xml.CreateElement("IP")
$ipNode.InnerText = "192.168.1.30"
$newServer.AppendChild($ipNode) | Out-Null

$statusNode          = $xml.CreateElement("Status")
$statusNode.InnerText = "Online"
$newServer.AppendChild($statusNode) | Out-Null

$xml.Infrastructure.AppendChild($newServer) | Out-Null

# Save back to file
$xml.Save("C:\Data\servers.xml")
Write-Host "XML updated and saved."

Select-Xml – XPath Queries

[xml]$xml = Get-Content "C:\Data\servers.xml"

# XPath query – find servers with Status = Online
$results = Select-Xml -Xml $xml -XPath "//Server[Status='Online']"

foreach ($result in $results) {
    Write-Host $result.Node.name
}

Format Comparison

Real-World Example – Generate a JSON Report from CSV Data

# Read CSV data
$employees = Import-Csv -Path "C:\Data\employees.csv"

# Transform to enriched objects
$report = $employees | ForEach-Object {
    [PSCustomObject]@{
        Name       = $_.Name
        Department = $_.Department
        Email      = $_.Email
        Salary     = [decimal]$_.Salary
        SalaryGrade = if ([decimal]$_.Salary -ge 80000) { "Senior" }
                      elseif ([decimal]$_.Salary -ge 60000) { "Mid" }
                      else { "Junior" }
    }
}

# Export as JSON report
$jsonReport = @{
    GeneratedAt = (Get-Date -Format "yyyy-MM-dd HH:mm:ss")
    TotalCount  = $report.Count
    Employees   = $report
}

$jsonReport | ConvertTo-Json -Depth 4 |
    Set-Content "C:\Reports\employees_report.json" -Encoding UTF8

Write-Host "JSON report saved with $($report.Count) records."

Summary

PowerShell handles CSV, JSON, and XML as first-class data formats. Import-Csv and Export-Csv read and write tabular data cleanly. ConvertFrom-Json and ConvertTo-Json bridge PowerShell with APIs and configuration systems. The [xml] type cast and Select-Xml with XPath queries provide full control over hierarchical XML documents. These capabilities make PowerShell a complete data processing tool — importing from any format, transforming with pipeline logic, and exporting to any required format.