question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked MotoX80 answered

How to escape a comma in a string - Powershell

Hi,

I have the following code

  $output += "D", $headerfile[$i]."SALES_DOCUMENT_NUM", $headerfile[$i]."DELIVERY_NAME", "";$headerfile[$i]."Address1"
  $output -join "," | Out-File $filename -Append

On some of the addresses it has a comma in the string, the output join to create the csv file is seeing that as a new column rather than as part of the address.

How can I go about keeping a comma in the address but also use it as a delimiter in the CSV?


Thanks

Chris


windows-server-powershell
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In a CSV values including a comma (or a space) are typically enclosed in quotation marks: value1,value2,"Los Angeles, CA" Pretty much any library that reads CSVs will respect that standard.

1 Vote 1 ·

you can try your export with other Character

 export-csv -Delimiter ";"
0 Votes 0 ·
AndreasBaumgarten avatar image
1 Vote"
AndreasBaumgarten answered

Hi @ChristopherJack-1763 ,

you can try this:

 $filename = "junk/junk.csv"
 $name = "Pan, Peter"
 $username = "ppan"
 $city = "Berlin, Germany"
    
 $output = """$name""","$username","""$city""" -join ","
 $output | Out-File $filename

The line in the file will look like this: "Pan, Peter",ppan,"Berlin, Germany"


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MotoX80 avatar image
0 Votes"
MotoX80 answered

had the quoatation marks it outputs as @{orders=System.Object[]}.orders[1].'shipping_address'.'address2'

Try this:

   $output += 'D,"{0}","{1}",,"{2}"' -f  $headerfile[$i]."SALES_DOCUMENT_NUM",   $headerfile[$i]."DELIVERY_NAME",  $headerfile[$i]."Address1"
   $output -join "," | Out-File $filename -Append


That will add a single line to $output, so you really don't need to use -join unless you have other entries in the array.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered ChristopherJack-1763 edited

Hi,

You can add quotation marks to $headerfile[$i]."Address1" like this

 $output += "D", $headerfile[$i]."SALES_DOCUMENT_NUM", $headerfile[$i]."DELIVERY_NAME", "", "`"$($headerfile[$i]."Address1")`""
 $output -join "," | Out-File $filename -Append

Or you can use a PSCustomObject

 $output = [PSCustomObject]@{
     column0 = "D"
     column1 = $headerfile[$i]."SALES_DOCUMENT_NUM"
     column2 = $headerfile[$i]."DELIVERY_NAME"
     column3 = ""
     column4 = $headerfile[$i]."Address1"
 }
 $output | Export-Csv $filename -NoTypeInformation

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Ian, If I had the quoatation marks it outputs as @{orders=System.Object[]}.orders[1].'shipping_address'.'address2'

0 Votes 0 ·