Saving Data: Export-Csv and Cleaning a CSV

Export-Csv saves objects as a CSV file that Excel and other programs open as a table, and on the way there a pipeline can clean an untidy export, dropping its blank rows, renaming its columns and turning text such as $36.00 into numbers with [decimal] and [int].

Journeyman · PowerShell · Operating System · Updated November 10, 2026

After this lesson you can

Save objects as a CSV file with Export-Csv, and open it in Excel
Clean a CSV export by turning text into numbers with [decimal] or [int], renaming columns and dropping blank rows

Key takeaways

[decimal]'36.00' turns text into a number that's right for money, and [int]'3' into a whole number. A $ sign has to come out first, with .Replace(' , '').
Where-Object Store -ne '' drops blank rows, and a calculated property renames a column: @{ Name = 'Bike'; Expression = { $_.'Bike Type' } }.
Export-Csv saves objects as a CSV file, replacing the file if it's already there, and Invoke-Item opens it in Excel.
A CSV file holds only text. Cleaned, its numbers read as numbers in Excel, and with [decimal] in PowerShell.

The bike shop's rental desk takes bookings in an app, and its October export doesn't match the shop's yearly workbook: one column is called "Bike Type" where the workbook says "Bike", every fee is written like "$36.00", and there are blank rows where someone deleted a booking. Every month somebody opens it in Excel, renames the heading, runs Find and Replace on the dollar signs, deletes the blank rows, and hopes nothing was missed. An office manager, a bookkeeper and a data analyst getting a file ready for a database all do this kind of cleanup. This lesson writes it once, as one pipeline, and saves the result with Export-Csv.

What's wrong with this export

Copy these lines into PowerShell to make the rentals export. Running them again is safe.

New-Item -ItemType Directory -Force -Path "$HOME\terminal-practice\bike-shop" | Out-Null
Set-Content -Path "$HOME\terminal-practice\bike-shop\rentals-2026-10.csv" -Value 'Rental Date,Store,Bike Type,Hours,Fee', '2026-10-02,Downtown,City Bike,3,$36.00', '2026-10-04,Riverside,E-Bike,2,$50.00', ',,,,', '2026-10-09,Downtown,E-Bike,4,$100.00', '2026-10-12,Riverside,City Bike,2,$24.00', '2026-10-17,Downtown,Kids Bike,1,$8.00', ',,,,', '2026-10-24,Riverside,E-Bike,6,$150.00', '2026-10-30,Downtown,City Bike,5,$60.00'

They print nothing when they work. Now look at it:

Set-Location "$HOME\terminal-practice\bike-shop"
Import-Csv rentals-2026-10.csv | Format-Table

Rental Date Store     Bike Type Hours Fee
----------- -----     --------- ----- ---
2026-10-02  Downtown  City Bike 3     $36.00
2026-10-04  Riverside E-Bike    2     $50.00

2026-10-09  Downtown  E-Bike    4     $100.00
2026-10-12  Riverside City Bike 2     $24.00
2026-10-17  Downtown  Kids Bike 1     $8.00

2026-10-24  Riverside E-Bike    6     $150.00
2026-10-30  Downtown  City Bike 5     $60.00

The blank rows are the lines ,,,, in the file: commas with nothing between them. And the dollar signs stop even Measure-Object from adding up the month's income:

(Import-Csv rentals-2026-10.csv | Measure-Object Fee -Sum).Sum

Measure-Object: Input object "$36.00" is not numeric.
Measure-Object: Input object "$50.00" is not numeric.
Measure-Object: Input object "$100.00" is not numeric.
Measure-Object: Input object "$24.00" is not numeric.
Measure-Object: Input object "$8.00" is not numeric.
Measure-Object: Input object "$150.00" is not numeric.
Measure-Object: Input object "$60.00" is not numeric.

Text into numbers: [decimal] and [int]

A price tag that says "$36.00" is a picture of a number, and the till needs the number itself. Put a type's name in square brackets in front of a value, and PowerShell turns the value into that type. [decimal] (read "as a decimal") makes a number that's right for money, cents and all, and [int] (read "as an integer") makes a whole number. Programmers call this a type conversion, or a cast. Compare text with numbers:

'36.00' + 1
[decimal]'36.00' + 1
[int]'3' * 12

