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

Question

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.

I have updated the source and output for better clarity.

CPickler
– CPickler

2019-09-03 17:17:23 +00:00
Commented Sep 3, 2019 at 17:17 — CPickler
– CPickler, Commented Sep 3, 2019 at 17:17

iRon · Accepted Answer · 2019-09-04 06:57:30Z

1

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

edited Sep 4, 2019 at 6:57

answered Sep 3, 2019 at 18:43

iRon

24.4k10 gold badges60 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nathaniel Palmer · Accepted Answer · 2019-09-03 16:38:16Z

0

# 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 :)

edited Sep 3, 2019 at 16:38

answered Sep 3, 2019 at 16:30

Nathaniel Palmer

613 bronze badges

1 Comment

CPickler Over a year ago

Thank you, this got me 99% of the way there. For some reason if the member number starts with a zero then it doesn't include the value in the output. For example I had a "CBF","09091,039600" and it did not get output at all, whereas a "LAB","3394177,081593, 112796, 200741" included all the entries except the one that started with zero.

Karthick Ganesan · Accepted Answer · 2019-09-03 17:25:26Z

0

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

edited Sep 3, 2019 at 17:25

answered Sep 3, 2019 at 17:12

Karthick Ganesan

3851 gold badge5 silver badges13 bronze badges

1 Comment

CPickler Over a year ago

Thank you, this got me 99% of the way there but has the same problem as the other solution. For some reason if the member number starts with a zero then it doesn't include the value in the output. For example I had a "CBF","09091,039600" and it did not get output at all, whereas a "LAB","3394177,081593, 112796, 200741" included all the entries except the one that started with zero.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related