CODEWITHSUNDEEP
parsingpowershellcsv
user2637202
user2637202

Reputation: 133

Powershell Parsing CSV file that contains comma

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?

Upvotes: 0

Views: 4335

Answers (2)

CB.
CB.

Reputation: 60956

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} }
  }

Upvotes: 1

Matt
Matt

Reputation: 46730

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

Upvotes: 1

Related Questions