CODEWITHSUNDEEP
arrayspowershellcsv
Jeff
Jeff

Reputation: 29

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

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?

Upvotes: 2

Views: 6375

Answers (1)

RB.
RB.

Reputation: 37192

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

Upvotes: 3

Related Questions