CSV, JSON, and XML

Read, convert, and write CSV, JSON, and XML in PowerShell, including the type, depth, delimiter, and encoding traps that silently corrupt real data.

By The Dark DebuggerUpdated 29 min read

Most automation sooner or later reads a file somebody else produced: an HR export, an appsettings.json, a web.config, the JSON that terraform or kubectl prints. This article covers the four formats PowerShell handles natively (CSV, JSON, XML and its own CliXml), with the traps that corrupt data without raising an error. It assumes you are comfortable with the object pipeline and custom objects and hashtables, and it builds on working with files and folders. Next in the path is regular expressions, for the text that has no structure at all.

Why this matters

Picture a Monday-morning request: "HR sent the user export again, can you tell us which accounts haven't logged in for two months and how much mailbox storage each department uses?" The file looks like this:

SamAccountName,DisplayName,Department,Title,LastLogon,MailboxMB,Enabled
asmith,"Smith, Alice",Engineering,Senior Engineer,2026-11-06,3420,True
bjones,"Jones, Bob",Finance,Accountant,2026-08-14,890,True
cnguyen,"Nguyen, Chi",Engineering,DevOps Engineer,2026-11-07,12050,True
dpatel,"Patel, Dev",Sales,Account Manager,2026-05-30,640,True
eobrien,"O'Brien, Eve",Finance,Controller,2026-11-03,2210,True
fgarcia,"Garcia, Felix",Sales,Sales Director,2026-11-05,7800,True
gkim,"Kim, Grace",Engineering,QA Lead,2026-07-21,1500,False
hmuller,"Müller, Hans",Support,Support Engineer,2026-11-06,350,True

Splitting each line on commas breaks on the very first row, because "Smith, Alice" contains a comma. PowerShell's format cmdlets parse the quoting for you and hand you objects, so you can sort, filter, group and export without touching a string. They also come with a handful of defaults that will quietly give you wrong answers. We'll hit every one of them with this file, save it as users.csv if you want to follow along.

Import-Csv turns rows into objects

$users = Import-Csv ./users.csv
$users | Format-Table SamAccountName, DisplayName, Department, MailboxMB, Enabled
SamAccountName DisplayName   Department  MailboxMB Enabled
-------------- -----------   ----------  --------- -------
asmith         Smith, Alice  Engineering 3420      True
bjones         Jones, Bob    Finance     890       True
cnguyen        Nguyen, Chi   Engineering 12050     True
dpatel         Patel, Dev    Sales       640       True
eobrien        O'Brien, Eve  Finance     2210      True
fgarcia        Garcia, Felix Sales       7800      True
gkim           Kim, Grace    Engineering 1500      False
hmuller        Müller, Hans  Support     350       True

The header row became property names and each line became one PSCustomObject. The quoted "Smith, Alice" survived intact. Notice something about the MailboxMB column, though: the numbers are left-aligned. PowerShell right-aligns numbers in tables, so that is your first hint. Ask the object what it holds:

$users = Import-Csv ./users.csv
$users[0] | Get-Member -MemberType NoteProperty
   TypeName: System.Management.Automation.PSCustomObject
 
Name           MemberType   Definition
----           ----------   ----------
Department     NoteProperty string Department=Engineering
DisplayName    NoteProperty string DisplayName=Smith, Alice
Enabled        NoteProperty string Enabled=True
LastLogon      NoteProperty string LastLogon=2026-11-06
MailboxMB      NoteProperty string MailboxMB=3420
SamAccountName NoteProperty string SamAccountName=asmith
Title          NoteProperty string Title=Senior Engineer

Every value from a CSV is a string. CSV has no types, so PowerShell can't guess them. That has two consequences you will hit in the first week.

Strings sort and compare like strings

"Show me the three biggest mailboxes":

$users = Import-Csv ./users.csv
$users | Sort-Object MailboxMB -Descending | Select-Object -First 3 SamAccountName, MailboxMB
SamAccountName MailboxMB
-------------- ---------
bjones         890
fgarcia        7800
dpatel         640

That's alphabetical order: "890" beats "7800" because 8 sorts after 7. The biggest mailbox (12,050 MB) isn't in the list at all. Comparisons have the same problem, because PowerShell converts the right-hand side to the type of the left-hand side. Here's /etc/passwd, which is a colon-delimited CSV without a header, filtered for "real" users with a UID of 1000 or more:

Get-Content /etc/passwd |
    ConvertFrom-Csv -Delimiter ':' -Header User, Pw, Uid, Gid, Gecos, Home, Shell |
    Where-Object Uid -ge 1000 |
    Format-Table User, Uid, Home, Shell
User             Uid   Home               Shell
----             ---   ----               -----
bin              2     /bin               /usr/sbin/nologin
sys              3     /dev               /usr/sbin/nologin
sync             4     /bin               /bin/sync
[…]
systemd-network  998   /                  /usr/sbin/nologin
polkitd          990   /                  /usr/sbin/nologin
sid              1000  /home/sid          /bin/bash

"2" -ge "1000" is true as a string comparison. No error, just a wrong answer. Cast the left-hand side and it works:

Get-Content /etc/passwd |
    ConvertFrom-Csv -Delimiter ':' -Header User, Pw, Uid, Gid, Gecos, Home, Shell |
    Where-Object { [int]$_.Uid -ge 1000 } |
    Format-Table User, Uid, Home, Shell
User   Uid   Home         Shell
----   ---   ----         -----
nobody 65534 /nonexistent /usr/sbin/nologin
sid    1000  /home/sid    /bin/bash

