Parsing CSV to 2D Array using ExtendScript

[PJ-AVT]

Hi,

I've working on a simple script to parse Vimeo review page .csv file and add relevant markers to an active sequence with comments etc.

I've got it all working, except I'm having some issues with parsing the csv if fields contain commas or line breaks. I've tried various methods found in StackOverflow for parsing, but I can't seem to get any of them working, probably because ExtendScript is so old. Any idea what methods should I split the following csv to 2D array.

I would also like to remove the header row and double quotes from the fields (i.e. test comment 1 not "test comment 1")

"Video Version","#","Timecode","Username","Note","Reply","Date Added","Resolved"

"Test Video-01.mp4",1,00:00:00,AVT,"test comment 1",--,"Tuesday, July 9, 2019 At 8:49 AM",No

"Test Video-01.mp4",2,00:00:00,AVT,"another at same timecode",--,"Tuesday, July 9, 2019 At 8:50 AM",Yes

,3,00:00:00,--,"another at same timecode","reply here from anon","Tuesday, July 9, 2019 At 8:54 AM",Yes

"Test Video-01.mp4",3,00:00:11,AVT,"really long comment Lorem ipsum dolor sit amet, Purus sit amet volutpat consequat mauris nunc congue nisi. Semper viverra nam libero justo laoreet sit amet cursus. Id interdum velit laoreet id. Bibendum est ultricies integer quis auctor elit sed vulputate. And some special chars to boot: !"#$%&'()*+,-./:;<=>?@[\]^_`{|}~

Eros donec ac odio tempor orci dapibus. Nam libero justo laoreet sit amet. Pellentesque pulvinar pellentesque habitant morbi. Pellentesque eu tincidunt tortor aliquam nulla facilisi cras fermentum.",--,"Tuesday, July 9, 2019 At 8:50 AM",No

"Test Video-01.mp4",4,00:00:19,AVT,"another one different timecode",--,"Tuesday, July 9, 2019 At 8:50 AM",Yes

"Test Video-01.mp4",5,00:00:43,AVT,"comment here tooo",--,"Tuesday, July 9, 2019 At 8:51 AM",No

,6,00:00:43,AVT,"comment here tooo","reply to a comment","Tuesday, July 9, 2019 At 8:51 AM",No

,7,00:00:43,AVT,"comment here tooo","reply again","Tuesday, July 9, 2019 At 8:51 AM",No

,8,00:00:43,"PJ Palomaki","comment here tooo","Different person reply","Tuesday, July 9, 2019 At 8:52 AM",No

,9,00:00:43,--,"comment here tooo","Anon reply reply","Tuesday, July 9, 2019 At 8:53 AM",No

"Test Video-01.mp4",6,00:01:29,--,"Anon comment",--,"Tuesday, July 9, 2019 At 8:53 AM",No

,7,00:01:29,--,"Anon comment","Anon reply","Tuesday, July 9, 2019 At 8:53 AM",No

,,,,,,,

[Justin T - Hyper Brew · Accepted solution]

Look for CSV parsers that are compatible with ES3, that's the version of JS that ExtendScript is based off of.

[Markerbox Man]

I've developed an extension that can parse CSV data and automatically create markers in Premiere (http://markerbox.pro). A vimeo import will be available within a few weeks (http://markerbox.pro/development).

So you can either lean back and wait for Markerbox to support Vimeo or you can download the extension (http://markerbox.pro/download) and check out how the CSV parsing is done there. Parsing CSV data with quotes is more difficult than just splitting an array using a comma, colon or tab as delimiter. You must walk through the strings, letter by letter, to find out whether you are inside quotes or not. Keep in mind that you may find quotes inside quoted data, which will appear als double-quotes "". So when you walk through your string it's not enough to analyze the current symbol. You must also look one symbol ahead to find out whether the quote is the end of a data item or whether that's a double quote, which should appear as quote in the final data output.

Good luck!