Bdtyktl

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:

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:

The end result when importing this into Excel is this:

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!

• 
  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"

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