# 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. ```powershell 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: ```powershell Set-Location "$HOME\terminal-practice\bike-shop" Import-Csv rentals-2026-10.csv | Format-Table ``` ```text 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: ```powershell (Import-Csv rentals-2026-10.csv | Measure-Object Fee -Sum).Sum ``` ```text 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: ```powershell '36.00' + 1 [decimal]'36.00' + 1 [int]'3' * 12 ``` ```text 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: ```powershell [decimal]'$36.00'.Replace('$', '') ``` ```text 36 ``` 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: ```powershell Import-Csv sales-2026-10.csv | Sort-Object { [decimal]$_.Price } -Descending | Select-Object Product, Price -First 3 ``` ```text 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: ```powershell 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: ```powershell Get-Content rentals-2026-10-clean.csv ``` ```text "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 - [Export-Csv](https://www.ask-codey.com/glossary/export-csv) - [CSV File](https://www.ask-codey.com/glossary/csv-file) - [Type Conversion](https://www.ask-codey.com/glossary/type-conversion-powershell) - [Calculated Property](https://www.ask-codey.com/glossary/calculated-property-powershell) ## Cheat sheets - [Saving Data: Export-Csv and Cleaning a CSV](https://www.ask-codey.com/cheat-sheets/powershell-export-csv-cheat-sheet) ## Related - [Spreadsheets as Objects: Import-Csv](https://www.ask-codey.com/lessons/powershell-import-csv) - [Columns You Make Yourself: Calculated Properties](https://www.ask-codey.com/lessons/powershell-calculated-properties) - [Reports: ConvertTo-Html and Out-GridView](https://www.ask-codey.com/lessons/powershell-reports) Source: https://www.ask-codey.com/lessons/powershell-export-csv