Removing Quotes from a CSV File with PowerShell

This post explains how to remove quotes from a CSV file using PowerShell, a common requirement when working with CSVs that include unnecessary or problematic quotes.

Whether you’re cleaning up data for easier processing or addressing formatting issues caused by quotes, this post will show you how to create a test CSV file, strip out quotes, and save the cleaned version efficiently. Additionally, we’ll highlight potential performance considerations when working with large files.

Step 1: Create a Test CSV File

Start by generating a test CSV file to practice on:

# Create a variable with sample CSV data
$TestData = @'
"first_name", "surname", "dob"
"pete", "whyte", "01/01/2000"
"joe", "broke", "05/12/2001"
'@

# Output data to a CSV file in the current directory
$TestData | Out-File .\TestData.csv

# Display the contents of the new CSV file
Get-Content .\TestData.csv

This script creates a small sample file named TestData.csv in your working directory.

Step 2: Remove Quotes from the CSV File

Now, process the CSV file to strip out quotes and save the cleaned data to a new file:

# Import the CSV file, remove quotes, and save to a new file
Import-Csv .\TestData.csv | ConvertTo-Csv -NoTypeInformation | % { $_ -Replace '"', "" } | Out-File C:\CSV_Tests\TestData_NoQuotes.csv -Force -Encoding Ascii

This script processes the CSV file:
> Import-Csv: Loads the CSV file into PowerShell.
> ConvertTo-Csv -NoTypeInformation: Converts the data back to CSV format without adding type metadata.
> -Replace '"': Removes all double quotes. We can change this to single quotes easily.
> Out-File: Outputs the cleaned data to a new file in the specified location.

Step 3: Verify the Output

After processing, inspect the contents of the new file to confirm the quotes were removed using Get-Content as shown in the screenshot above, or open the file for a quick manual check.

If you’re dealing with large CSV files, monitor system performance, validate the cleaned data, and consider using specialized database tools for better efficiency and handling.

I hope this tip was a useful one for you. Feel free to browse around my site here for more random tips like this!