"False" is true

The second trap is booleans. A non-empty string is truthy, and "False" is a non-empty string:

$users = Import-Csv ./users.csv
($users | Where-Object { -not $_.Enabled }).Count
[bool]'False'
0
True

Grace's account is disabled, but the filter found nobody.

Fix the types once, at import time

Don't sprinkle casts through the rest of the script. Convert each row into a properly typed object as it comes in, and everything downstream just works:

$users = Import-Csv ./users.csv | ForEach-Object {
    [pscustomobject]@{
        User       = $_.SamAccountName
        Name       = $_.DisplayName
        Department = $_.Department
        LastLogon  = [datetime]$_.LastLogon
        MailboxMB  = [int]$_.MailboxMB
        Enabled    = $_.Enabled -eq 'True'
    }
}
$users | Sort-Object MailboxMB -Descending | Select-Object -First 3 User, MailboxMB | Format-Table
$users | Where-Object { -not $_.Enabled } | Format-Table User, Department, LastLogon
$users[0].LastLogon.GetType().FullName
User    MailboxMB
----    ---------
cnguyen     12050
fgarcia      7800
asmith      3420
 
 
User Department  LastLogon
---- ----------  ---------
gkim Engineering 07/21/2026 00:00:00
 
System.DateTime

Numbers are right-aligned now, the sort is numeric, and the disabled account shows up. Two details:

  • [datetime]'2026-07-21' always parses with the invariant culture, so ISO dates are safe on any machine. For something like 21/07/2026, use [datetime]::ParseExact($s, 'dd/MM/yyyy', [cultureinfo]::InvariantCulture) rather than relying on the current culture.
  • $_.Enabled -eq 'True' is case-insensitive (-eq always is for strings), so it also copes with TRUE and true from other exporters.

Delimiters, headers, and culture

Not every "CSV" uses commas. Excel saves CSV with the list separator of the user's regional settings, which is a semicolon in Germany, France and much of Europe, because the comma is their decimal separator. Here's a file from a German colleague:

Benutzer;Abteilung;Postfach_MB;Kosten
asmith;Engineering;3420;12,50
bjones;Finance;890;4,75

Import it with the defaults and you get a telling symptom: one property whose name is the entire header line.

Import-Csv ./users-de.csv | Format-List
Benutzer;Abteilung;Postfach_MB;Kosten : asmith;Engineering;3420;12
 
Benutzer;Abteilung;Postfach_MB;Kosten : bjones;Finance;890;4

Worse, the comma inside 12,50 was treated as a delimiter, so the cost was cut to 12. Tell it the delimiter:

Import-Csv ./users-de.csv -Delimiter ';' | Format-Table
Benutzer Abteilung   Postfach_MB Kosten
-------- ---------   ----------- ------
asmith   Engineering 3420        12,50
bjones   Finance     890         4,75

-UseCulture takes the separator from (Get-Culture).TextInfo.ListSeparator instead. When I started pwsh with LANG=de_DE.UTF-8, the culture was de-DE, the separator was ;, and Import-Csv ./users-de.csv -UseCulture produced the same table as above. My recommendation: use -UseCulture only for files a human exported from Excel on the same machine. For files that travel between systems, state the delimiter explicitly, or your script behaves differently depending on who runs it.

The decimal comma needs the same care once you convert the column. A cast doesn't fail, it gives you a number a hundred times too big:

[decimal]'12,50'
[decimal]::Parse('12,50', [cultureinfo]'de-DE')
1250
12.50

PowerShell casts always use the invariant culture, where a comma is a thousands separator. Parse with the culture the file was written in.

The other switches you'll need:

ParameterUse it when
-Delimiter "`t"Tab-separated files (TSV), common from databases and spreadsheets
-Header A, B, CThe file has no header row (as in the /etc/passwd example above)
-Encoding utf8Accented names come out garbled (Müller): tell it the file's real encoding
ConvertFrom-CsvThe CSV is already in memory: a here-string, an API response, a native command's output

Export-Csv, and what changed between 5.1 and 7

Writing CSV is Select-Object the columns you want, then Export-Csv. A calculated property converts megabytes to gigabytes on the way out:

$report = Import-Csv ./users.csv |
    Where-Object Department -eq 'Engineering' |
    Select-Object SamAccountName, DisplayName, @{ Name = 'MailboxGB'; Expression = { [math]::Round([int]$_.MailboxMB / 1024, 2) } }
$report | Export-Csv ./engineering.csv
Get-Content ./engineering.csv
"SamAccountName","DisplayName","MailboxGB"
"asmith","Smith, Alice","3.34"
"cnguyen","Nguyen, Chi","11.77"
"gkim","Kim, Grace","1.46"

Run the same kind of export in Windows PowerShell 5.1 and the first line is different:

#TYPE Selected.System.Management.Automation.PSCustomObject
"SamAccountName","Department"
"asmith","Engineering"
"bjones","Finance"

That #TYPE line confuses Excel and every non-PowerShell parser. On 5.1, always pass -NoTypeInformation. PowerShell 7 dropped the line by default and still accepts the switch, so scripts that must run on both should simply always include it.

PowerShell 7 also quotes every field. Most consumers don't care, but some picky importers do. -UseQuotes AsNeeded (7 only) quotes only fields that need it:

Import-Csv ./users.csv | Select-Object SamAccountName, Department |
    Export-Csv ./asneeded.csv -UseQuotes AsNeeded
Get-Content ./asneeded.csv -TotalCount 3
SamAccountName,Department
asmith,Engineering
bjones,Finance

