Powershell - Loading a CSV file into a multi-dimensional Array

Question

I have a CSV file for help desk calls. The same ticket might have 1,2, or even 5 records based on the number of updates it has. (One field is different, all other fields are identical).

I want to take the mostly-duplicate records and create one record with the differences concatenated into it. (I'm a long time programmer, this shouldn't be a problem. But I'm brand new to PowerShell.)

I figured the best way was to load the file into a multi-dimensional array, then I can step through the records looking for duplicate ID numbers.

So, assuming that is the best way to handle this problem, how do I load the CSV file into a multi-dimensional array?

Powershell has an Import-CSV cmdlet for importing into an array of objects. However, I don't really understand how you would import a CSV into a multi-dimensional array (unless you mean a 2-dimensional array, as opposed to a jagged array?). A CSV is inherently a 2-d structure... — RB.
– RB., Commented Oct 27, 2016 at 22:05
I think what you would use in idiomatic powershell is pipe the results of an Import-CSV into a Group-By. — RB.
– RB., Commented Oct 27, 2016 at 22:07
@RB. A "Group-By"? I'm not familiar with that, and search wasn't much help. — Jeff
– Jeff, Commented Oct 27, 2016 at 22:13
Sorry- I meant Group-Object (aka Group). I've posted an example of how to use both Import-CSV and Group-Object. Other useful cmdlets for record processing include Select-Object (for projecting out different sorts of objects - maybe you only want the first 3 fields for exampl) and Sort-Object (sorts the input by whatever property you specify), — RB.
– RB., Commented Oct 27, 2016 at 22:17

RB. · Accepted Answer · 2016-10-27 22:33:22Z

3

Ok, so without knowing your input data, here is a skeleton of how you would approach the problem in PowerShell. Remember that in PowerShell you are dealing with objects, which actually makes this a lot easier.

My input data is as shown:

IncidentID,Comment
IT01,"User says stuff is broken"
IT01,"All fixed"
IT02,"Printer is out of toner. Sent Moss to replace."
IT03,"Jen turned off the internet."
IT03,"Douglas is very cross - we need a fix urgently."
IT03,"Turns out Roy was playing a practical joke on Jen."

First, I import the CSV into an array of records - this is basically your 2d array, except that it's actually a 1-dimensional array of objects.

$> $records = Import-CSV myfile.csv

Then, we group the objects by the IncidentID

$> $incidents = $records | Group IncidentID

If we print $incidents now, we can see how that looks:

$> $incidents
Count Name                      Group
----- ----                      -----
    2 IT01                      {@{IncidentID=IT01; Comment=User says stuff is broken}, @{IncidentID=IT01; Comment=All fixed}}
    1 IT02                      {@{IncidentID=IT02; Comment=Printer is out of toner. Sent Moss to replace.}}
    3 IT03                      {@{IncidentID=IT03; Comment=Jen turned off the internet.}, @{IncidentID=IT03; Comment=Douglas is very cros...

To access a single record, you can use the following syntax:

$> $incidents[0].Group[0]
IncidentID Comment
---------- -------
IT01       User says stuff is broken

Finally, to turn the array of comments into a single string, you can use the following, which will create a "Calculated Property" (in PowerShell parlance) called Comments which flattens the comment array into a string.

$> $final = $incidents | `
             Select Name, `
                    @{`
                        Name='Comments'; `
                        Expression={ $_.Group | Select -Expand Comment | Out-String }}

$> $final[0].Comments
User says stuff is broken
All fixed

edited Oct 27, 2016 at 22:33

answered Oct 27, 2016 at 22:15

RB.

37.4k13 gold badges103 silver badges137 bronze badges

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

5 Comments

Jeff Over a year ago

I will try that tomorrow and let you know if I have any problems. Thanks!

Jeff Over a year ago

OK, I think I've got that. Now, I want to make each group a single record, in other words, concatenate all of the Comments together. I have additional fields, so I'll add one to your database (love the examples, BTW.)

Jeff Over a year ago

OK, I think I've got that. Now, I want to make each group a single record, in other words, concatenate all of the Comments together. I have more fields, so I'll add one to your database (love the examples, BTW.) All other fields in a group will be identical. ID, Date, Comment IT01,1/1/16, "User says stuff is broken" IT01,1/1/16, "All fixed" IT02,1/15/16, "Printer is out of toner. Sent Moss to replace." IT03,2/1/16, "Jen turned off the internet." IT03,2/1/16, "Douglas is very cross - we need a fix urgently." IT03,2/1/16, "Turns out Roy was playing a practical joke on Jen."

Jeff Over a year ago

$FileList = Import-Csv "U:\Call List.csv" $incidents = $FileList | Group ID foreach($group in $incidents) { >>> What goes here?<<< }

RB. Over a year ago

@Jeff - I would recommend asking a new question, describing how far you've got, what output you are currently getting, and what output you want to achieve. Ensure you include an MVCE. It's much easier than trying to do it through the comments on a random answer. Feel free to add a link to the new question to this answer so I'll notice it :)

Collectives™ on Stack Overflow

Powershell - Loading a CSV file into a multi-dimensional Array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related