SQL DBA Blog

This is a post on how to remove quotes from a CSV file using PowerShell.

If your dealing with a large CSV file, running the Import-CSV cmdlet below will consume a lot of system resources (RAM mostly). Also, if there’s a set of double quotation marks within the CSV file there may be some issues with the ordering of columns.

Create Test File

I’m creating the TestData variable and piping that out into a file using Out-File here. I can then check what’s in the file using Get-Content.

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

# Output data as CSV file in working directory.
$TestData | Out-File .\TestData.csv

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

Remove Quotess

Amend import and output file names and then Get-Content to check the file after since it’s a tiny test file.

# Import CSV file and output as new file with quotes removed.
Import-Csv .\TestData.csv | ConvertTo-CSV -NoTypeInformation | % { $_ -Replace '"', ""} | Out-File C:\CSV_Tests\TestData_NoQuotes.csv -fo -en ascii

As mentioned above in this post performing this on a large data file might not work out well. Monitor the performance of your machine and give the data a thorough visual check after.

If you encounter issues I’d probably ask why this is needed, as there are many options for handling such delimitated files in the more recent versions database tooling.