Arrays don't fit in a cell

CSV is flat. If a property holds an array, Export-Csv writes the type name instead of the data:

$servers = @(
    [pscustomobject]@{ Name = 'web01'; Roles = @('web', 'cache') }
    [pscustomobject]@{ Name = 'db01';  Roles = @('database') }
)
$servers | Export-Csv ./servers.csv
Get-Content ./servers.csv
$servers | Select-Object Name, @{ Name = 'Roles'; Expression = { $_.Roles -join ';' } } |
    Export-Csv ./servers.csv
Get-Content ./servers.csv
"Name","Roles"
"web01","System.Object[]"
"db01","System.Object[]"
"Name","Roles"
"web01","web;cache"
"db01","database"

Flatten nested data with -join (pick a separator that isn't the CSV delimiter), or use JSON when the nesting matters.

ConvertTo-Csv for in-memory text

ConvertTo-Csv produces the same lines as an array of strings instead of writing a file, which is handy for an email body or a chat message. ConvertFrom-Csv goes the other way, as in the /etc/passwd example:

$csv = Import-Csv ./users.csv | Select-Object -First 2 SamAccountName, Department | ConvertTo-Csv
$csv
$csv.GetType().Name
"SamAccountName","Department"
"asmith","Engineering"
"bjones","Finance"
Object[]

JSON: ConvertFrom-Json and ConvertTo-Json

JSON is what APIs return, what modern apps use for configuration, and what most CLI tools print with a -json or -o json flag. There is no Import-Json: you read the file as one string and convert it. Here is the appsettings.json we'll work with:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "Serilog": {
    "WriteTo": [
      { "Name": "File", "Args": { "path": "/var/log/orders/app-.log", "rollingInterval": "Day" } }
    ]
  },
  "ConnectionStrings": {
    "Orders": "Server=sql01;Database=orders;Integrated Security=true"
  },
  "FeatureFlags": {
    "NewCheckout": false,
    "BetaReports": true
  },
  "Endpoints": [
    { "Name": "payments", "Url": "https://payments.internal/api", "TimeoutSec": 30 },
    { "Name": "inventory", "Url": "https://inventory.internal/api", "TimeoutSec": 15 }
  ],
  "DeployedAt": "2026-10-28T14:05:00Z",
  "AllowedHosts": "*"
}
$config = Get-Content ./appsettings.json -Raw | ConvertFrom-Json
$config.FeatureFlags
$config.Endpoints | Format-Table
$config.Logging.LogLevel.'Microsoft.AspNetCore'
$config.Serilog.WriteTo[0].Args.path
NewCheckout BetaReports
----------- -----------
      False        True
 
 
Name      Url                            TimeoutSec
----      ---                            ----------
payments  https://payments.internal/api          30
inventory https://inventory.internal/api         15
 
Warning
/var/log/orders/app-.log

JSON objects become PSCustomObjects and arrays become arrays, so dot notation walks the tree. A key containing a dot or a space needs quotes: .'Microsoft.AspNetCore'. Use -Raw so Get-Content returns one string instead of an array of lines; it's faster and avoids edge cases with how the lines get rejoined.

Unlike CSV, JSON carries types, and PowerShell 7 goes one step further than you might expect:

$config = Get-Content ./appsettings.json -Raw | ConvertFrom-Json
$config.Endpoints[0].TimeoutSec.GetType().Name
$config.FeatureFlags.NewCheckout.GetType().Name
$config.DeployedAt.GetType().Name
$config.DeployedAt.Kind
$config.DeployedAt.ToString('o')
Int64
Boolean
DateTime
Utc
2026-10-28T14:05:00.0000000Z

Numbers and booleans are real types, and strings that look like ISO 8601 dates are converted to DateTime. Windows PowerShell 5.1 leaves DeployedAt as a String, so code that calls .Substring() on it works in 5.1 and breaks in 7. If you need the original text, PowerShell 7.5 added -DateKind String:

$c = Get-Content ./appsettings.bak.json -Raw | ConvertFrom-Json -DateKind String
$c.DeployedAt.GetType().Name
$c.DeployedAt
String
2026-10-28T14:05:00Z

The -Depth trap

This is the one that bites everybody once. Convert the config straight back to JSON:

$config = Get-Content ./appsettings.json -Raw | ConvertFrom-Json
$config | ConvertTo-Json
WARNING: Resulting JSON is truncated as serialization has exceeded the set depth of 2.
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "Serilog": {
    "WriteTo": [
      "@{Name=File; Args=}"
    ]
  },
[…]

ConvertTo-Json only serializes two levels deep by default. Anything deeper is replaced with its string form, here "@{Name=File; Args=}", and the Serilog file path is gone. If you'd piped that into Set-Content, the application would fail to start on the next deploy. PowerShell 7 at least prints the warning. Windows PowerShell 5.1 prints the same truncated output with no warning at all: when I ran it, the warning stream was empty.

The fix is to always pass -Depth when converting anything you didn't build by hand. The maximum is 100; I use 10 for config files, which covers anything sane.

Editing a JSON config file

Flip a feature flag, add a new one and append an endpoint. The first attempt at adding a property fails:

$config = Get-Content ./appsettings.json -Raw | ConvertFrom-Json
$config.FeatureFlags.NewCheckout = $true
$config.FeatureFlags.DarkMode = $false
SetValueInvocationException:
Line |
   3 |  $config.FeatureFlags.DarkMode = $false
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception setting "DarkMode": "The property 'DarkMode' cannot be found on this object. Verify that the property exists and can be set."

