CSV, replace comma within quotas and Export CSV, Newbee Question

Question

hello,

is it posible to replace and export csv file with less pipes because I will lern PS and debug this code

so it works fine, but output remove dopplequota (no problem because excel can import it)

Import-Csv C:\temp\test1.csv | ForEach-Object  {
    $_.psobject.properties.Value -replace ',' -join ','
    #$_.psobject.properties.Value -replace ',', '' -join ','
  } | set-content -Encoding UTF8 c:\temp\result.csv 

Now I change the code and try output with export-csv (" will not removed and I can debug step per step and lern PS and Objects)
but it did'nt work

<#
test1.csv
name,answer1,answer2,answer3
demo1,yes,no,yes
demo2,yes,no,yes
#>

$x = import-csv C:\temp\test1.csv 
Foreach($line in $x){

   $line -replace ',' -join "," | export-csv c:\temp\result.csv -Append # did't work

}

hmmm? $line is an object

wrong Output?
"Length"
"2129"
"2645"
"2116"
"2283"
"2155"
"2143"

maybe missing Column Name in output? I have different headers Colums (it's an Survey evaluation)

Answer 1

how can I replace comma within quotas?

How about this?

cls
$csv = import-csv C:\temp\test1.csv
"Here is the csv"
$csv 
foreach ($row in $csv) {
    foreach ($col in $row.psobject.properties) {                      # look at each column 
        $col.value = $col.value.Replace(',','')  
    }
} 
""
"Save the csv"
$csv | export-csv c:\temp\result.csv -NoTypeInformation
"Here is the plain old text content of the result file"
Get-Content c:\temp\result.csv

Produces these results.

Here is the csv

name  answer1 answer2 answer3             
----  ------- ------- -------             
demo1 yes     no      I don't,,,,,,,, know

Save the csv
Here is the plain old text content of the result file
"name","answer1","answer2","answer3"
"demo1","yes","no","I don't know"



PS C:\> 

Answer 2

Export-csv will add the quotes for you.

cls
"Here is the plain old text content of the csv file"
get-content C:\temp\test1.csv
""
"Now read it as a csv"
$x = import-csv C:\temp\test1.csv
""
"Show the properties of x"
$x | get-member
""
"Save the csv"
$x | export-csv c:\temp\result.csv -NoTypeInformation
"Here is the plain old text content of the result file"

Get-Content c:\temp\result.csv

That code produces these results:

Here is the plain old text content of the csv file
name,answer1,answer2,answer3
demo1,yes,no,yes
demo2,yes,no,yes

Now read it as a csv

Show the properties of x


   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition                    
----        ----------   ----------                    
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()             
GetType     Method       type GetType()                
ToString    Method       string ToString()             
answer1     NoteProperty string answer1=yes            
answer2     NoteProperty string answer2=no             
answer3     NoteProperty string answer3=yes            
name        NoteProperty string name=demo1             

Save the csv
Here is the plain old text content of the result file
"name","answer1","answer2","answer3"
"demo1","yes","no","yes"
"demo2","yes","no","yes"



PS C:\> 

Answer 3

MotoX thanks for answer, it was very helpfull

how can I replace comma within quotas?

"name","answer1","answer2","answer3"
"demo1","yes","no","I don't, know"

 cls
 "Here is the plain old text content of the csv file"
 get-content C:\temp\test1.csv
 ""
 "Now read it as a csv"
 $x = import-csv C:\temp\test1.csv | ForEach-Object {$_ -replace ','}
 ""
 "Show the properties of x"
 $x | get-member
 ""
 "Save the csv"
 $x | export-csv c:\temp\result.csv -NoTypeInformation
 "Here is the plain old text content of the result file"

 Get-Content c:\temp\result.csv

Now read it as a csv

Show the properties of x

TypeName: System.String

Output?
Save the csv
Here is the plain old text content of the result file
"Length"
"53"
"52"

Answer 4

Line #6 of your script ($x = import-csv C:\temp\test1.csv | ForEach-Object {$_ -replace ','}) will return an array of strings.

This happens because the "-replace" is a string replacement operator. It expects a string as input and it will cause the PSCustomObject that Import-CSV produces to be cast as a string and then replace the commas in the string with nothing (effectively removing them).

The Export-CSV expects a PSObject (or an array of them) as input. When you provide a String object the are no values of the NoteProperty type so Export-CSV has no idea how to assign it to a column name -- so you get only the length of the string in the result (and "Length" as the only column).

Answer 5

thank you very much MotoX and Rich,

i hope this is my last question

 cls
 $csv = import-csv C:\temp\test1.csv
 "Here is the csv"
 $csv 
 foreach ($row in $csv) {
     foreach ($col in $row.psobject.properties) {                      # look at each column 
         $col.value = $col.value.Replace(',','')  

I can write $row in $csv (csv is an object with properties) but I can't write $col in $row ? why

$row is an Object with properties. For me it looks like $csv

sorry for bad questions and bad english