Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 34 ยท Files, Data and Errors

CSV, JSON and XML

Read and write structured data in PowerShell with Import-Csv, Export-Csv, ConvertFrom-Json, ConvertTo-Json -Depth and Select-Xml, and avoid the common traps.

Text files become far more useful when PowerShell turns them into objects. A CSV row becomes an object with one property per column. A JSON document becomes nested objects and arrays. An XML file becomes a tree you walk with dots. Once the data is objects, everything from the pipeline works on it: filter, sort, group, export again.

This lesson covers the cmdlets for the three formats and the surprises that catch beginners: CSV values are always strings, ConvertTo-Json stops at a default depth, and XML needs a cast. The real cmdlets are shown in read-only blocks because the site has no PowerShell runtime. The runnable examples use Python's csv, json and xml modules as stand-ins: they parse the same formats and let you watch the same effects, but they are not PowerShell and their API differs.

Three formats, three entry points

CSV Import-Csv and Export-Csv.
JSON ConvertFrom-Json and ConvertTo-Json.
XML Cast with [xml] or use Select-Xml.

Text goes in, objects come out.

1 / 2

CSV: Import-Csv and Export-Csv

Suppose staff.csv looks like this:

TEXT
name,team,salary,start
Ada,Platform,7200,2021-03-01
Grace,Data,8100,2019-11-15
Linus,Platform,6900,2023-06-12

Import-Csv reads the header row as property names and returns one object per data row:

POWERSHELL
$staff = Import-Csv .\staff.csv
$staff[0].name                    # Ada
$staff | Where-Object team -eq 'Platform'
$staff | Sort-Object { [int]$_.salary } -Descending

The important point is that every property is a string. $staff[0].salary is the text 7200, so Sort-Object salary compares text character by character and 900 sorts after 8100 in an ascending sort. Convert explicitly with [int], [decimal] or [datetime], or build typed objects with a calculated property.

Useful parameters: -Delimiter ';' for semicolon files common in some locales, -Header 'a','b','c' for files with no header row, and -Encoding when accented characters look wrong.

POWERSHELL
$staff |
  Select-Object name, team, @{ n = 'salary'; e = { [decimal]$_.salary } } |
  Export-Csv .\staff-typed.csv -NoTypeInformation

Export-Csv writes each property of the objects it receives. Windows PowerShell 5.1 adds a first line starting with #TYPE unless you pass -NoTypeInformation; PowerShell 7 does not, and still accepts the parameter. Quoting is applied for you, so values containing commas are safe.

Here is the same idea in Python: reading with a header, noticing that everything is text, and converting.

Python CSV stand-in: every cell starts as text

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

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

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

Look at the two sorted lists. Sorted as text (descending), Mina's 900 comes first, ahead of 8100, because the characters are compared one by one. Sorted as integers the order is correct. That is exactly the pitfall behind Sort-Object salary on imported CSV data.

JSON: ConvertFrom-Json and ConvertTo-Json

JSON is the common language of web APIs and configuration files.

POWERSHELL
$text = Get-Content .\config.json -Raw
$config = $text | ConvertFrom-Json
$config.server.port                         # property access, like objects
$config.features | ForEach-Object { $_ }

$config.server.port = 9090
$config | ConvertTo-Json -Depth 5 | Set-Content .\config.json

Use -Raw when reading, so the whole file is one string. The result is a PSCustomObject (nested ones for nested JSON), and arrays become arrays. Strings that look like dates may be converted to [datetime] values, so check types if dates matter. In PowerShell 6 and later, -AsHashtable returns hashtables instead, which helps with keys that differ only by case or are not valid identifiers.

The depth trap

ConvertTo-Json does not serialize unlimited levels. The default -Depth is 2, and anything nested deeper is flattened into a plain string instead of expanded. PowerShell 7 prints a warning about the truncation; Windows PowerShell 5.1 does not, so the data loss is silent there.

POWERSHELL
$order = @{ id = 1; customer = @{ name = 'Ada'; address = @{ city = 'Oslo'; geo = @{ lat = 59.9 } } } }
$order | ConvertTo-Json            # address.geo is flattened to a string
$order | ConvertTo-Json -Depth 5   # everything is kept

Whenever you write JSON that you will read again, pass a -Depth large enough for your data. -Compress removes whitespace for smaller output.

The stand-in below serializes a nested structure with a depth limit. The placeholder "..." stands for the flattened string PowerShell would write; the exact text PowerShell writes differs, and the model only demonstrates that information is lost.

Python JSON stand-in: a depth limit loses data

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

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

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

With depth 2 the geo object and the tags list collapse into "...". With depth 5 the full data survives, and the round trip is equal. In your scripts the lesson is the same: choose the depth deliberately and test a round trip on a realistic sample.

XML: casting and Select-Xml

Casting text to [xml] creates an XML document you navigate with dots: element names become properties, attributes too.

POWERSHELL
[xml]$doc = Get-Content .\inventory.xml -Raw
$doc.inventory.item                       # all <item> elements
$doc.inventory.item[0].name
$doc.inventory.item | Where-Object { [int]$_.qty -lt 5 }

Select-Xml -Path .\inventory.xml -XPath '//item[@category="tools"]' |
  ForEach-Object { $_.Node.name }

$doc.Save("$PWD\inventory-new.xml")       # use a full path with Save

Dot navigation is convenient for simple structures. Select-Xml runs an XPath query and returns objects whose .Node property holds the matching element; use it when you need conditions such as attributes or positions. As with CSV, values read from XML are strings unless you convert them. Passing a relative path to $doc.Save() can resolve against the process's working directory rather than your PowerShell location, so give it a full path.

The XML stand-in below parses a document with Python's ElementTree and runs XPath-like queries.

Python XML stand-in: elements, attributes and queries

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

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

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

Notice again that qty is text until converted. Python's ElementTree supports only a subset of XPath, while Select-Xml uses the full XPath 1.0 engine of .NET, so some queries that work in PowerShell need different code here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What type is $row.salary after Import-Csv reads a file with a salary column?

    Import-Csv does not guess types. Every property is a string, so cast with [int] or [decimal] before sorting or calculating.
  2. 2. Why can ConvertTo-Json silently lose data?

    The default depth is 2. Deeper levels become plain strings. Pass -Depth with a value large enough for your data.
  3. 3. Which pair reads a JSON file into objects correctly?

    -Raw gives one string, and ConvertFrom-Json turns it into PSCustomObject values. ConvertTo-Json goes the other direction.
  4. 4. What does Select-Xml return that you use to reach the matching element?

    Select-Xml returns SelectXmlInfo objects. The matching element is in .Node, so you write $_.Node.name inside ForEach-Object.

Summary

  • Import-Csv creates one object per row with string properties; convert types yourself and use -Delimiter, -Header or -Encoding as needed.
  • Export-Csv writes object properties as columns; use -NoTypeInformation in Windows PowerShell 5.1.
  • Read JSON with Get-Content -Raw | ConvertFrom-Json; write it with ConvertTo-Json -Depth N, because the default depth of 2 flattens deeper data.
  • Cast text to [xml] to navigate with dots, or use Select-Xml -XPath for queries; remember values are strings.
  • Python's csv, json and xml modules were stand-ins that show the same effects, not PowerShell itself.

Next, learn what to do when things go wrong in Errors, try/catch and ErrorAction.