Changing an existing property works; creating one on a PSCustomObject needs Add-Member. The complete, working edit, with a backup first:

Copy-Item ./appsettings.json ./appsettings.bak.json
$config = Get-Content ./appsettings.json -Raw | ConvertFrom-Json
$config.FeatureFlags.NewCheckout = $true
$config.FeatureFlags | Add-Member -NotePropertyName DarkMode -NotePropertyValue $false
$config.Endpoints += [pscustomobject]@{ Name = 'shipping'; Url = 'https://shipping.internal/api'; TimeoutSec = 20 }
$config | ConvertTo-Json -Depth 10 | Set-Content ./appsettings.json
Get-Content ./appsettings.json
[…]
        "Args": {
          "path": "/var/log/orders/app-.log",
          "rollingInterval": "Day"
        }
[…]
  "FeatureFlags": {
    "NewCheckout": true,
    "BetaReports": true,
    "DarkMode": false
  },
  "Endpoints": [
[…]
    {
      "Name": "shipping",
      "Url": "https://shipping.internal/api",
      "TimeoutSec": 20
    }
  ],
  "DeployedAt": "2026-10-28T14:05:00Z",
  "AllowedHosts": "*"
}

Property order is preserved, the Serilog block survived, and the date round-tripped as Z because it was parsed as UTC. What you lose is formatting: the one-line endpoint objects are now expanded. If the file is in git, expect a noisy diff the first time.

Comments are lost too. PowerShell 7 accepts // comments in JSON (common in appsettings.Development.json and tsconfig.json), but discards them on read: when I round-tripped a file that started with // local overrides - never deployed, the comment was simply gone from the output. If comments in a file matter, edit it with a targeted text replacement instead (see regular expressions).

-AsHashtable (PowerShell 7)

-AsHashtable returns hashtables instead of objects. That buys you three things: you can add keys with plain assignment, you can test for keys with ContainsKey, and you can read JSON whose keys differ only by case, which the default mode refuses:

'{ "Id": 1, "id": 2 }' | ConvertFrom-Json
ConvertFrom-Json: Cannot convert the JSON string because it contains keys with different casing. Please use the -AsHashTable switch instead. The key that was attempted to be added to the existing key 'Id' was 'id'.
$h = '{ "Id": 1, "id": 2 }' | ConvertFrom-Json -AsHashtable
$h
$cfg = Get-Content ./appsettings.bak.json -Raw | ConvertFrom-Json -AsHashtable
$cfg.GetType().FullName
$cfg.FeatureFlags['DarkMode'] = $false
$cfg.FeatureFlags
$cfg.ContainsKey('Serilog')
Name                           Value
----                           -----
Id                             1
id                             2
System.Management.Automation.OrderedHashtable
NewCheckout                    False
BetaReports                    True
DarkMode                       False
True

It's an ordered hashtable, so writing it back keeps the key order. The catch: the hashtable is case-sensitive in that mode, so $cfg.featureflags returns nothing.

Arrays of one and dates on the way out

Two output details that break consumers:

$servers = @([pscustomobject]@{ Name = 'web01'; Healthy = $true })
$servers | ConvertTo-Json -Compress
$servers | ConvertTo-Json -Compress -AsArray
ConvertTo-Json -InputObject $servers -Compress
{"Name":"web01","Healthy":true}
[{"Name":"web01","Healthy":true}]
[{"Name":"web01","Healthy":true}]

Piping an array unrolls it, so a one-element array arrives at ConvertTo-Json as a single object and the API that expected [...] gets {...}. Use -AsArray (7 only) or pass the array with -InputObject, which works on 5.1 too. (Enums have a similar surprise: they serialize as numbers, so DayOfWeek Monday becomes 1 unless you add -EnumsAsStrings.)

[pscustomobject]@{ When = [datetime]'2026-11-09T06:00:00'; Day = [System.DayOfWeek]::Monday } | ConvertTo-Json -Compress
{"When":"2026-11-09T06:00:00","Day":1}

Dates are ISO 8601 in PowerShell 7. Windows PowerShell 5.1 writes a date in the old ASP.NET format:

{"When":"\/Date(1794222000000)\/"}

If a 5.1 script talks to an API, format dates yourself with .ToString('o').

File encoding when you save

Set-Content, Out-File and > write UTF-8 without a BOM in PowerShell 7. In Windows PowerShell 5.1 they disagree with each other. I ran this in a clean 5.1 session (powershell.exe -NoProfile):

$json = '{"name":"Müller"}'
$json | Out-File .\out-file.json
$json | Set-Content .\set-content.json
$json | Set-Content .\set-content-utf8.json -Encoding UTF8
Get-ChildItem *.json -Exclude appsettings.json | ForEach-Object {
    $bytes = [System.IO.File]::ReadAllBytes($_.FullName) | Select-Object -First 6
    '{0,-22} {1}' -f $_.Name, (($bytes | ForEach-Object { $_.ToString('X2') }) -join ' ')
}
out-file.json          FF FE 7B 00 22 00
set-content-utf8.json  EF BB BF 7B 22 6E
set-content.json       7B 22 6E 61 6D 65

Out-File (and >) wrote UTF-16 (FF FE, a zero byte after every character), which many JSON parsers reject. Set-Content wrote the ANSI code page, which mangles ü for anything expecting UTF-8. -Encoding UTF8 gives UTF-8 with a BOM (EF BB BF), which .NET and most tools accept. On 5.1, pass -Encoding UTF8 every time you write a data file.

