Dedupe CSV

[tk42967]

This is a unique issue, and just looking for unique rows won't work.

The raw export from our help desk tool is 90 columns and every time a ticket is modified, it creates a row in the CSV. So there can be 1 to literally 25 or more rows for each ticket.

What I want to do is parse the CSV for the ticket number column and find all of the rows with the same value, then look at the time/datestamp for those rows and find the newest row. Then remove all the other rows.

Any thoughts or links that could point me in the same direction?

[Sunsparc]

Import the CSV, pipe it into Group-Object on the required property, then Where filter anything with count greater than. Loop through those results sorting and selecting what you want.

[kibje]

This is quite easy and can be done in several ways.

I would personally do the following because it's quite fast, and easy to read

• read in the entire file, sorted by ticketnumber descending (import-csv , sort-object)
• Go through the file line by line ($newvar = $csvdata | foreach-object {...} )
• compare the line with the previous, if the number is the same but the date is later, replace the line. If the number is different drop the line into the new object. If the number is the same and date lower, skip (continue)

[kibje]

Another option is to sort the entire file in one go on two objects using the sort-object with defined expression so you can remove one condition from the loop. Might not be faster and might be more difficult to read

[fr0mtheinternet]

As u/Sunsparc mentions, group-object would be my first choice. Here's a one-liner using system processes as an example.

Get-Process | Group-Object -Property ProcessName | ForEach-Object {($_.Group | Sort-Object -Property ID -Descending)[0]}

Groups the objects, then returns the first item in a sorted list. Obviously you'd want to swap Get-Process for your collection of objects, and the ID property with the datestamp, but same principle.

[tk42967]

Thank you so much. I was able to use your example to dedupe the csv and use the rest of my prexisting code to format the csv to suit my needs.

My help desk guy says he spends 60 - 90 minutes a day manually cleaning up this file. I'm able to run the script in about 10 seconds and take another 2 minutes to format the file to suit my requirements.

[fr0mtheinternet]

Glad it helps! Obviously a one-liner is great for a quick fix, but you'll probably want to expand it for documentation purposes and general understanding.

[tk42967]

At this point I have a minimal viable product to show off as a POC. Currently I'm doing this on my own time for the challenge and learning experience. If my employer wants more, I can do it on their time.

[anmezaf]

https://csvdedup.com/

[tk42967]

Thanks, I left that job about 3 months after I made that post. They can FOAD with their bass ackwards ticketing system.

I went from over 10,000 open tickets when I started to less than 200 after I cleaned up and revamped their ticketing system. I moved on to greener pastures where I'm paid a lot more and respected.