Parsing CSV of files paths line-by-line [Logic Request]

Question

I have a tricky data set to parse through and have not been able to formulate a processing method for it and several failed ideas have just made me more confused...

Sample data in CSV format - before processing

C:\User1\Videos\videos
10
C:\User1\Videos\videos
22
C:\User2\Videos\videos
8
C:\User2\Videos\videos
67
C:\User3\Videos\videos
18
C:\User3\Videos\videos
12
C:\User4\Videos\videos
90

I'm trying to combine the lengths of the video files in each user's video directory and output a list of each user and the total runtime of all their files.

Result - after processing

C:\User1\Videos\videos
32
C:\User2\Videos\videos
75
C:\User3\Videos\videos
30
C:\User4\Videos\videos
90

I'm looking for pseudocode or any advice really as to how I can achieve this result. I have been unsuccessful in trying to use nested loops and am having a hard time conceptualizing other solutions. I am writing this in VBScript for convenience with file processing in Windows.

Thanks so much for the help in advance, I appreciate any advice you can offer.

Answer 1

First, this is a line delimited format with two lines per record.
1: directory
2: video length

Second, you need only a single loop to read each line of the file and process the data.

Steps

1. Dim a dic var. Set dic = CreateObject("Scripting.Dictionary").
2. Dim vars for filepath, userkey, and length value
3. Loop and read lines from file
4. Inside the loop, read line 1 and identify user for the record. VBScript has the ability to split the string. Based on the example, if the idea is to aggregate all lengths under User1 no matter what the remaining subfolders are then split string and grab the first path element and use this as the user key. You can check that the second element is Videos to filter, etc or use more elements as the key or as expressed in your results example use the full string as the key for exact matching. Store the user key in a local variable.
5. Inside the loop, read second line and parse length from line 2, store in local variable length.
6. Inside the loop, check if key exists in the dictionary then if so get value for key and add to length yielding the sum, add key,sum to dictionary. "dic.Item(userkey) = sum" Else if it does not exist then just add key,value "dic.Item(userkey) = value"
7. Repeat from step 4 until end of file
8. List items from dictionary by getting keys and then printing each key and the key's value from the dictionary.

The value stored in the dictionary could be an Object to store more information. Don't forget error handling.