XML: the [xml] accelerator

XML still runs a lot of the world: .NET app.config and web.config, Maven pom.xml, MSBuild project files, RSS feeds, SAML metadata. Cast a string to [xml] and you get an XmlDocument that PowerShell lets you navigate with dot notation:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="Environment" value="staging" />
    <add key="MaxUploadMB" value="25" />
    <add key="ReportsPath" value="/srv/reports" />
  </appSettings>
  <connectionStrings>
    <add name="Orders" connectionString="Server=sql01;Database=orders;Integrated Security=true" />
  </connectionStrings>
  <servers>
    <server name="web01" role="web" ip="10.0.1.11" />
    <server name="web02" role="web" ip="10.0.1.12" />
    <server name="db01" role="database" ip="10.0.2.21" />
  </servers>
</configuration>
[xml]$cfg = Get-Content ./app.config -Raw
$cfg.configuration.appSettings.add
$cfg.configuration.servers.server | Where-Object role -eq 'web' | Format-Table name, ip
key         value
---         -----
Environment staging
MaxUploadMB 25
ReportsPath /srv/reports
 
 
name  ip
----  --
web01 10.0.1.11
web02 10.0.1.12

Child elements and attributes both show up as properties, and repeated elements become arrays you can pipe. Attribute values are always strings, just like CSV. Each node is a real System.Xml.XmlElement, and when an attribute shares a name with one of its .NET properties (name vs Name), the attribute wins: server[0].name returned web01 when I tried it. Use .LocalName when you need the tag name itself.

XPath with SelectNodes and Select-Xml

Dot notation is fine for walking a known path. For "find the node where…", use XPath:

[xml]$cfg = Get-Content ./app.config -Raw
$cfg.SelectSingleNode("//appSettings/add[@key='MaxUploadMB']").value
$cfg.SelectNodes("//server[@role='web']/@ip") | ForEach-Object Value
Select-Xml -Path ./app.config -XPath "//server[starts-with(@ip, '10.0.2.')]" |
    ForEach-Object { $_.Node.name }
25
10.0.1.11
10.0.1.12
db01
XPathMeaning
//serverEvery server element anywhere in the document
/configuration/appSettings/addAn exact path from the root
//add[@key='MaxUploadMB']Elements whose attribute equals a value
//server[@role='web']/@ipThe ip attribute nodes of matching elements
//server[starts-with(@ip, '10.0.2.')]XPath 1.0 string functions (contains, starts-with)

Select-Xml reads files directly and returns wrapper objects; the element itself is in .Node.

Namespaces: why XPath finds nothing

Many real XML formats declare a default namespace. Maven's pom.xml is a classic:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.example</groupId>
  <artifactId>orders-service</artifactId>
  <version>2.3.1</version>
  <dependencies>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.7.4</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.18.1</version>
    </dependency>
  </dependencies>
</project>
[xml]$pom = Get-Content ./pom.xml -Raw
$pom.project.version
$pom.SelectNodes('//dependency').Count
$ns = @{ m = 'http://maven.apache.org/POM/4.0.0' }
Select-Xml -Path ./pom.xml -XPath '//m:dependency' -Namespace $ns |
    ForEach-Object { '{0}:{1}' -f $_.Node.artifactId, $_.Node.version }
2.3.1
0
postgresql:42.7.4
jackson-databind:2.18.1

Dot notation ignores namespaces, so $pom.project.version works. XPath doesn't: plain //dependency matches zero nodes with no error. Give the namespace a prefix with -Namespace and use that prefix in the query.

Modifying and saving XML

Change an attribute, add an element, remove an element, save:

$path = Join-Path $PWD 'app.config'
[xml]$cfg = Get-Content $path -Raw
 
# 1. Change an attribute
$cfg.SelectSingleNode("//appSettings/add[@key='MaxUploadMB']").value = '100'
 
# 2. Add a new <add key=... value=... /> element
$node = $cfg.CreateElement('add')
$node.SetAttribute('key', 'MaintenanceMode')
$node.SetAttribute('value', 'false')
[void]$cfg.configuration.appSettings.AppendChild($node)
 
# 3. Remove a decommissioned server
$old = $cfg.SelectSingleNode("//server[@name='web02']")
[void]$old.ParentNode.RemoveChild($old)
 
$cfg.Save($path)
Get-Content $path
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="Environment" value="staging" />
    <add key="MaxUploadMB" value="100" />
    <add key="ReportsPath" value="/srv/reports" />
    <add key="MaintenanceMode" value="false" />
  </appSettings>
  <connectionStrings>
    <add name="Orders" connectionString="Server=sql01;Database=orders;Integrated Security=true" />
  </connectionStrings>
  <servers>
    <server name="web01" role="web" ip="10.0.1.11" />
    <server name="db01" role="database" ip="10.0.2.21" />
  </servers>
</configuration>

AppendChild and RemoveChild return the node they touched; [void] stops it from cluttering your output. And note $path is built with Join-Path $PWD. That's not decoration, as the Common mistakes section shows.

Export-Clixml: round-tripping PowerShell objects

CliXml is PowerShell's own serialization format, the one remoting and background jobs use under the hood. Export-Clixml writes objects with their type information, and Import-Clixml restores them. Compare a round trip through CliXml with one through JSON:

