1

I'm outputting raw EDL files from Avid Media Composer which is essentially just text that needs re-formatting into the appropriate columns so it's easy to digest for the person receiving it. For security reasons the machines we use do not have internet connection so I'm trying to understand how I can achieve this without the use of third party tools or websites from the net.

The Raw .EDL file when opened in Notepad looks like this:

RAW EDL FILE

It's basically just a summary of the cuts used on the timeline and a the In/Out source and destination time codes involved. The example above is very small in size as a full EDL can have up to 1000 cuts (each numbered line being a cut).

I managed to format this manually with the use of comma separators. I achieved this by adding comma's and quotations so it looks like so:

FORMATTED FOR EXCEL

The end result when importing this into Excel is this:

RESULT IN EXCEL

I've also been trying to explore the idea of using Powershell by using Get-Content to try and parse the data i need into specific rows/columns but I'm a complete novice in this field so I'm not sure what i'm doing:

$Content = Get-Content "C:\TEST EDIT DECISION LIST.EDL"
$Content | Foreach {


If ($_ -match '[0-9]{1,6}$') 

So i've managed to have Get-Content read the EDL file and the text within is retrieved fine. I then tried to apply the match operator to get it to identify the 6 digit numerical (000001) and the goal is to figure out how to send that to column 1 row 1 (but it doesn't want to run). I then need to get the operator to identify the next entry (GUIDE_VO) which would be alpha-numeric-symbolic with a maximum 32 character limit etc, so as to adhere to the formatting I've created manually for the rest of the line. I would need Powershell to rinse and repeat process this through every line in the EDL and compile a CSV for me.

My question is, how would I go about getting this EDL file to output to the CSV as per the manual formatting I've done? I'd like to make this possible a with a "drag and drop" bat file or similar workflow. The entries that appear in the raw edl are always in that specific order, with only the clip names and source files varying in what they say throughout all the data. The entry numbers also incrementally go up with every new line of data as well.

This is the raw text from the EDL File itself:

TITLE:   Untitled Sequence.01
000001  GUIDE_VO                         A     C        12:36:54:21 12:37:06:19 01:00:00:00 01:00:11:23 
*FROM CLIP NAME:  SFX CLIP 1 
*SOURCE FILE: GUIDE VO
000002  HORROR.COPY.01.MP3               A     C        01:00:00:00 01:00:22:10 01:00:11:23 01:00:34:08 
*FROM CLIP NAME:  SFX CLIP 2 
*SOURCE FILE: HORROR.COPY.01.MP3
000003  93_HORROR_DRONE.MP3              A     C        01:00:04:15 01:00:08:08 01:00:34:08 01:00:38:01 
*FROM CLIP NAME:  SFX CLIP 3 
*SOURCE FILE: 93 HORROR DRONE.MP3

Many Thanks in Advance for any help or suggestions from this amazing community!

  • Batch is not the best tool to edit text files. You need another tool. Can you get e.g. Notepad++ ? – Máté Juhász Feb 1 at 10:21
  • I do have Notepad++ i just figured we could also automate the solution in a batch script is all. Can the reformatting be done automatically in Notepad++ with ease? I have used a Notepad++ command within a batch script before. I'm assuming you're thinking of a long line command in the find and replace function? – Myles Feb 1 at 10:44
  • I'm also trying to explore parsing each segment of the data via "get-content" in powershell but i'm really not understanding much of what i'm doing thre – Myles Feb 1 at 11:18
