Automation Blocks: Adding Row Numbers and Sorting by Column in CSV export

[Jared_Wmuff]

Hey @Mathias Moehl, I've been using Automation Blocks for a month to export shot lists from PP (vs previously exporting to EDL, converting to CSV etc) and love it — so handy!

There are two things I haven't been able to build automations for yet, both of which seem very simple. I've searched these forums, the user guide and asked ChatGPT to write a script (didn't work sadly), but still haven't figured them out, so wondered if you could help/point me in the direction of a tutorial which will:

1. How would I add a column of row numbers to a CSV/spreadsheet export (e.g. in a leftmost column of the attached, starting at 1)?
2.  Is there a way to sort the CSV export by a specific column (either before or after it's generated but still using Automation Blocks)? For example, in the attached screengrab I'd like the CSV to be sorted in ascending order by the timecodes in the fifth column.

Thanks again for the amazing tool!

[Mathias Moehl · Accepted solution]

Hi Jared,

great to hear that Automation Blocks for Premiere Pro is so handy for you 🙂

To add an index to the spreadsheet export you need to create a variable which counts the rows and increase it with each new row you export. 

Here is a very basic example. The first block script exports the names of all clips of the active sequence to a spreadsheet. In the second script, I extended that code such that in addition to the name now also the row number is written to the spreadsheet:

[Mathias Moehl · Accepted solution]

Concerning the sorting:

I think this will be done much easier in the spreadsheet application than in Automtion Blocks. If you still want to do it with Automation Blocks, the most promising appropach will be to ask ChatGPT to write a function for that task and then execute it with an Execute Code block.
In general, asking ChatGPT to only implemment certain functions for you instead of generating full block scripts is much more reliable.

Here I asked ChatGPT for a function to read a csv file, and save a copy of it which is sorted by a given index (block script attached). See the full conversation with ChatGPT here.

[Jared_Wmuff]

Hi Mathias, thank you so much for the speedy response! I thought the row index would be something simple like that, appreciate your clarification.

I followed your ChatGPT convo and advice and ended up using it to create a script which (using a column of timecodes formatted as numbers):

• Reads that column and adds a second column next to it which ranks those numbers in ascending order (e.g. if col 1 row 1 = 9.83 and col 1 row 2 = 4.15, col 2 row 1 returns "2" and col 2 row 2 returns "1", depending on other numbers in col 1).
• Sorts the sheet/CSV by column 2 and then deletes col 1. 

This seems to work really well to sort the sheet as accurately as a spreadsheet app (re-sorting in Google Sheets for instance doesn't correct anything), and also handily creates the (sorted) row index list: 

Attached the script if helpful to you/anyone else.

The last issue I seem to be having is this error when trying to combine this script/block with the CSV generator blocks: "Cannot open input file: ~/Desktop/test.csv". When I run these scripts separately, everything works fine. It looks like the CSV is only generated once the combined script is done? Perhaps I'm getting my understanding wrong here, but is there a way to close the loop/get the CSV exported before the my rank&sort script/block starts in the same process? I thought the issue might be therank&sort script itself, but the same error occurs when testing and appending other blocks to the generator part (e.g. the Rename File or Delete File blocks).

Hope that final question makes sense, thanks again for your help on this!

[Mathias Moehl]

Hi Jared,

oh, yes, for performance reasons, Autoamtion Blocks usually just writes the files to disk at the very end of the script execution (this prevents opening, closing and writing the file after each spreadsheet operation). To force it writing the file contents to disk before running your sorting algorithm on the file, run this in an execute code block:

$._extAutomationBlocks.all.fileCollection.cleanup()

See https://docs.mamoworld.com/automation-blocks/block-reference/filesText#forcing-file-writes-to-disk

I updated the example and you can now also download it in the Community Library at

File Handling/Spreadsheets/Sort CSV Spreadsheet Rows.xml

I also don't fully understand your idea with sorting using an extra temporary column. The sorting code from ChatGPT in my example should be able to deal with sorting based on timecode, numbers or texts out of the box. It first checks, if the cell looks like a timecode, then if it looks as a number and if none of that is the case, it treads it as a string.