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,TrueSplitting 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, EnabledSamAccountName 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 TrueThe 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 EngineerEvery 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, MailboxMBSamAccountName MailboxMB
-------------- ---------
bjones 890
fgarcia 7800
dpatel 640That'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, ShellUser 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, ShellUser 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
TrueGrace'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().FullNameUser MailboxMB
---- ---------
cnguyen 12050
fgarcia 7800
asmith 3420
User Department LastLogon
---- ---------- ---------
gkim Engineering 07/21/2026 00:00:00
System.DateTimeNumbers 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 like21/07/2026, use[datetime]::ParseExact($s, 'dd/MM/yyyy', [cultureinfo]::InvariantCulture)rather than relying on the current culture.$_.Enabled -eq 'True'is case-insensitive (-eqalways is for strings), so it also copes withTRUEandtruefrom 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,75Import 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-ListBenutzer;Abteilung;Postfach_MB;Kosten : asmith;Engineering;3420;12
Benutzer;Abteilung;Postfach_MB;Kosten : bjones;Finance;890;4Worse, 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-TableBenutzer 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.50PowerShell 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:
| Parameter | Use it when |
|---|---|
-Delimiter "`t" | Tab-separated files (TSV), common from databases and spreadsheets |
-Header A, B, C | The file has no header row (as in the /etc/passwd example above) |
-Encoding utf8 | Accented names come out garbled (Müller): tell it the file's real encoding |
ConvertFrom-Csv | The 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 3SamAccountName,Department
asmith,Engineering
bjones,FinanceArrays 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.pathNewCheckout BetaReports
----------- -----------
False True
Name Url TimeoutSec
---- --- ----------
payments https://payments.internal/api 30
inventory https://inventory.internal/api 15
Warning
/var/log/orders/app-.logJSON 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.0000000ZNumbers 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.DeployedAtString
2026-10-28T14:05:00ZThe -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-JsonWARNING: 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 = $falseSetValueInvocationException:
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-JsonConvertFrom-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
TrueIt'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 65Out-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, ipkey value
--- -----
Environment staging
MaxUploadMB 25
ReportsPath /srv/reports
name ip
---- --
web01 10.0.1.11
web02 10.0.1.12Child 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| XPath | Meaning |
|---|---|
//server | Every server element anywhere in the document |
/configuration/appSettings/add | An exact path from the root |
//add[@key='MaxUploadMB'] | Elements whose attribute equals a value |
//server[@role='web']/@ip | The 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.1Dot 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 PSCustomObjectCliXml 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))NotARealPassword1That "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
| Format | Use it for | Watch out for |
|---|---|---|
| CSV | Flat tables, anything a person opens in Excel, HR and finance exports | Everything is a string; delimiter depends on locale; no nesting |
| JSON | APIs, 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 systems | Namespaces break XPath; .Save() relative paths |
| CliXml | Your own scripts' state and caches; credentials on Windows | Only 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.jsonDepartment 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.xmlFalse
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-Csvgives you objects whose properties are all strings: sorting,-geand-notgive 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
-NoTypeInformationtoExport-Csvand-Encoding UTF8when writing data files. Never pipeFormat-Tableinto an export. - Read JSON with
Get-Content -Raw | ConvertFrom-Json; write it withConvertTo-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
DateTimeon read (-DateKind Stringkeeps them), supports-AsHashtablefor case-variant keys and easy edits, and-AsArrayfor one-element arrays. [xml]gives dot-notation access; useSelectNodes/Select-Xmlwith XPath for queries,-Namespacefor documents with a default namespace, and a full path for.Save().Export-Clixmlround-trips types (not methods) for PowerShell-only state. Its credential encryption exists only on Windows.
Exercises
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, LastLogonSamAccountName Department LastLogon
-------------- ---------- ---------
gkim Engineering 2026-07-21Enabled is the string "False", which is truthy, so -not $_.Enabled matches nothing.
Comparing to the string 'False' (case-insensitively, since -eq ignores case) works.
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).Sum8
{"user":"asmith","department":"Engineering","mailboxMB":3420,"enabled":true}
288603420 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.
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, Line2.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.
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.
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.