$state = [pscustomobject]@{
    LastRun   = [datetime]'2026-11-09T06:00:00'
    Processed = 1250
    Failed    = @('web02')
    Options   = @{ Retries = 3; DryRun = $false }
}
$state | Export-Clixml ./state.xml
$fromXml  = Import-Clixml ./state.xml
$fromJson = $state | ConvertTo-Json | ConvertFrom-Json
foreach ($p in 'LastRun', 'Processed', 'Failed', 'Options') {
    [pscustomobject]@{
        Property = $p
        Original = $state.$p.GetType().Name
        Clixml   = $fromXml.$p.GetType().Name
        Json     = $fromJson.$p.GetType().Name
    }
}
Property  Original  Clixml    Json
--------  --------  ------    ----
LastRun   DateTime  DateTime  DateTime
Processed Int32     Int32     Int64
Failed    Object[]  ArrayList Object[]
Options   Hashtable Hashtable PSCustomObject

CliXml kept the Int32 and the hashtable; JSON turned the hashtable into an object (no more .ContainsKey()). That makes CliXml the right choice for a script that saves its own state between runs, such as "last processed timestamp" or a cache of a slow query.

It does not preserve live objects. When I exported Get-Process -Id $PID and imported it again, the type name was Deserialized.System.Diagnostics.Process: every property was there, but the only methods left were GetType and ToString, so no .Kill(). You'll see the same Deserialized. prefix on everything that comes back from background jobs and remoting.

Credentials: safe on Windows only

The common advice is "store a credential with Export-Clixml". On Windows that works because the password is encrypted with DPAPI, tied to your user account on that machine. In Windows PowerShell 5.1 the password element starts like this:

01000000d08c9ddf0115d1118c7a00c04fc297eb...

Now the same export with PowerShell 7 on Linux:

$cred = [pscredential]::new('svc-backup', (ConvertTo-SecureString 'NotARealPassword1' -AsPlainText -Force))
$cred | Export-Clixml ./cred.xml
Select-String -Path ./cred.xml -Pattern 'Password'
cred.xml:10:      <SS N="Password">4e006f00740041005200650061006c00500061007300
730077006f00720064003100</SS>
$xml   = [xml](Get-Content ./cred.xml -Raw)
$hex   = $xml.Objs.Obj.Props.SS.'#text'
[System.Text.Encoding]::Unicode.GetString([Convert]::FromHexString($hex))
NotARealPassword1

