CODEWITHSUNDEEP
powershellexport-to-csv
Carsten
Carsten

Reputation: 1

How to add quotation mark to existing text in a csv file using PowerShell

I need to convert strings in a csv file to strings with quotation marks around it.

My csv file looks like this:

Description;AllowHosts;SPNs;Owner
 
Description1;server1$, server2$, server3$;MSSQLSvc/PD01.dom1.com:1521,MSSQLSvc/PD01.dom1;Owner JDOE
Description2;server4$, server5$, server6$;MSSQLSvc/PD02.dom2.com:1521,MSSQLSvc/PD02.dom2;Owner JDOE
Description3;server7$, server8$, server9$;MSSQLSvc/PD03.dom1.com:1521,MSSQLSvc/PD03.dom1;Owner JDOE

I tried to search for header "AllowHosts" and replace with quotation mark in start and end,

$csv = @(
Import-Csv -Path $New -Delimiter ';' -Encoding UTF8
)

$data = ConvertFrom-Csv $csv
$Data[0].AllowHosts = '"'

$Data | where AllowHosts -Like '*$' | foreach {
    $_.AllowHosts = '*$"'
}
$Data | where AllowHosts -Like 'SF' | foreach {
  $_.AllowHosts = '"SF*'
}
$Data | ConvertTo-Csv -NoTypeInformation

but it did not work as expected....

I would like to have quotation mark around each string

  1. in column "AllowHosts" (servernames)
  2. in column "SPNs"

I am hoping for a result like this:

Description;AllowHosts;SPNs;Owner
Description1;"server1$", "server2$", "server3$";"MSSQLSvc/PD01.dom1.com:1521","MSSQLSvc/PD01.dom1";Owner JDOE
Description2;"server4$", "server5$", "server6$";"MSSQLSvc/PD02.dom2.com:1521","MSSQLSvc/PD02.dom2";Owner JDOE
Description3;"server7$", "server8$", "server9$";"MSSQLSvc/PD03.dom1.com:1521","MSSQLSvc/PD03.dom1";Owner JDOE

But how?

I have a powershell script that imports csv-file and creates json-files. My problem is that this line

"      ""PrincipalsAllowedToRetrieveManagedPassword"""+": [" | Out-File $filepath1 -Append

gives this result

"PrincipalsAllowedToRetrieveManagedPassword": [ "server1$, server2$, server3$"  ],

instead of

"PrincipalsAllowedToRetrieveManagedPassword": [ "server1$", "server2$", "server3$"  ],

Upvotes: 0

Views: 305

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174990

Use the -replace operator to add "'s around each "word" in the string:

# read data into memory
$csv = Import-Csv -Path $New -Delimiter ';' -Encoding UTF8

# modify all `AllowHosts` and `SPN` cells
$csv |ForEach-Object {
    $_.AllowHosts = $_.AllowHosts -replace '([^\s,]+)','"$1"'
    $_.SPNs = $_.SPNs -replace '([^\s,]+)','"$1"'
}

# re-export
$csv |Export-Csv -Path path\to\export.csv -NoTypeInformation

The pattern ([^\s,]+) matches (and captures) any consecutive sequence of characters not containing , or whitespace, and the substitution string "$1" expands to "".

Beware that this introduces ambiguity, as "'s are also used as value qualifiers in CSVs - so Export-Csv will escape the quotation marks you've added to retain them, and the resulting file will look like this:

"Description","AllowHosts","SPNs","Owner"
"Description1","""server1$"", ""server2$"", ""server3$""","""MSSQLSvc/PD01.dom1.com:1521"",""MSSQLSvc/PD01.dom1""","Owner JDOE"
"Description2","""server4$"", ""server5$"", ""server6$""","""MSSQLSvc/PD02.dom2.com:1521"",""MSSQLSvc/PD02.dom2""","Owner JDOE"
"Description3","""server7$"", ""server8$"", ""server9$""","""MSSQLSvc/PD03.dom1.com:1521"",""MSSQLSvc/PD03.dom1""","Owner JDOE"

Upvotes: 2

Related Questions