I spent many months working in Premiere logging all the shots with Scene, Shot and Take, including a brief description of the type of shot and the action in the Description field, and used the Comments field for my own comments about the shot, it’s purpose, etc. so that when another editor looks at this — or me, months from now when I may not remember why the heck we did what we did — and make some sense out of it.
I didn’t want to waste all that time, so I devised a method of exporting all that carefully typed information into Lightworks. Lightworks has a feature called a Logging Database, which exports all these fields about your project, including Clip name, shottake, scene, description, log notes, etc.
The beauty is that this .ODB file is a plain text file, and that you can make changes to it, and then import it back into Lightworks.
So, since Premiere Project files are saved as XML these days, I figured it would be easy to get that info transferred over. I knew it wouldn’t be easy, but also not impossible. My method may not be for the faint of heart!
I ended up using AWK (which runs multiplatform from Linux to Windows to Mac), a text editor and Excel (plenty of versions of these, including free versions such as Open Office, also multi-platform).
AWK is a text processing tool, essentially, and I created 2 files to parse out the XML data, and format it in a useful form for Excel to use. In Excel I used a VLOOKUP function to copy the data from the text files I manipulated from the XML originals.
Firstly, though, I discovered that Adobe’s project file format is NOT friendly or conducive to this type of work, and that the file names and kept very separate from the Metadata I wanted, and also that the Comments field is not standard, and is not kept with the metadata either. Due to this last discovery, I had to go to ALL my Premiere projects, copy the content of the Comments field into the Log Notes field… but at least there weren’t that many, and I didn’t loose the information.
Second step, since Premiere’s format is — very creative — I ended up exporting every project file as a Final Cut XML (which caused Premiere to crash a couple of times… hooray, just another reminder of why I moved over to Lightworks).
With all the Final Cut XML projects exported into the same folder, I ran both my AWK scripts (attached in the ZIP file) and then concatenated the resulting .TXT files into a single one.
This is the syntax for the first script: prem-log-info.awk which is strips out all the useless data, and outputs only the good fields
awk -f prem-log-info.awk *.xml > amnesia1.txt
NOTE: MAKE SURE THE .AWK FILES AND YOUR XML FILES ARE IN THE SAME FOLDER
Before running the second script on this file, I went in a cleaned things up a bit (manually deleted invalid lines). The main issue I had cam from adding notes in Premiere to pieces of Black Video, to give general scene notes to an editor. Since those aren’t associated with a file, they created problems in my translation. But it was an easy clean up. Also, sequences with files in them created some inconsistencies because they had references to file names, which my script interpreted as another entry, but none of the remaining fields existed (description, shot, take, notes, etc.).
With both of those issues cleaned up from the first file, it was time to move on.
Here is the syntax for the second AWK script: reformat.awk which puts this in an Excel-friendly format
awk -f reformat.awk amnesia1.txt > amnesia2.txt
Obviously, you need to have AWK installed on your machine. For Windows, I used the freely available GAWK for Windows (Gnu Windows tools).
That last file I brought into Excel, where I had opened the .ODB file from Lightworks (or rather, just the clip metadata portion of the file, as it contains some headers rows that do not help us at all).
All those .TXT files went into a second sheet in Excel, and I added a new B column where I removed the extension part of the file name using this formula (you can see what I did in the Excel file inside the .ZIP archive): =LEFT(A2,FIND(“.”,A2)-1)
That B column now matches the Clip name inside Lightworks, making the portion of copying the .TXT file data over to the .ODB sheet a simple matter of using a VLOOKUP: =IF(ISNA(VLOOKUP(A2,Sheet1!B:F,3,FALSE)),””,VLOOKUP(A2,Sheet1!B:F,3,FALSE))
In the end I used more complex string of functions for error handling, in each column – you’ll have to look at the final Excel file for the exact formula. I also decided to finagle the Name column as well, after converting the .XLSX to .CSV so the clips would show up named — the name column is how I matched file names to descriptions, etc. so I couldn’t change it in the .XLSX. But in the .CSV it was no problem. This was just so I could re-work any of the files without having to start from scratch.
I ended up also adding a new column for filename without extension to facilitate searching with the VLOOKUP, and had to add some error searching so Excel wouldn’t put “0” in some places, and would just leave it “” (blank).
I also moved one the NAME column around so it would work better with the VLOOKUP — again it’s easier to show in the file than explain here. Do feel free to contact me if you need any help of have any questions. I’ll do my best to help 🙂
With this Excel file all populated, then it was just a matter of exporting it as Comma Delimited Text, appending the header from the original .ODB file, and naming it something else, like UPDATED Amnesia Contents.ODB, and then import it back into Lightworks where it will be appended to every clip in the project.
It’s important to note that I worked primarily with Tab delimited files, in case any of the descriptions and notes from Premiere had any characters (like commas or quotes), so I then had to convert my tab delimited file to comma separated values, so I could paste into the .ODB file and have it match.
And voila… mission accomplished! All my hard work in Premiere was not a waste.
Your mileage may vary! I imagine this could work with any NLE that can export Final Cut XML, or similar format.
I’m sure there are other ways of doing this, but I’ve written many AWK scripts in my day, and I knew I could make it work very simply. I also wanted to use tools that were freely available.
So, if this explanation is confusing, or you have any issues downloading or understanding the files in the ZIP file, please let me know. I’m rather busy these days, but I will do my best to respond in a timely manner. You can also find me in the Lightworks forums, where I will be posting this description as well.
Let me know how it works for you!
Writer, Director, Filmmaker