How to remove double quotes from export-csv in powershell

Question

I have the below code:

$files = Get-ChildItem 'C:\SitePlotsA\' -filter '*.csv'
$currentDate = get-date


for ($i=0; $i -lt $files.Count; $i++) {
    $outfile = $files[$i].FullName + "out" 
    $csv = Import-Csv $files[$i].FullName -Delimiter "|"
    $newcsv = @()
    foreach ( $row in $csv ) {
        $row | Add-Member -MemberType NoteProperty -Name "TimeStamp" -Value $currentDate -Force
        $newcsv += $row
    }


    $newcsv | Export-Csv $files[$i].FullName -NoTypeInformation -Delimiter "|"

}

What it does is import all csv files in a given directory and add a new column with the current datetime. It also encapsulates all of my data in double quotation marks "". Is there anyway to have it output this without the double quotation marks? I've looked everywhere.

I want it to stay the way it is by looking at a directory of CSVs and not point to an individual csv.

Thanks!

Why do you want to remove the double quotes? It's part of the CSV file format. — Enigmativity
I'm bulk inserting the data into a SQL Table. I'm using | as a delimiter and when I run it after I insert this column, it looks like "Bob Smith" — Cameron Rose
What if your CSV looked like "Jim ""The Tank"" Brown",27,"Australia"? — Enigmativity
How are you adding the data to the SQL table? I suspect something is going a bit errant there, as CSV is a standard data format and the double quotes are not part of the values, they're for marking the start and end of each value... ie. CSV readers will interpret "|" as a split, not just the pipe character. If you're not interpreting that data correctly and instead are manually specifying the pipe character as your delimeter in some bespoke interpreation of the data, that's probably the issue? — Adam Parsons

Adam Parsons Adam Parsons · Accepted Answer · 2018-06-22T07:39:49

Assuming you need to manually interpret this data in a way that's not properly ingesting the CSV data format, you should be able to achieve what you're asking at the point at which you are feeding the data to your SQL table by performing a replace on each row (as a string).

You'll want to replace "|" with |, and then remove the " off each end.

For example:

$data = Get-Content $csvfile
$data | foreach {$_.replace('"|"','|').TrimStart('"').TrimEnd('"')}

How to remove double quotes from export-csv in powershell

1 Answers