
📎 This article includes 3 downloadable practice files ↓
In this article
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.
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:
# 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)
- 🖥️ps-05-csv-excel.ps1Lesson code file.⬇ PS1 · 546 B
- 🧾sales.csv40 sample orders used by the scripts.⬇ CSV · 1 KB
- 📄ps-05-csv-excel-output.txtOutput from our test run.⬇ TXT · 187 B
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.
Stuck on a step? Ask a question and the AI answers using this article.