CODEWITHSUNDEEP
powershellcsvquotation-marks
Ted.Xiong
Ted.Xiong

Reputation: 71

How to remove all quotations mark in the csv file using powershell script?

I would like remove all quotations character in my exported csv file, it's very annoying when i generated a new csv file and i need to manually to remove all the quotations that include in the string. Could anyone provide me a Powershell script to overcome this problem? Thanks.

$File = "c:\programfiles\programx\file.csv"
(Get-Content $File) | Foreach-Object {
    $_ -replace """, ""
} | Set-Content $File

Upvotes: 2

Views: 19652

Answers (9)

IT_Elder
IT_Elder

Reputation: 1

Your solution was actually close, you just missed adding the escape character, it works like this:

(Get-Content $FilePath).Replace("`"","") | Set-Content $FilePath

Upvotes: 0

RoadRunner
RoadRunner

Reputation: 26335

After you have exported the CSV file with Export-CSV, you can use Get-Content to load the CSV file into an array of strings, then use Set-Content and replace to remove the quotation marks:

Set-Content -Path sample.csv -Value ((Get-Content -Path sample.csv) -replace '"')

As mklement0 helpfully pointed out, this could potentially corrupt the CSV if some lines need quoting. This solution simply goes through the whole file and replaces every quote with ''.

You could also speed this up with using the -Raw switch with Get-Content, which returns a whole string with the newlines preserved, instead of an array of newline delimited strings:

Set-Content -NoNewline -Path sample.csv -Value ((Get-Content -Raw -Path sample.csv) -replace '"')

Upvotes: 1

Theo
Theo

Reputation: 61243

It seems many of us have already explained that quotes are sometimes needed in CSV files. This is the case when:

  • the value contains a double quote
  • the value contains the delimiter character
  • the value contains newlines or has whitespace at the beginning or the end of the string

With PS version 7 you have the option to use parameter -UseQuotes AsNeeded. For older versions I made this helper function to convert to CSV using only quotes when needed:

function ConvertTo-CsvNoQuotes {
    # returns a csv delimited string array with values unquoted unless needed
    [OutputType('System.Object[]')]
    [CmdletBinding(DefaultParameterSetName = 'ByDelimiter')]
    param (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)]
        [PSObject]$InputObject,

        [Parameter(Position = 1, ParameterSetName = 'ByDelimiter')]
        [char]$Delimiter = ',',

        [Parameter(ParameterSetName = 'ByCulture')]
        [switch]$UseCulture,
        [switch]$NoHeaders,
        [switch]$IncludeTypeInformation  # by default, this function does NOT include type information
    )
    begin {
        if ($UseCulture) { $Delimiter = (Get-Culture).TextInfo.ListSeparator }
        # regex to test if a string contains a double quote, the delimiter character,
        # newlines or has whitespace at the beginning or the end of the string.
        # if that is the case, the value needs to be quoted.
        $needQuotes = '^\s|["{0}\r\n]|\s$' -f [regex]::Escape($Delimiter)
        # a boolean to check if we have output the headers or not from the object(s)
        # and another to check if we have output type information or not
        $doneHeaders = $doneTypeInfo = $false
    }

    process {
        foreach($item in $InputObject) {
            if (!$doneTypeInfo -and $IncludeTypeInformation) {
                '#TYPE {0}' -f $item.GetType().FullName
                $doneTypeInfo = $true
            }
            if (!$doneHeaders -and !$NoHeaders) {
                $row = $item.PsObject.Properties | ForEach-Object {
                    # if needed, wrap the value in quotes and double any quotes inside
                    if ($_.Name -match $needQuotes) { '"{0}"' -f ($_.Name -replace '"', '""') } else { $_.Name }
                }
                $row -join $Delimiter
                $doneHeaders = $true
            }
            $item | ForEach-Object {
                $row = $_.PsObject.Properties | ForEach-Object {
                    # if needed, wrap the value in quotes and double any quotes inside
                    if ($_.Value -match $needQuotes) { '"{0}"' -f ($_.Value -replace '"', '""') } else { $_.Value }
                }
                $row -join $Delimiter
            }
        }
    }
}

Using your example to remove the unnecessary quotes in an existing CSV file:

$File = "c:\programfiles\programx\file.csv"
(Import-Csv $File) | ConvertTo-CsvNoQuotes | Set-Content $File

Upvotes: 6

wasif
wasif

Reputation: 15518

An extra double quote can be used to escape a double quote in a string:

$File = "c:\programfiles\programx\file.csv" 
(Get-Content $File) | Foreach-Object { $_ -replace """", "" } | Set-Content $File

Upvotes: 1

Esperento57
Esperento57

Reputation: 17492

One solution for dont remove the double quote into the string quoted :

$delimiter=","
$InputFile="c:\programfiles\programx\file.csv"
$OutputFile="c:\programfiles\programx\resultfile.csv"

#import file in variable (not necessary if your faile is big repeat this import where i use $ContentFile)
$ContentFile=import-csv $InputFile -Delimiter $delimiter -Encoding utf8 

#list of property of csv file
$properties=($ContentFile | select -First 1 | Get-Member -MemberType NoteProperty).Name


#write header into new file
$properties -join $delimiter | Out-File $OutputFile -Encoding utf8

#write data into new file
$ContentFile | %{
$RowObject=$_                                        #==> get row object
$Line=@()                                            #==> create array
$properties | %{$Line+=$RowObject."$_"}              #==> Loop on every property, take value (without quote) inot row object
$Line -join $delimiter                               #==> join array for get line with delimer and send to standard outut 
} | Out-File $OutputFile -Encoding utf8 -Append      #==> export result to output file

Upvotes: 1

js2010
js2010

Reputation: 27606

Next time you make one, export-csv in powershell 7 has a new option you may like:

export-csv -UseQuotes AsNeeded

Upvotes: 6

postanote
postanote

Reputation: 16116

Why are you manually in a text editor read Csv files?

You exported them to that format for a reason. To read them, just import them back in and view them on screen and or Read them back in and send the readout to notepad for reading.

Export-Csv -Path D:\temp\book1.csv
Import-Csv -Path D:\temp\book1.csv | 
Clip | 
Notepad # then press crtl+v, then save the notepad file with a new name.

If you don't want Csv, then don't export as Csv, just output as a flat-file, using Out-File instead.

Update

Since your last comment to me indicated your final use case. CSV into SQL is a very common thing. A quick web search will show you how even provide you with a script. You should also be looking at the PowerShell DBATools module.

How to import data from .csv in SQL Server using PowerShell?

Importing CSV files into a Microsoft SQL DB using PowerShell

ImportingCSVsIntoSQLv1.zip

Four Easy Ways to Import CSV Files to SQL Server with PowerShell

Find-Module -Name '*dba*' 
<#
Version  Name         Repository Description
-------  ----         ---------- -----------
1.0.101  dbatools     PSGallery  The community module that enables SQL Server Pros to automate database development and server administration
...
#>

Update

You mean this...

Get-Content 'D:\temp\book1.csv'
<#
# Results

"Site","Dept"
"Main","aaa,bbb,ccc"
"Branch1","ddd,eee,fff"
"Branch2","ggg,hhh,iii"
#>

Get-ChildItem -Path  'D:\temp' -Filter 'book1.csv' | 
ForEach {
    $NewFile = New-Item -Path 'D:\Temp' -Name "$($PSItem.BaseName).txt"
    Get-Content -Path $PSItem.FullName |
    ForEach-Object {
        Add-Content -Path $NewFile -Value ($PSItem -replace '"') -WhatIf
    }
}

<#
What if: Performing the operation "Add Content" on target "Path: D:\Temp\book1.txt".
What if: Performing the operation "Add Content" on target "Path: D:\Temp\book1.txt".
What if: Performing the operation "Add Content" on target "Path: D:\Temp\book1.txt".
What if: Performing the operation "Add Content" on target "Path: D:\Temp\book1.txt"
#>

Get-ChildItem -Path  'D:\temp' -Filter 'book1.csv' | 
ForEach {
    $NewFile = New-Item -Path 'D:\Temp' -Name "$($PSItem.BaseName).txt"
    Get-Content -Path $PSItem.FullName |
    ForEach-Object {
        Add-Content -Path $NewFile -Value ($PSItem -replace '"')
    }
}

Get-Content 'D:\temp\book1.txt'
<#
# Results

Site,Dept
Main,aaa,bbb,ccc
Branch1,ddd,eee,fff
Branch2,ggg,hhh,iii
#>

Of course, you need to use a wildcard for the csv files and use the -Resurse to get all directories and an error handler to make sure you don't have file name collisions.

Upvotes: 1

Lee_Dailey
Lee_Dailey

Reputation: 7489

keeping in mind that this may trash your data if you have embedded double quotes in your data, here is yet another variation on the idea ... [grin]

what it does ...

  • defines the input & output full file names
  • grabs the *.tmp files from the temp dir
  • filters for the 1st three files & only three basic properties
  • creates the file to work with
  • loads the file content
  • replaces the double quotes with nothing
  • saves the cleaned file to the 2nd file name
  • displays the original & cleaned versions of the file

the code ... 

$TestCSV = "$env:TEMP\Ted.Xiong_-_Test.csv"
$CleanedTestCSV = $TestCSV -replace 'Test', 'CleanedTest'

Get-ChildItem -LiteralPath $env:TEMP -Filter '*.tmp' -File |
    Select-Object -Property Name, LastWriteTime, Length -First 3 |
    Export-Csv -LiteralPath $TestCSV -NoTypeInformation

(Get-Content -LiteralPath $TestCSV) -replace '"', '' |
    Set-Content -LiteralPath $CleanedTestCSV

Get-Content -LiteralPath $TestCSV
'=' * 30
Get-Content -LiteralPath $CleanedTestCSV

output ... 

"Name","LastWriteTime","Length"
"hd4130E.tmp","2020-03-13 5:23:06 PM","0"
"hd418D4.tmp","2020-03-12 11:47:59 PM","0"
"hd41F7D.tmp","2020-03-13 5:23:09 PM","0"
==============================
Name,LastWriteTime,Length
hd4130E.tmp,2020-03-13 5:23:06 PM,0
hd418D4.tmp,2020-03-12 11:47:59 PM,0
hd41F7D.tmp,2020-03-13 5:23:09 PM,0

Upvotes: 3

Scepticalist
Scepticalist

Reputation: 3923

As above, the quotations are valid for csv, but to remove them you need to escape the quote mark in the replace operation as is a special character:

$File = "c:\programfiles\programx\file.csv"
(Get-Content $File) | Foreach-Object {
    $_ -replace "`"", ""
} | Set-Content $File

Upvotes: 1

Related Questions