That "secure string" is the password in UTF-16, hex-encoded. Anybody who can read the file can read the password. On Linux and macOS, use a real secret store (the Microsoft.PowerShell.SecretManagement module, your CI system's secrets, or environment variables injected at runtime) instead.

Choosing a format

FormatUse it forWatch out for
CSVFlat tables, anything a person opens in Excel, HR and finance exportsEverything is a string; delimiter depends on locale; no nesting
JSONAPIs, app configuration, nested data, output of terraform, kubectl, az, gh-Depth truncation; comments discarded; dates auto-converted in 7
XML.NET config files, Maven and MSBuild projects, feeds, older enterprise systemsNamespaces break XPath; .Save() relative paths
CliXmlYour own scripts' state and caches; credentials on WindowsOnly PowerShell reads it; methods are gone; not encrypted on Linux/macOS

When the consumer is a person, use CSV. When it's another program, use JSON. Use XML when the file format is already decided for you, and CliXml when the only reader is PowerShell.

Where you'll use this

Format conversion is the glue in most admin automation: HR exports driving account provisioning, inventory spreadsheets feeding monitoring, config files updated per environment in a release pipeline, and the JSON output of terraform output -json, kubectl get -o json or az piped straight into ConvertFrom-Json.

Here's the Monday-morning request from the start of the article as a complete script. It imports the CSV, fixes types once, then writes a CSV for the humans and a JSON summary for a dashboard. -AsOf defaults to today but is a parameter so the output is reproducible and testable (the functions article covers param() in depth).

# Get-AccountReport.ps1
[CmdletBinding()]
param(
    [Parameter(Mandatory)]
    [string]$Path,
 
    [datetime]$AsOf = (Get-Date),
 
    [int]$StaleDays = 60,
 
    [string]$OutDir = '.'
)
 
# 1. Import and fix the types once, at the edge.
$users = Import-Csv -Path $Path | ForEach-Object {
    [pscustomobject]@{
        User       = $_.SamAccountName
        Name       = $_.DisplayName
        Department = $_.Department
        LastLogon  = [datetime]$_.LastLogon
        MailboxMB  = [int]$_.MailboxMB
        Enabled    = $_.Enabled -eq 'True'
        DaysIdle   = ($AsOf - [datetime]$_.LastLogon).Days
    }
}
 
# 2. Work with real types: dates subtract, numbers sum, booleans filter.
$stale = @($users | Where-Object { $_.Enabled -and $_.DaysIdle -gt $StaleDays })
 
$byDepartment = $users | Group-Object Department | ForEach-Object {
    [pscustomobject]@{
        Department = $_.Name
        Users      = $_.Count
        Stale      = @($_.Group | Where-Object User -in $stale.User).Count
        MailboxGB  = [math]::Round(($_.Group | Measure-Object MailboxMB -Sum).Sum / 1024, 1)
    }
} | Sort-Object MailboxGB -Descending
 
# 3. CSV for the people who will open it in Excel...
$staleCsv = Join-Path $OutDir 'stale-accounts.csv'
$stale |
    Select-Object User, Name, Department, DaysIdle,
        @{ Name = 'LastLogon'; Expression = { $_.LastLogon.ToString('yyyy-MM-dd') } } |
    Export-Csv -Path $staleCsv -NoTypeInformation
 
# 4. ...and JSON for the dashboard that polls it.
$summaryJson = Join-Path $OutDir 'account-summary.json'
[pscustomobject]@{
    generatedAt = $AsOf.ToString('yyyy-MM-dd')
    totalUsers  = $users.Count
    staleUsers  = $stale.Count
    departments = $byDepartment
} | ConvertTo-Json -Depth 5 | Set-Content -Path $summaryJson
 
$byDepartment | Format-Table
Write-Host "Wrote $staleCsv and $summaryJson"
./Get-AccountReport.ps1 -Path ./users.csv -AsOf 2026-11-09 -OutDir ./out
Get-Content ./out/stale-accounts.csv
Get-Content ./out/account-summary.json
Department  Users Stale MailboxGB
----------  ----- ----- ---------
Engineering     3     0     16.60
Sales           2     1      8.20
Finance         2     1      3.00
Support         1     0      0.30
 
Wrote ./out/stale-accounts.csv and ./out/account-summary.json
"User","Name","Department","DaysIdle","LastLogon"
"bjones","Jones, Bob","Finance","87","2026-08-14"
"dpatel","Patel, Dev","Sales","163","2026-05-30"
{
  "generatedAt": "2026-11-09",
  "totalUsers": 8,
  "staleUsers": 2,
  "departments": [
    {
      "Department": "Engineering",
[…]

A few choices in there are deliberate. @(...) around $stale guarantees .Count works even when exactly one account matches. -NoTypeInformation keeps the CSV clean if someone runs the script on Windows PowerShell 5.1. -Depth 5 covers the nested departments array. And the dates in the CSV are formatted explicitly, so Excel in any locale reads them the same way. To run this every Monday, see real-world PowerShell scripts for scheduling; to pull the data from an API instead of a file, see calling REST APIs.

Common mistakes

Piping Format-Table into Export-Csv

Import-Csv ./users.csv | Format-Table SamAccountName, Department | Export-Csv ./broken.csv
Get-Content ./broken.csv -TotalCount 3
"ClassId2e4f51ef21dd47e99d3c952918aff9cd","pageHeaderEntry","pageFooterEntry","autosizeInfo","shapeInfo","groupingEntry"
"033ecb2bc07a4d43b5ef94ed5a35d280",,,,"Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo",
"9e210fe47d09416682b841769c78b8a3",,,,,

Format-* cmdlets turn objects into formatting instructions for the console, and those instructions are what got exported. Use Select-Object to pick columns, and keep Format-* for the very end of a pipeline whose output goes to the screen.

Appending rows with different columns

[pscustomobject]@{ User = 'asmith'; Action = 'Disabled' } | Export-Csv ./audit.csv
[pscustomobject]@{ User = 'bjones'; Action = 'Disabled'; Ticket = 'CHG-1042' } | Export-Csv ./audit.csv -Append
Get-Content ./audit.csv
[pscustomobject]@{ User = 'cnguyen'; Ticket = 'CHG-1043' } | Export-Csv ./audit.csv -Append
Get-Content ./audit.csv
"User","Action"
"asmith","Disabled"
"bjones","Disabled"
Export-Csv:
Line |
   4 |  … r = 'cnguyen'; Ticket = 'CHG-1043' } | Export-Csv ./audit.csv -Append
     |                                           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Cannot append CSV content to the following file: ./audit.csv. The appended object does not have a property that corresponds to the following column: Action. To continue with mismatched properties, add the -Force parameter, and then retry the command.
"User","Action"
"asmith","Disabled"
"bjones","Disabled"

Look at the second row. The Ticket value was silently dropped, because -Append only writes the columns already in the file's header. A missing column is an error; an extra one is not. For a log you append to over months, decide the full set of columns up front and build every row with all of them (use $null for blanks).

Saving XML to a relative path

New-Item -ItemType Directory ./reports -Force | Out-Null
Set-Location ./reports
[xml]$doc = '<report generated="2026-11-09" />'
$doc.Save('summary.xml')
Test-Path ./summary.xml
[Environment]::CurrentDirectory -eq (Split-Path $PWD)
Test-Path ../summary.xml
Remove-Item ../summary.xml
False
True
True

$doc.Save() is a .NET method, and .NET resolves relative paths against the process working directory, which Set-Location doesn't change. The file landed in the parent folder, with no error. The same applies to [System.IO.File]::WriteAllText() and every other .NET call that takes a path. Always pass a full path: Join-Path $PWD 'file.xml', or (Resolve-Path ./file.xml).Path for a file that already exists.

Summary

  • Import-Csv gives you objects whose properties are all strings: sorting, -ge and -not give wrong answers until you convert. Build typed [pscustomobject]s once, right after import.
  • European CSVs often use ;. A single property named after the whole header line means the delimiter is wrong; use -Delimiter (portable) or -UseCulture (local Excel files).
  • On Windows PowerShell 5.1, always pass -NoTypeInformation to Export-Csv and -Encoding UTF8 when writing data files. Never pipe Format-Table into an export.
  • Read JSON with Get-Content -Raw | ConvertFrom-Json; write it with ConvertTo-Json -Depth 10. The default depth of 2 silently flattens nested data (7 warns, 5.1 doesn't).
  • PowerShell 7 converts ISO date strings to DateTime on read (-DateKind String keeps them), supports -AsHashtable for case-variant keys and easy edits, and -AsArray for one-element arrays.
  • [xml] gives dot-notation access; use SelectNodes/Select-Xml with XPath for queries, -Namespace for documents with a default namespace, and a full path for .Save().
  • Export-Clixml round-trips types (not methods) for PowerShell-only state. Its credential encryption exists only on Windows.

Exercises

Exercise 1: Find the disabled accountseasy

Using users.csv from this article, list the SamAccountName, Department and LastLogon of every disabled account. Your filter must not depend on -not.

Hint

What type is Enabled after Import-Csv, and what is [bool]'False'?

Show Solution
Import-Csv ./users.csv |
    Where-Object Enabled -eq 'False' |
    Select-Object SamAccountName, Department, LastLogon
SamAccountName Department  LastLogon
-------------- ----------  ---------
gkim           Engineering 2026-07-21

Enabled is the string "False", which is truthy, so -not $_.Enabled matches nothing. Comparing to the string 'False' (case-insensitively, since -eq ignores case) works.

Exercise 2: CSV to typed JSONmedium

Convert users.csv to users.json: an array of objects with lowercase keys user, department, mailboxMB (a JSON number) and enabled (a JSON boolean). Then read the file back and prove the types survived by summing mailboxMB.

Show Solution
Import-Csv ./users.csv | ForEach-Object {
    [pscustomobject]@{
        user       = $_.SamAccountName
        department = $_.Department
        mailboxMB  = [int]$_.MailboxMB
        enabled    = $_.Enabled -eq 'True'
    }
} | ConvertTo-Json -AsArray | Set-Content ./users.json
 
$check = Get-Content ./users.json -Raw | ConvertFrom-Json
$check.Count
$check[0] | ConvertTo-Json -Compress
($check | Measure-Object mailboxMB -Sum).Sum
8
{"user":"asmith","department":"Engineering","mailboxMB":3420,"enabled":true}
28860

3420 and true have no quotes, so they are a number and a boolean in JSON. -AsArray guarantees the file is a JSON array even if the CSV only had one row. On 5.1, use ConvertTo-Json -InputObject @(...) instead, since -AsArray doesn't exist there.

Exercise 3: Bump a Maven versionmedium

Write a script that reads pom.xml, increments the patch number of the project version (2.3.1 becomes 2.3.2), saves the file in place and prints old -> new. It must work no matter which directory Set-Location last pointed at.

Hint

[version]'2.3.1' has Major, Minor and Build properties. Dot notation ignores the namespace, and .Save() needs a full path.

Show Solution
$path = Join-Path $PWD 'pom.xml'
[xml]$pom = Get-Content $path -Raw
$old = [version]$pom.project.version
$new = [version]::new($old.Major, $old.Minor, $old.Build + 1)
$pom.project.version = $new.ToString()
$pom.Save($path)
"$old -> $new"
Select-String -Path $path -Pattern '<version>' | Select-Object -First 1 LineNumber, Line
2.3.1 -> 2.3.2
 
LineNumber Line
---------- ----
         6   <version>2.3.2</version>

Only the project's own <version> (line 6) changed; the dependency versions further down were untouched, because $pom.project.version addresses exactly one element. Casting to [version] gives numeric parts, so 2.3.9 becomes 2.3.10 rather than a string mess.

Exercise 4: Merge XML inventory with JSON health datahard

Combine the <servers> list from the original app.config with this health.json from a monitoring system:

[
  { "server": "web01", "status": "ok", "latencyMs": 42 },
  { "server": "db01", "status": "degraded", "latencyMs": 870 }
]

Produce server-health.csv with columns Server, Role, IP, Status, LatencyMs. Servers with no health record must appear with the status missing.

Hint

Build a hashtable keyed by server name from the JSON first, then loop over the XML servers and look each one up.

Show Solution
[xml]$cfg = Get-Content ./app.config -Raw
$health = Get-Content ./health.json -Raw | ConvertFrom-Json
 
# Index the health records by server name for O(1) lookups.
$byName = @{}
foreach ($h in $health) { $byName[$h.server] = $h }
 
$report = foreach ($s in $cfg.configuration.servers.server) {
    $h = $byName[$s.name]
    [pscustomobject]@{
        Server    = $s.name
        Role      = $s.role
        IP        = $s.ip
        Status    = if ($h) { $h.status } else { 'missing' }
        LatencyMs = if ($h) { $h.latencyMs } else { $null }
    }
}
$report | Export-Csv ./server-health.csv -NoTypeInformation
Get-Content ./server-health.csv
"Server","Role","IP","Status","LatencyMs"
"web01","web","10.0.1.11","ok","42"
"web02","web","10.0.1.12","missing",
"db01","database","10.0.2.21","degraded","870"

Iterating over the XML (the source of truth for what should exist) rather than over the JSON is what makes web02 show up as missing. The hashtable avoids a Where-Object scan of the health data for every server, which matters when the inventory has thousands of entries.


Tested with: PowerShell 7.6.9 on Ubuntu 24.04 (WSL2) and Windows PowerShell 5.1.26100 on Windows 11, November 2026. Every PowerShell 7 example ran as shown in a scratch directory with the sample files above; the lab uses the invariant culture, which is why dates display as 07/21/2026 00:00:00, and the -UseCulture example ran with LANG=de_DE.UTF-8. The 5.1 outputs (the #TYPE line, silent -Depth truncation, the \/Date()\/ format, file encodings and the DPAPI credential prefix) came from a clean powershell.exe -NoProfile session in a temporary folder. The biggest surprise was Export-Csv -Append silently dropping an extra column while erroring on a missing one.

Was this page helpful?
The Dark Debugger

Written by The Dark Debugger

Engineer documenting the things that took me too long to learn, so they take you less time. Every PowerShell tutorial here is tested end to end before publishing.