In this blog post, we are going to share a script that will help you remove quotes from CSV files using PowerShell.
First, we are going to create a new test CSV File in our PowerShell Terminal for the demo, and then remove quotes from the data in the CSV file. As well as this, we also share a PowerShell script that will remove quotes from many CSV files in a folder. Good luck with your data manipulation!
Create a Test CSV File with PowerShell
We can create a test CSV file in PowerShell, first by generating the data in a variable, and then use Out-File to pipe it into a new CSV file. We can read the contents of the new CSV file by using the Get-Content PowerShell cmdlet.
# 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 the working directory. $TestData | Out-File .\TestData.csv # Show contents of the new CSV file. Get-Content .\TestData.csv
Remove Quotes from a CSV File with PowerShell
We are going to remove quotes from the CSV file we created above. This PowerShell script is great for handing individual files. It removes the quotes from the CSV file and outputs the modified data to a new file.
Ensure you have a backup of your CSV files to avoid accidental data loss.
Amend the directory/filename for the Import-CSV command and the output directory (Out-File) in the following command:
# Import CSV file and output as a new file with quotes removed.
Import-Csv .\TestData.csv | ConvertTo-CSV -NoTypeInformation | % { $_ -Replace '"', ""} | Out-File D:\csv-Demo\TestData_NoQuotes.csv -fo -en ascii
Remove Quotes from all CSV Files in a Folder
This next script takes it a step further by handling multiple CSV files within a specified folder. It removes quotation marks and generates new CSV files with “_noQuote” appended to their names.
Remember: Always keep backups before executing scripts like this one, especially when handling critical data. And keep an eye on system performance, particularly when dealing with sizable CSV files.
# Specify the folder path containing CSV files
$csvFolderPath = "D:\csv-Demo\"
# Process all CSV files in the specified folder
Get-ChildItem $csvFolderPath -Filter *.csv | ForEach-Object {
$outputFile = Join-Path $_.DirectoryName "$($_.BaseName)_noQuote.csv"
Import-Csv $_.FullName | ConvertTo-Csv -NoTypeInformation | ForEach-Object { $_ -replace '"', '' } | Out-File $outputFile -Force -Encoding ascii
}
For more posts like this one, check out my other blog posts on PowerShell scripting and general PowerShell tips. Happy scripting!
Leave a Reply