Powershell Parsing CSV file that contains comma

Question

Consider that CSV file:

Node Name,Client Name,Job Directory,Policy Name
server1,test.domain.com,"vmware:/?filter= VMHostName AnyOf "server2.domain.com", "server3.domain.com"",TEST

My code:

$events = Import-Csv "C:\file.csv" | foreach {
New-Object PSObject -prop @{
Server = $_.{Node Name};
Client = $_.{Client Name};
{JobDirectory/Script} = $_.{Job Directory};
Policy = $_.{Policy Name};  
}
}

I have some problems when I try to parse the third field. I am not sure if its because the comma, or the double quote.

This is the object I would like to have:

Node Name           : server1
Client Name         : test.domain.com
JobDirectory/Script : vmware:/?filter= VMHostName AnyOf "server2.domain.com", "server3.domain.com"
Policy Name         : TEST

Can someone help me?

Is that really how your csv is formed? Where every second line is actually a continuation of the previous? Also do you have any control over how this csv is created? — Matt, Feb 26 '15 at 13:16
I've edited my original post. Now I think its more clear. I can't control the creation of the cvs file. — user2637202, Feb 26 '15 at 13:56
Yes it is because of the lack of closing double quote. Never seen this behaviour. If you manually add the closing quote it works. Could use `get-content` for this i think — Matt, Feb 26 '15 at 14:01
You just changed your csv content. I was working on an answer based on what it was previously. Does it not have a new line now? — Matt, Feb 26 '15 at 14:14
Sorry Matt, No new line. (i've corrected code format again). Thanks! — user2637202, Feb 26 '15 at 14:18
`If ($thirdfield | Select-String -Pattern '"",') { $thirdfield = $string -replace ",",";" }` — user2637202, Feb 26 '15 at 15:00

score 1 · Answer 1 · edited May 23 '17 at 12:20

1

Ok, so the easiest way to approach this is to read the file in with Get-Content and then split each line where the commas are not inside quotes. I borrowed the regex from this solution for this.

Using your current input data I would do something like this

$filedata = Get-Content C:\temp\test.csv
$asObject = ForEach($singlerow in ($filedata | Select-Object -Skip 1)){
    $props = @{}
    $singlerow = $singlerow -split ',(?=(?:[^"]*"[^"]*")*[^"]*$)'
    [pscustomobject][ordered]@{
        Server = $singlerow[0]
        Client = $singlerow[1]
        "JobDirectory/Script" = $singlerow[2]
        Policy = $singlerow[3]
    }
}

Sample Output from $asObject | Format-List

Server              : server1
Client              : test.domain.com
JobDirectory/Script : "vmware:/?filter= VMHostName AnyOf "server2.domain.com", "server3.domain.com""
Policy              : TEST

edited May 23 '17 at 12:20

Community

1
1

answered Feb 26 '15 at 14:59

Matt

45,022
8
78
119

Thanks Matt. Nice code. What about this pseudocode?. If I find a double quote and a semicolon replace comma for semicolon.. `If ($thirdfield | Select-String -Pattern '"",') { $thirdfield = $string -replace ",",";" }` – user2637202 Feb 26 '15 at 15:13
Not sure what context you are calling this. How is `$thirdfield` defined? Why would you replace that last comma with a semicolon. If you don't want to use my code and have another avenue then update this question or ask another one. – Matt Feb 26 '15 at 15:29
No, no... your code is perfect and works very well! I am jealous.. :) I was rambling. Thanks a lot! – user2637202 Feb 26 '15 at 15:32

score 1 · Answer 2 · answered Feb 26 '15 at 15:30

Another way using your starting code

$obj = gc c:\temp\test.csv |
% { $_ -replace '"(\b[^"]*\b)"','$1' } |
    convertfrom-csv | % {  [pscustomobject][ordered] @{
    Server = $_.{Node Name}
    Client = $_.{Client Name}
    {JobDirectory/Script} = $_.{Job Directory}
    Policy = $_.{Policy Name} }
  }

Powershell Parsing CSV file that contains comma

2 Answers2