Exporting data with PowerShell Export-Csv is one of those tasks that looks trivial until you actually hit Excel. Wrong encoding, mangled non-ASCII characters, columns full of System.Object[], calculated properties that disappear — the cmdlet is simple, but the surrounding behaviour is full of small traps. This post collects the patterns we keep coming back to when we need a clean CSV out of a PowerShell pipeline.
Key Takeaways
Export-Csvalways pairs with-NoTypeInformationon Windows PowerShell 5.1 — the type header it omits is otherwise the first thing that confuses Excel.- Use
-Encoding UTF8BOM(PowerShell 7) orUTF8(5.1) when the data contains non-ASCII characters, or Excel will render them as garbage. - Filter and project the data before piping into
Export-Csv; the cmdlet writes every property it sees, including the noisy ones. - Properties that hold arrays or nested objects export as type names. Flatten them with
-joinor calculated properties first. - For long-running collections, prefer
-Appendover building one huge in-memory array.
Environment
Examples were tested on both supported PowerShell editions. Behaviour for encoding flags and default delimiters differs between them, so it matters which you are running:
- Windows PowerShell 5.1 on Windows 10/11 — ships in box, default encoding is ASCII unless overridden.
- PowerShell 7.4 LTS on Windows and Linux — default encoding is UTF-8 without BOM.
- Excel for Microsoft 365 (current channel) is the consumer for most of these CSVs; Excel cares deeply about BOMs and locale-specific delimiters.
The Problem
The naive call Get-Process | Export-Csv processes.csv works. It produces a file. Opening it in Excel, however, gives you a useful column called #TYPE System.Diagnostics.Process, three columns of meaningless WMI internals, a column called Modules that contains the literal text System.Diagnostics.ProcessModule[], and — if you happen to be in a German, Dutch, or French Windows locale — every column is glued together because Excel expected semicolons and PowerShell wrote commas.
Most of these are one-flag fixes once you know which flag. The rest are pipeline shape issues that have to be solved before the data ever reaches Export-Csv.
The Solution
Step 1 — Strip the type header and project only the columns you want
Always pass -NoTypeInformation on Windows PowerShell 5.1. On PowerShell 7 the flag is the default and the parameter is a no-op kept for compatibility, but writing it explicitly keeps scripts portable.
Get-Process |
Select-Object Name, Id, CPU, @{Name='WorkingSetMB';Expression={[math]::Round($_.WorkingSet64/1MB, 2)}} |
Export-Csv -Path .\processes.csv -NoTypeInformationThe calculated property here does two useful things at once: it gives the column a clean name, and it converts the raw byte count into megabytes rounded to two decimals. Doing the same work in Excel afterwards is painful; doing it in the pipeline is one line.
Step 2 — Pick the right encoding for Excel
Excel does not auto-detect UTF-8 when a CSV lacks a BOM. If the data contains any non-ASCII character — an accented user name, a Cyrillic group, a German umlaut, an em dash — and you do not write a BOM, Excel will mojibake the cells:
# PowerShell 7+: write UTF-8 with byte-order mark for Excel
Get-LocalUser |
Select-Object Name, Enabled, LastLogon |
Export-Csv -Path .\users.csv -NoTypeInformation -Encoding UTF8BOM
# Windows PowerShell 5.1: UTF8 already means UTF-8 with BOM
Get-LocalUser |
Select-Object Name, Enabled, LastLogon |
Export-Csv -Path .\users.csv -NoTypeInformation -Encoding UTF8
In PowerShell 7 the value UTF8 writes without a BOM, which is the opposite of 5.1. If you are sharing scripts between versions, use UTF8BOM on 7 and UTF8 on 5.1 — or wrap the call so the script picks the right one based on $PSVersionTable.PSEdition.
Step 3 — Match the delimiter to the consuming locale
Excel's "Text to Columns" silently picks its delimiter from Windows regional settings. In English-speaking locales that is a comma, which matches CSV. In most of continental Europe it is a semicolon, which does not, and every row lands in column A. Set the delimiter to whatever the target machine expects:
# Force semicolon for German/Dutch/French Excel
Get-Service |
Where-Object Status -eq 'Running' |
Select-Object Name, DisplayName, StartType |
Export-Csv -Path .\services.csv -NoTypeInformation -Delimiter ';' -Encoding UTF8BOM
# Or use the culture-appropriate one automatically
$listSep = (Get-Culture).TextInfo.ListSeparator
Get-Service | Export-Csv -Path .\services.csv -NoTypeInformation -Delimiter $listSep
The Get-Culture approach pulls the same separator Excel will, which is the safest choice when scripts move between machines.
Step 4 — Flatten properties that contain collections
PowerShell objects often hold sub-objects or arrays. Export-Csv resolves those to their type name — Microsoft.ActiveDirectory.Management.ADPropertyValueCollection is not a useful cell value. Convert collections to a delimiter-joined string in a calculated property:
Get-LocalGroup |
Select-Object Name,
@{Name='Members';Expression={
(Get-LocalGroupMember -Group $_.Name -ErrorAction SilentlyContinue |
Select-Object -ExpandProperty Name) -join '; '
}} |
Export-Csv -Path .\local_groups.csv -NoTypeInformation -Encoding UTF8BOM
Joining on '; ' rather than the row delimiter keeps the cell readable in Excel without breaking the CSV structure. If a cell may itself contain the join delimiter, switch to a pipe ('|') or rethink the schema.
Step 5 — Append instead of buffering for long-running collectors
Scripts that watch event logs, poll endpoints, or walk an Active Directory forest can produce more rows than fit comfortably in memory. -Append writes one row at a time, reusing the header from the first call:
foreach ($computer in $allComputers) {
Get-CimInstance Win32_OperatingSystem -ComputerName $computer -ErrorAction SilentlyContinue |
Select-Object PSComputerName, Caption, Version, LastBootUpTime |
Export-Csv -Path .\os_inventory.csv -NoTypeInformation -Append -Encoding UTF8BOM
}
-Append only checks that the file exists; it does not verify that the schema matches. If a later object has different properties, the new columns silently align by position, not by name. Project to a fixed set of columns with Select-Object on every iteration so the schema is stable.
Step 6 — Round-trip with Import-Csv when you need to keep working
A CSV exported with the above flags imports cleanly back into PowerShell with Import-Csv. Everything comes back as a string — there is no type metadata in CSV — so cast as needed:
$processes = Import-Csv .\processes.csv -Encoding UTF8BOM
$processes |
Where-Object { [int]$_.Id -gt 1000 } |
Sort-Object { [double]$_.WorkingSetMB } -Descending
For one-off transformations that never touch disk, skip the file entirely with ConvertTo-Csv and ConvertFrom-Csv. Same flags, same gotchas, no I/O.
Frequently Asked Questions
Why does Excel show System.Object[] in my column?
The property contained an array, and Export-Csv rendered it as the type name rather than the contents. Flatten the property with -join inside a calculated property before exporting (see Step 4).
What is the difference between UTF8 and UTF8BOM in PowerShell?
In Windows PowerShell 5.1, UTF8 writes a BOM. In PowerShell 7, UTF8 writes without a BOM and UTF8BOM is the variant Excel needs. The defaults differ, so the same script can produce different files on the two editions.
How do I export to a semicolon-delimited CSV without hardcoding the delimiter?
Use (Get-Culture).TextInfo.ListSeparator as the value for -Delimiter. That gives you the same separator Excel will expect on the same machine, regardless of regional settings.
Can Export-Csv overwrite a file that is open in Excel?
No. Excel opens CSV files with a write lock. Export-Csv throws The process cannot access the file because it is being used by another process. Either close Excel or write to a new file name and swap.
Is there a faster alternative for very large datasets?
For millions of rows, the per-object overhead of Export-Csv becomes the bottleneck. Use StreamWriter directly or pipe through ConvertTo-Csv once and write the resulting strings. For tabular SQL-style work, exporting to Parquet via a dedicated module is usually a better fit than CSV.
Conclusion
Export-Csv is one of the most-used cmdlets in any administrative script, and the boring details — encoding, delimiter, schema stability — are what separate a CSV that opens cleanly in Excel from one that needs a follow-up cleanup pass. None of this is rocket science, but it is the kind of thing that bites once per locale change and once per PowerShell version bump.
Most of the time, the recipe is: project with Select-Object, encode with UTF8BOM, set the delimiter from the culture, flatten arrays in calculated properties, and append for long runs. If that pattern is in your fingers, you will spend approximately zero further minutes fighting Excel.
Related Posts
- PowerShell Quick Guide: Creating Your First Security Audit Script — a longer audit pipeline that ends in
Export-Csv. - PowerShell Quick Guide: Working with Event Logs Like a Pro — produces the kind of large, structured datasets you usually want to ship to CSV.
- PowerShell Quick Guide: Process Investigation — most of the worked examples here pipe straight into
Export-Csvfor triage.
Microsoft's authoritative reference for the cmdlet lives at Export-Csv on Microsoft Learn.
Editorial note: posts on this blog are drafted with AI assistance and then reviewed, edited, and tested against a real environment before publishing. Commands, output, and screenshots come from systems I actually ran the work on.
0 comments:
Post a Comment