36.001
37
36

Text plus 1 glues a 1 onto the end. As a decimal, it's arithmetic. Numbers, Dates and Types, in Module 5, has the rest of PowerShell's types.

A $ sign gets in the way: [decimal]'$36.00' stops with Cannot convert value "$36.00" to type "System.Decimal". Take it out first with the Replace method, as in Properties and Methods. The method runs first, then the conversion:

[decimal]'$36.00'.Replace('


, '')

This also fixes the sorting surprise from Spreadsheets as Objects: Import-Csv, with that lesson's sales file. Give Sort-Object a script block that turns each Price into a decimal, and it sorts by money instead of by letters:

Import-Csv sales-2026-10.csv | Sort-Object { [decimal]$_.Price } -Descending | Select-Object Product, Price -First 3

Product   Price
-------   -----
E-Bike    1299.00
City Bike 449.00
City Bike 449.00

Where-Object { [decimal]$_.Price -gt 100 } compares as numbers the same way.

Clean it, then save it: Export-Csv

Now the whole cleanup in one pipeline, over several lines (a line that ends with | or , tells PowerShell more is coming). Where-Object drops the rows with no store. Select-Object keeps five columns: two with new names, made with calculated properties from Columns You Make Yourself: Calculated Properties, and two turned into numbers. 'Bike Type' is in quotes because the heading has a space in it. Last, Export-Csv saves the objects as a new CSV file:

Import-Csv rentals-2026-10.csv |
    Where-Object Store -ne '' |
    Select-Object @{ Name = 'Date'; Expression = { $_.'Rental Date' } },
        Store,
        @{ Name = 'Bike'; Expression = { $_.'Bike Type' } },
        @{ Name = 'Hours'; Expression = { [int]$_.Hours } },
        @{ Name = 'Fee'; Expression = { [decimal]$_.Fee.Replace('


, '') } } |
    Export-Csv rentals-2026-10-clean.csv

It prints nothing. Before you save, you can end the pipeline with Format-Table instead, to look first. Here's the new file:

Get-Content rentals-2026-10-clean.csv

"Date","Store","Bike","Hours","Fee"
"2026-10-02","Downtown","City Bike","3","36"
"2026-10-04","Riverside","E-Bike","2","50"
"2026-10-09","Downtown","E-Bike","4","100"
"2026-10-12","Riverside","City Bike","2","24"
"2026-10-17","Downtown","Kids Bike","1","8"
"2026-10-24","Riverside","E-Bike","6","150"
"2026-10-30","Downtown","City Bike","5","60"

New headings, no blank rows, no dollar signs. Export-Csv puts every value in double quotes, which Excel and Import-Csv both read without trouble; add -UseQuotes AsNeeded to leave them out. The file is text again, so Import-Csv gives text back, but now it's text every program reads as a number: (Import-Csv rentals-2026-10-clean.csv | Measure-Object Fee -Sum).Sum says 428.

Export-Csv replaces the file without asking, like Set-Content. Keep the original export and save the cleaned copy under a new name, as here, so a mistake costs nothing. -NoClobber makes it refuse to replace a file that's already there.

Windows PowerShell 5.1 differs: its Export-Csv writes an extra first line, #TYPE System.Management.Automation.PSCustomObject, unless you add -NoTypeInformation, and it has no -UseQuotes.

Open it in Excel

Invoke-Item rentals-2026-10-clean.csv opens the file in the program Windows uses for .csv files, which is Excel if it's installed. Double-clicking it in File Explorer does the same. In Excel, the Hours and Fee values sit on the right of their cells: Excel read them as numbers, so =SUM() works on them. The headings are the ones the yearly workbook expects, so the rows can be pasted straight in.

While a file is open in Excel, Excel holds on to it. Run the cleanup again then, and Export-Csv stops with The process cannot access the file … because it is being used by another process. Close the file in Excel, and run it again.

Try it. Make rentals-downtown.csv with only the Downtown rentals from the cleaned file, then add up their fees with Measure-Object.

Check yourself

2 questions to try on the lesson page.

So what

Write the cleanup once, as a pipeline, and next month's messy export takes one command instead of twenty minutes of Find and Replace.

Key terms

Cheat sheets