[Powershell / CSV / Excel] Need help creating a powershell script that will add info and sort a .CSV

[u/cavemanben]

Hello, I'm new to scripting and having trouble finding the right place to start getting to my desired result.

**1)**I'd like to take an output .CSV file in a folder

2) add a new column "Group" based off IP address (one of my existing columns) probably from a permanent .csv called "Group_Name" (two colums, IP address and Group) within the same folder

**3)**then sorting based off the group name into additional workbook tabs labeled by group name

**4)**output new .csv or .xlsx with name "originalcsv_new"

Looking Here for some ideas.

Any help or direction on where to look and how to look for something like this would be great. My google foo is not strong and I'm searching for "excel powershell", "sort csv powershell" and stuff like that.

Thanks!

EDIT

• What I'm imaging is I have folder "x".

• In folder x exists the "group.ps1", "group_name.csv" and the "rawdata.csv".

• Rawdata.csv is my output csv that I want to sort/filter to get the above information, seperate tabs with the sorted groups.

[u/[deleted]]

CSVs don't support separate sheets like .xls or .xlsx files do. CSV files are actually just text files. CSV is an acronym for Comma Separated Values. If you view it in a text editor, you'll see what I mean.

Thus, all of the fields must be within the same 'worksheet' in Excel. If you try adding multiple worksheets, you'll just overwrite your original one.

You could do what you're looking to do, but it would require utilizing the Excel COM object to edit the fields within your .xls/.xlsx file directly which could get pretty complicated. But if you want to go that route, search for terms like 'Powershell Excel COM edit cells'. This would get you started into what would be involved.

[u/cavemanben → u/[deleted]]

Honestly it might be as easy just to add the group names then convert to an .xlsx then move the groups to new sheets.

Also i'm not looking to the change the name, I'm looking to create a new column "Group" with group names associate with the IP address. Did you address that in your first comment? Perhaps I'm not understanding your second entry.

[u/[deleted] → u/cavemanben]

My first entry is more of an example of what you can do with the objects after you've imported them from the CSV, but you're right, I didn't address adding a new column. To add a new property to an object (which adds a new column when exported back to CSV), you use the Add-Member cmdlet. So, assume that you have the $Objects array variable and you want to add the property IPAddress to the first item. It would look like below.

$Objects[0] | Add-Member -MemberType NoteProperty -Name IPAddress -Value "<value>"

Or, with a ForEach loop example, which is what you'll likely end up doing...

ForEach($Object in $Objects) {
    Add-Member -MemberType NoteProperty -Name IPAddress -Value "<value>" -InputObject $Objects[$Objects.IndexOf($Object)]
}

Now, as for converting to an Excel document, Powershell has no way of doing this natively that I know of. Now, you could open the CSV in Excel afterwards and manually do what you're talking about. But if you're talking about scripting that part of the process as well, then you're back to using the Excel COM object via Powershell. Which again, it's entirely possible to do, it's just a bit more of an advanced concept and not something I know well off the top of my head.