0

I have a CSV file that has a static number of columns (with headers) and a dynamic number of rows and I need convert it to another CSV which has a static number of rows and a dynamic number of columns (with or without headers).

Here is an example of what the before and after would look like in a spreadsheet. Please keep in mind that the top section would represent my input data and the bottom section is the desired state.

CSV Spreadsheet Data

Of course this is a small subset as I could have a user that has 50 groups and a user which may only have 1 group associated with it. This leaves me with not knowing the number of columns in advance so it has to be dynamic.

I have tried using what I found on this post, but I am running into an issue trying to modify the code to fit my needs. The closest I have been able to do is create a row for each unique user ID but instead of having the corresponding group names in columns next to each user, they are set as the headers and no values for the users.

Undesired Current Result

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Blkhwksfan2010
  • 3
  • 1
  • 3
  • Ansgar Wiechers that gets me exactly what needed. Like I said, I was way overthinking it. Thanks as well for the cleanup of my post as I will keep it in mind in the future when posting. Also I was not able to post the pictures directly because I didn't have 10 posts yet so it wouldn't let me embed them directly. – Blkhwksfan2010 Sep 21 '17 at 21:27

1 Answers1

1

If you don't require headers or defined columns you could simply collect the values from the second column in a hashtable of arrays where you use the values from the first column as keys:

$data = @{}
Import-Csv 'C:\path\to\input.csv' | ForEach-Object {
    $data[$_.UserID] += @($_.GroupName)
}

Then you can export the data to a text file like this:

$data.Keys | ForEach-Object {
    $_ + ',' + ($data[$_] -join ',')
} | Set-Content 'C:\path\to\output.txt'
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328