FORUMS: list search recent posts

How to Open EDL in Excel with TCs in columns ?

COW Forums : Avid Media Composer

<< PREVIOUS   •   VIEW ALL   •   PRINT   •   NEXT >>
Rosie Walunas
How to Open EDL in Excel with TCs in columns ?
on Jan 21, 2013 at 8:18:25 am

Sorry this is really dense, but I'm not doing something right here.

I just want to open up an EDL (CMX 3600) in Excel so that all the TCs are in their own column. Every time I open it, it's just got the information and TCs on single rows. I know it's possible but I cannot get it to work. What step(s) am I missing? What is the step-by-step process ?

Right now, I just have the straight out, .edl, then I'm in Excel, choosing File > Open > (navigating to the EDL) > Open > Delimited > Next > Tab > Finish. And still there are no columns of TCs.

Thanks.


Return to posts index

John Pale
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 21, 2013 at 1:25:57 pm

When I do it, you get a dialing asking you to assist in mapping the data to columns...does this not happen with you?


Return to posts index

Michael Phillips
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 21, 2013 at 2:40:16 pm

Don't hit "finish". You need to select "next" and go through the steps selecting "fixed width" and then "text".

This works fairly well for single line EDL's. but can get a little messy with comments and effect commands if they are still there. Also keep in mind that a comment which may carry a clip name is on its own line, making sort operations a pain if at all possible. If this is related to your original request, you still won't have a duration defined unless someone knows how to create a formula for doing so, or a plug-in exists that can be added. If you're going down that route, an EDL does not declare its frame rate, so be sure that is part of the formula.

Michael


Return to posts index


Michael Cheung
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 21, 2013 at 3:31:10 pm

I do this almost every day. I go to Data > Get External Data > Text file. Delimit by "space". This will give you fields of EDL in separate columns. Then you can use formulae to shift data around. Copy and paste the whole lot back on itself using "paste special" and select values only to get rid of formulae and then you can sort to get rid of columns / rows you don't want.

Michael Cheung
http://filmcutter.blogspot.com


Return to posts index

Rosie Walunas
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 21, 2013 at 7:54:30 pm

"When I do it, you get a dialing asking you to assist in mapping the data to columns...does this not happen with you?"
- Not sure where you are seeing this window, if I hit Open, it takes me through the motions and it doesn't work, if I Get Data, it's grayed out.

"Don't hit "finish". You need to select "next" and go through the steps selecting "fixed width" and then "text"."
- I tried going to Open > Fixed width > (then I have to choose column break lines, which just breaks up other lines of text/words into the columns along with the TC) > Text > Finish

"I go to Data > Get External Data > Text file. Delimit by "space"."
- When I have Excel open: Data > Get External Data > (the options are grayed out). If I open a worksheet, I can navigate to and select the EDL, but then it just puts all the data on to the single rows of the worksheet.

Please see screen shots:
EDL sample: http://farm9.staticflickr.com/8088/8402206309_4ac402d044_o.png
EDL opened in Excel rows not columns: http://farm9.staticflickr.com/8053/8403296202_6740203dc9_o.png
Excel can't Get External Data: http://farm9.staticflickr.com/8187/8403296230_c097096d12_z.jpg (and when I do, I either get everything on single rows or entire lines of texts and words broken up into columns.)

Could there be something wrong with my EDL export? Or a particular setting in Excel.

Can't believe this is becoming such a struggle.

And, Yes, I plan to set up a formula to calculate durations of the clips.

Thanks.


Return to posts index

Rosie Walunas
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 22, 2013 at 6:02:18 am

I'm not exactly sure, but I think the biggest issue is the clip Names are very long, so it's kind of throwing things off. But I guess I'll have to deal.

Does anyone have any idea how to calculate durations using TC INs and OUTs of the columns?

I've found some Excel templates that show how to calculate durations - but the TCs have to be entered by hand and columns cannot be copied and pasted.

I'm working with EDLs of multiple tracks and we're talking hundred of clips, thus hundreds of TC lines to deal with.

Thanks.


Return to posts index


Michael Cheung
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 22, 2013 at 11:07:53 am

Something like this in your excel doc:

=((LEFT(C1,2)*60*60*24)+(MID(C1,4,2)*60*24)+(MID(C1,7,2)*24)+RIGHT(C1,2)) - ((LEFT(B1,2)*60*60*24)+(MID(B1,4,2)*60*24)+(MID(B1,7,2)*24)+RIGHT(B1,2))

This is assuming 24fps and column b is TC IN and column c is TC OUT. May have to +/- 1 for inclusive TCs etc....but should get you started.

This will give duration in frames

Michael Cheung
http://filmcutter.blogspot.com


Return to posts index

Rosie Walunas
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 22, 2013 at 7:34:19 pm

- can you substitute 24 for 30, or what other numbers would have to be changed?
- What do you mean by "May have to +/- 1 for inclusive TCs etc"? The frame rate is not exact?
- this goes in the duration column?


Return to posts index

Michael Cheung
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 23, 2013 at 9:48:27 am

Yes, you can just change the 24 to 30, or however many frames per second.

This formula converts column c into frames and then column b into frames and does c minus b to give you your duration.

The reason I said you may need to add 1 is because for example if your shot is:

TC IN: 00:00:00:00
TC OUT: 00:00:00:01

using that formula will give duration of 1 frame. When really it might be two frames.

Michael Cheung
http://filmcutter.blogspot.com


Return to posts index


Michael Phillips
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 23, 2013 at 12:22:09 pm

EDLs count +1 on the OUT by EDL specification.

Michael


Return to posts index

Michael Cheung
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 23, 2013 at 12:37:16 pm

There you are, by specification you shouldn't need to +1.

Thanks Michael!

Michael Cheung
http://filmcutter.blogspot.com


Return to posts index

Scott Cole
Re: How to Open EDL in Excel with TCs in columns ?
on Jan 25, 2013 at 12:30:04 pm

Michael is right. Linear edit systems, which are the origin of EDLs always worked with "exclusive outs," Non-linear edit systems tend to work with "inclusive outs." In other words, in a typical CMX session (the granddaddy of computerized linear tape to tape editing), the OUT of your current edit became the IN of your next edit. This means that the Timecode listed as the OUT wasn't actually included, but excluded, in your current edit, hence the term "exclusive outs." In the non-linear world, the frame you mark as your OUT actually is included in your edit, hence the term "inclusive outs."

This discrepancy is a bane to almost all editors who are transitioning from linear to non-linear, but explained once, it typically becomes rote. Until you are asked to work with some new device, at which point you begin to wonder, "hmmmm is this out inclusive or exclusive on this device???"

M. Scott Cole
Senior Post Production Editor
60 MINUTES
CBS News, NYC
sc6@cbsnews.com
mscottc@comcast.net


Return to posts index


Mica Gordon
Re: How to Open EDL in Excel with TCs in columns ?
on Feb 15, 2015 at 9:30:52 am

Thank you all!!

Best regards.
Micagordon


Return to posts index

<< PREVIOUS   •   VIEW ALL   •   PRINT   •   NEXT >>
© 2017 CreativeCOW.net All Rights Reserved
[TOP]