CODEWITHSUNDEEP
powershellcsv
CPickler
CPickler

Reputation: 93

How to seperate CSV values within a CSV into new rows in PowerShell

I'm receiving an automated report from a system that cannot be modified as a CSV. I am using PowerShell to split the CSV into multiple files and parse out the specific data needed. The CSV contains columns that may contain no data, 1 value, or multiple values that are comma separated within the CSV file itself.

Example(UPDATED FOR CLARITY):

"Group","Members"  
"Event","362403"  
"Risk","324542, 340668, 292196"  
"Approval","AA-334454, 344366, 323570, 322827, 360225, 358850, 345935"  
"ITS","345935, 358850"  
"Services",""

I want the data to have one entry per line like this (UPDATED FOR CLARITY):

"Group","Members"  
"Event","362403"  
"Risk","324542"  
"Risk","340668"  
"Risk","292196"  
#etc.

I've tried splitting the data and I just get an unknown number of columns at the end.

I tried a foreach loop, but can't seem to get it right (pseudocode below):

Import-CSV $Groups
ForEach ($line in $Groups){
    If($_.'Members'.count -gt 1, add-content "$_.Group,$_.Members[2]",)}

I appreciate any help you can provide. I've searched all the stackexchange posts and used Google but haven't been able to find something that addresses this exact issue.

Upvotes: 1

Views: 1760

Answers (3)

iRon
iRon

Reputation: 23663

Import-Csv .\input.csv | ForEach-Object {
    ForEach ($Member in ($_.Members -Split ',')) {
        [PSCustomObject]@{Group = $_.Group; Member = $Member.Trim()}
    }
} | Export-Csv .\output.csv -NoTypeInformation

Upvotes: 1

Karthick Ganesan
Karthick Ganesan

Reputation: 385

Revised the code as per your updated question,

$List = Import-Csv "\path\to\input.csv"

foreach ($row in $List) {
    $Group = $row.Group
    $Members = $row.Members -split ","

    # Process for each value in Members
    foreach ($MemberValue in $Members) {
        # PS v3 and above
        $Group + "," + $MemberValue | Export-Csv "\path\to\output.csv" -NoTypeInformation -Append
        # PS v2
        # $Group + "," + $MemberValue | Out-File "\path\to\output.csv" -Append
    }
}

Upvotes: 0

Nathaniel Palmer
Nathaniel Palmer

Reputation: 61

# Get the raw text contents
$CsvContents = Get-Content "\path\to\file.csv"

# Convert it to a table object
$CsvData = ConvertFrom-CSV -InputObject $CsvContents

# Iterate through the records in the table
ForEach ($Record in $CsvData) {
    # Create array from the members values at commas & trim whitespace
    $Record.Members -Split "," | % {
        $MemberCount = $_.Trim()
        # Check if the count is greater than 1
        if($MemberCount -gt 1) {
            # Create our output string
            $OutputString = "$($Record.Group), $MemberCount"
            # Write our output string to a file
            Add-Content -Path "\path\to\output.txt" -Value $OutputString
        }
    }
}

This should work, you had the right idea but I think you may have been encountering some syntax issues. Let me know if you have questions :)

Upvotes: 0

Related Questions