3
  • Ctrl+H
  • Find what: ^(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+(\S+)\h+\R\*FROM CLIP NAME:\h*(.+?)\h*\R.+
  • Replace with: $1,$2,$4,$3,$5,$6,$7,$8,"$9"
  • check Wrap around
  • check Regular expression
  • UNCHECK . matches newline
  • Replace all

Explanation:

^               # beginning of line
    (\S+)\h+    # group 1, 1 or more non spaces, then 1 or more horizontal spaces
    (\S+)\h+    # group 2, idem
    ...         # idem until
    (\S+)\h+    # group 8
    \R          # any kind of linebreak
    \*          # asterisk
    FROM CLIP NAME:\h*  # literally FROM CLIP NAME: followed by 0 or more horizontal spaces
    (.+?)       # group 9, 1 or more any character but newline, not greeedy
    \h*         # 0 or more horizontal spaces
    \R          # any kind of linebreak
    .+          # 1 or more any character but newline

Replacement:

$1,                 # content of group 1 plus a comma
$2,                 # content of group 2 plus a comma
$4,$3,$5,$6,$7,$8,  # idem
"$9"                # content of group 9 surounded by double quote

Result for given example:

TITLE:   Untitled Sequence.01
000001,GUIDE_VO,C,A,12:36:54:21,12:37:06:19,01:00:00:00,01:00:11:23,"SFX CLIP 1"
000002,HORROR.COPY.01.MP3,C,A,01:00:00:00,01:00:22:10,01:00:11:23,01:00:34:08,"SFX CLIP 2"
000003,93_HORROR_DRONE.MP3,C,A,01:00:04:15,01:00:08:08,01:00:34:08,01:00:38:01,"SFX CLIP 3"

enter image description here

  • Hi Toto! I just gave it a and found that produced the correct formatting for Excel import. I do need to turn this into a drag drop batch script if possible though. I'm going to experiment with that now and see if i can get it to work, and pick it up once i'm back in the office again on Monday. I also need to test it with a much larger EDL that contains the same data entries but far more of it. I really appreciate all the help. Happy Friday to you. – Myles Feb 1 at 16:31
  • @Myles:You're welcome, glad it helps. Have a good week-end. – Toto Feb 1 at 16:40
0

if your source is

000001  GUIDE_VO                         A     C        12:36:54:21 12:37:06:19 01:00:00:00 01:00:11:23
*FROM CLIP NAME:  SFX CLIP 1
*SOURCE FILE: GUIDE VO
000002  HORROR.COPY.01.MP3               A     C        01:00:00:00 01:00:22:10 01:00:11:23 01:00:34:08
*FROM CLIP NAME:  SFX CLIP 2
*SOURCE FILE: HORROR.COPY.01.MP3
000003  93_HORROR_DRONE.MP3              A     C        01:00:04:15 01:00:08:08 01:00:34:08 01:00:38:01
*FROM CLIP NAME:  SFX CLIP 3
*SOURCE FILE: 93 HORROR DRONE.MP3

you could apply

mlr --skip-comments-with "*" --inidx --ifs ' ' --ocsv --repifs cat inputFile.txt

and have

1,2,3,4,5,6,7,8
000001,GUIDE_VO,A,C,12:36:54:21,12:37:06:19,01:00:00:00,01:00:11:23
000002,HORROR.COPY.01.MP3,A,C,01:00:00:00,01:00:22:10,01:00:11:23,01:00:34:08
000003,93_HORROR_DRONE.MP3,A,C,01:00:04:15,01:00:08:08,01:00:34:08,01:00:38:01

mlr is an opensource utility, also fow Windows, and you can run it via prompt. The last win exe is here (mlr.exe) https://github.com/johnkerl/miller/releases/tag/5.4.0

  • 1
    Hi aborruso, does that command output the exact result you've listed below? I'm interested to know how it figures out what to do with the data if so. I noticed that the clip name has been excluded from the result, which i would need in the last column of the CSV data. Thanks for all your input so far! – Myles Feb 1 at 11:52
  • hi @Myles do you have a lot of file, and for each at the first row you have something like "TITLE: Untitled Sequence.01", or do you have one file with a lot of title? If it's one file, could you share a real example? – aborruso Feb 1 at 11:56
  • The first line is only the name of the timeline and just appears once with every export. this never repeats. From line 2 onwards the same data repeats itself in the same style for every 3 lines. So l would need lines 2,3 and 4 parsed into the first row of cells seperated into the columns I've displayed in the screenshot. But looking at the result you've created in your answer i can see it's pretty much done. All that remains to be included is the clip names "SFX CLIP 1" etc – Myles Feb 1 at 12:07
  • I can always get a second process going to replace 1,2,3,4,5,6,7,8 with the names of the columns i need so that shouldn't be a problem. Its just the clip names that also need to go in. – Myles Feb 1 at 12:09
  • If i'm not mistaken the part that goes --skip-comments-with "*" is omitting the entire entry *FROM CLIP NAME: SFX CLIP 1 is that correct? So it's skipping the clip name SFX CLIP 1 itself as a result? – Myles Feb 1 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.