PowerShell Lesson 5: Working With CSV, JSON and Excel Files

PowerShell Lesson 5: Working With CSV, JSON and Excel Files featured illustration

📎 This article includes 3 downloadable practice files ↓

⏱ 2 min readUpdated 11 November 2026

📘 PowerShell Beginner Course · Lesson 5 of 6

In this article
  1. Basic Level: Importing and Exporting CSV Data
  2. Intermediate Level: Consuming REST APIs and Parsing JSON
  3. Expert Level: Creating Real Excel Workbooks via ImportExcel Module
  4. Summary of Key Data Formats and Cmdlets
  5. Continue learning

Every business runs on tabular data. In modern enterprise IT and finance environments, automated reconciliation between CSV bank feeds, JSON API payloads, and Excel workbooks is a daily requirement. PowerShell makes importing, transforming, and exporting these file formats virtually effortless.

Advertisement

Basic Level: Importing and Exporting CSV Data

The cmdlets Import-Csv and Export-Csv automatically parse headers into object properties without requiring any manual delimiter manipulation.

# 1. Import a CSV file into PowerShell objects
 = Import-Csv -Path "C:FinanceNovember_Invoices.csv"

# 2. Filter rows where Amount is greater than 50000
 =  | Where-Object { [double].Amount -gt 50000 }

# 3. Export back to a clean CSV without legacy type headers
 | Export-Csv -Path "C:FinanceHigh_Value_Audit.csv" -NoTypeInformation -Encoding UTF8

Intermediate Level: Consuming REST APIs and Parsing JSON

PowerShell provides seamless conversion between JSON strings and native nested custom objects using Invoke-RestMethod and ConvertFrom-Json / ConvertTo-Json:

Advertisement
# Fetch live currency exchange rates from a public API
 = Invoke-RestMethod -Uri "https://api.exchangerate-api.com/v4/latest/USD"
 = .rates.INR
Write-Host "Current USD to INR Rate: ₹" -ForegroundColor Yellow

# Exporting complex structured data to JSON
 = [PSCustomObject]@{
    GeneratedAt = (Get-Date).ToString("yyyy-MM-dd HH:mm:ss")
    Environment = "Production"
    TotalFiles  = 1450
    Status      = "Completed"
}
 | ConvertTo-Json -Depth 3 | Set-Content "C:LogsAudit.json"

Expert Level: Creating Real Excel Workbooks via ImportExcel Module

Historically, generating Excel files from PowerShell required launching slow, invisible instances of Excel.Application COM objects, which frequently hung on servers. The open-source PowerShell module ImportExcel creates, formats, and reads real .xlsx files directly via .NET OpenXML without needing Microsoft Excel installed on the computer!

# Install module once (if not already present)
# Install-Module ImportExcel -Scope CurrentUser -Force

# Export data directly to formatted Excel with Pivot Tables & Auto-filter
Get-Process | Select-Object -First 20 Name, WorkingSet, CPU |
    Export-Excel -Path "C:ReportsServerLoad.xlsx" `
                 -WorksheetName "ProcessSummary" `
                 -BoldTopRow `
                 -AutoSize `
                 -AutoFilter `
                 -Show

Summary of Key Data Formats and Cmdlets

Format Import Cmdlet Export Cmdlet Key Flag
CSV Import-Csv Export-Csv -NoTypeInformation
JSON ConvertFrom-Json ConvertTo-Json -Depth 5
Excel (.xlsx) Import-Excel Export-Excel -WorksheetName, -AutoSize
XML [xml](Get-Content) Export-Clixml -Depth

📎 Practice files for this article

⬇ Download all 3 files (ZIP · 1 KB)

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong

Leave a Comment