Learn PowerShell - Working with CSV & Structured Data

#Introduction

In episode 9 you read and wrote plain text files. But real-world data is rarely in the form of random text — it comes as CSV, JSON, and XML, structured formats understood by other systems: spreadsheets, APIs, applications, and databases. Episode 10 teaches this intermediary language.

Imagine CSV like a spreadsheet table, JSON like a structured ID card sent by an API, and XML like a labeled document. All three have their own conventions, and PowerShell treats them natively — not by manually parsing text.

#PSCustomObject: Hand-Made Objects

Before storing data, you need to create objects yourself. PSCustomObject is the building block of structured data — a single statement that creates an object with properties of your choosing:

$server = [PSCustomObject]@{
    Name    = "web-01"
    Status  = "Running"
    Cores   = 4
}
$server.Name
$server | Select-Object Name, Status, Cores

This object behaves exactly like cmdlet output: it can be filtered with Where-Object, sorted, and exported. This is the raw material for all the formats in this episode — many structured-file cmdlets accept arrays of objects like this.

#CSV: Import-Csv and Export-Csv

CSV stores tabular data — rows and columns. Export-Csv writes objects to CSV, Import-Csv reads them back:

Get-Process pwsh | Select-Object ProcessName, Id, WS | Export-Csv /tmp/proses.csv
Import-Csv /tmp/proses.csv | Select-Object -First 3

Note: Export-Csv writes text, so the stored data is strings. After importing, numbers can be converted back to their original types — often via ForEach-Object or at the point of use.

Column headers are stored in the first row. When a file has no headers (raw from another system), tell Import-Csv with -Header:

Import-Csv /tmp/data-raw.csv -Header "nama", "usia", "kota"

The default delimiter is a comma, but many systems use semicolons or tabs. Specify with -Delimiter:

Import-Csv /tmp/data.tsv -Delimiter "`t"
Import-Csv /tmp/data-semi.csv -Delimiter ";"

ConvertTo-Csv and ConvertFrom-Csv work on strings in memory, not files — useful when the data is in a variable rather than on disk:

$teks = Get-Process pwsh | ConvertTo-Csv
ConvertFrom-Csv $teks | Select-Object -First 2

#JSON: ConvertFrom-Json and ConvertTo-Json

JSON is the favorite format of modern APIs — concise, structured, and understood by almost every language. Its two-way conversion is simple:

$data = [PSCustomObject]@{ Nama = "Arman"; Usia = 30; Skills = @("DevOps", "Cloud") }
$json = $data | ConvertTo-Json
$json
 
$kembali = $json | ConvertFrom-Json
$kembali.Nama
$kembali.Skills

Consuming an API response is just two steps — fetch, then convert to objects:

$res = Invoke-RestMethod "https://api.example.com/users?limit=3"
$res | ForEach-Object { Write-Host "$($_.name) - $($_.email)" }

Invoke-RestMethod automatically parses JSON into objects. This is one reason API integration scripts in PowerShell feel so smooth.

ConvertTo-Json has a -Depth parameter worth remembering: the default value is 2, meaning objects nested deeper than two levels get truncated:

$kompleks = [PSCustomObject]@{ L1 = @{ L2 = @{ L3 = "dalam" } } }
$kompleks | ConvertTo-Json
$kompleks | ConvertTo-Json -Depth 4

Without sufficient -Depth, the deepest structures silently disappear — one of the most common lurking JSON bugs.

#XML: [xml], Select-Xml, and XPath

XML stores data as labeled documents. Casting to the [xml] type parses a string or file into a navigable structure:

[xml]$doc = Get-Content /tmp/data.xml
$doc.books.book

The code above accesses the <books> element and then all <book> elements inside it — navigation like properties. For XPath-based searches, use Select-Xml:

[xml]$doc = Get-Content /tmp/data.xml
Select-Xml -Xml $doc -XPath "//book[@kategori='devops']" |
    ForEach-Object { $_.Node.title }

XPath is the language for pointing at elements: //book takes all book elements at any depth, and attributes are filtered with square brackets. Creating or modifying XML is done by manipulating nodes — adding child elements, changing text, then saving with Save:

$doc = [xml]"<books></books>"
$book = $doc.CreateElement("book")
$book.SetAttribute("kategori", "devops")
$title = $doc.CreateElement("title")
$title.InnerText = "Belajar PowerShell"
$book.AppendChild($title) | Out-Null
$doc.DocumentElement.AppendChild($book) | Out-Null
$doc.Save("/tmp/buku.xml")

CreateElement, SetAttribute, AppendChild are .NET APIs neatly wrapped by the [xml] type. Confirm the result by reading $doc again or viewing the saved file.

#Excel: COM and the ImportExcel Module

Two routes to Excel:

• COM — automates a truly installed Excel application, via the New-Object -ComObject Excel.Application object.
• The ImportExcel module — works with .xlsx files without Excel installed, purely from PowerShell. Much lighter and suited to servers.

Install-Module ImportExcel -Scope CurrentUser

$data = Get-Process pwsh | Select-Object ProcessName, Id, WS
$data | Export-Excel /tmp/proses.xlsx -WorksheetName "Proses" -AutoSize
$data | Import-Excel /tmp/proses.xlsx

Export-Excel and Import-Excel treat Excel files like ordinary tables — exporting objects to a worksheet, importing them back as objects. For reports that must be sent to others (who may not have PowerShell), this is the most practical route.

#Practical Exercise

A small script that takes service status, saving it to both JSON and CSV:

$status = Get-Service | Select-Object Name, Status, StartType
 
$status | Export-Csv /tmp/status-service.csv -NoTypeInformation
$status | ConvertTo-Json -Depth 3 | Set-Content /tmp/status-service.json
 
$kembali = Import-Csv /tmp/status-service.csv
Write-Host "Total service tercatat: $($kembali.Count)"

-NoTypeInformation removes the type metadata line stuck at the top of the CSV — so the file is clean for other systems to read.

#Conclusion

Episode 10 opens data exchange with the outside world: PSCustomObject as the raw material for hand-made objects; CSV with Import-Csv, Export-Csv, -Delimiter, -Header, and the in-memory ConvertTo-Csv/ConvertFrom-Csv pair; JSON with ConvertTo-Json, ConvertFrom-Json, and the -Depth parameter for API responses; XML with [xml], Select-Xml, and XPath for navigating and modifying documents; and Excel via COM and the ImportExcel module without needing Excel installed.

Key takeaways:

• Choose the format that fits the data consumer: JSON for APIs, CSV for tables, XML for documents.
• Invoke-RestMethod automatically parses JSON.
• Default -Depth of 2 can truncate nested JSON — increase it when needed.
• ImportExcel enables .xlsx files without Excel installed.
• Always provide clear headers; -NoTypeInformation keeps CSV clean.

Structured data handles data that's orderly — but the real world is full of disorderly text: logs, messages, and user input. In episode 11 we call the strongest text filtering weapon: regular expressions — patterns that recognize, validate, and extract from free text. See you in episode 11!