How to read every csv file in the folder and remove quote character from the csv file?

Question

How can i read every csv file the specific folder? When script below is executed, it only will remove quote character of one csv file.

$file="C:\test\IV-1-2020-04-02.csv"

(GC $file) | % {$_ -replace '"', ''}  > $file

Get-ChildItem -Path C:\test\  -Filter '*.csv'

The output only will remove the quote character of "IV-1-2020-04-02.csv". What if i have different filename ?

Does this answer your question? [How to remove all quotations mark in the csv file using powershell script?](https://stackoverflow.com/questions/60678901/how-to-remove-all-quotations-mark-in-the-csv-file-using-powershell-script). Do not simply use `-replace '"', ''` to remove the quotes, because under certain circumstances the quotes are essential in a CSV file. By replacing all of them you're likely to end up with mal-aligned field values. — Theo, Apr 05 '20 at 12:59

RoadRunner · Answer 1 · 2020-04-05T15:38:29.703

You can iterate each .csv file from Get-ChildItem and replace the quotes " with '' using Set-Content.

$files = Get-ChildItem -Path "YOUR_FOLDER_PATH" -Filter *.csv

foreach ($file in $files)
{
    Set-Content -Path $file.FullName -Value ((Get-Content -Path $file.FullName -Raw) -replace '"', '')
}

^{Make sure to pass your folder path to -Path, which tells Get-ChildItem to fetch every file from this folder}

^{Its also faster to use the -Raw switch for Get-Content, since it reads the file into one string and preserves newlines. If you omit this switch, Get-Content will by default split the lines by newlines into an array of strings}

If you want to read files in deeper sub directories as well, then add the -Recurse switch to Get-ChildItem:

$files = Get-ChildItem -Path "YOUR_FOLDER_PATH" -Filter *.csv -Recurse

Addtionally, you could also use Foreach-Object here:

Get-ChildItem -Path "YOUR_FOLDER_PATH" -Filter *.csv -Recurse | ForEach-Object {
    Set-Content -Path $_.FullName -Value ((Get-Content -Path $_.FullName -Raw) -replace '"', '')
}

Furthermore, you could replace Foreach-Object with its alias %. However, If your using VSCode and have PSScriptAnalyzer enabled, you may get this warning:

'%' is an alias of 'ForEach-Object'. Alias can introduce possible problems and make scripts hard to maintain. Please consider changing alias to its full content.

Which warns against using aliases for maintainability. Its much safer and more portable to use the full version. I only use the aliases for quick command line usage, but when writing scripts I use the full versions.

Note: The above solutions could potentially corrupt the CSV if some lines need quoting. This solution simply goes through the whole file and replaces every quote with ''. PowerShell 7 offers a -UseQuotes AsNeeded option for Export-Csv, so you may look into that instead.

score 0 · Answer 2 · edited Oct 07 '21 at 13:21

Don't just replace all the " unless you are very certain that it's a good idea; otherwise replace the " when it shouldn't matter because the field doesn't contain text with a comma, double quote, nor line break. (see RFC-4180 section 2, #6 and #7)

As with any script that overwrites its working files, make sure you have backups of those files should you want an undo option later on...

$tog = $true 
$sep = ':_:'
$header=@()

filter asString{
   $obj=$_
   
   if($tog){
      $header=(gm -InputObject $obj -Type NoteProperty).Name
      $hc = $header.Count-1
      $tog=$false

      $str = $header -join $sep
      $str = "$sep$str" -replace '"','""'
      $str = $str -replace "$sep(((?!$sep)[\s\S])*(,|""|\n)((?!$sep)[\s\S])*)",($sep+'"$1"')
      ($str -replace $sep,',').Substring(1)
   }

   $str = (0..$hc | %{$obj.($header[$_])}) -join $sep
   $str = "$sep$str" -replace '"','""'
   $str = $str -replace "$sep(((?!$sep)[\s\S])*(,|""|\n)((?!$sep)[\s\S])*)",($sep+'"$1"')
   ($str -replace $sep,',').Substring(1)
}

ls *.csv | %{$tog=$true;import-csv $_ | asString | sc "$_.new";$_.FullName} | %{if(test-path "$_.new"){mv "$_.new" $_ -force}}

Note: the CSV files are expected to contain their own headers. You could work around that if you needed to with the use of the -Header option of Import-Csv

How to read every csv file in the folder and remove quote character from the csv file?

2 Answers2