How to Open EDL in Excel with TCs in columns ?

[John Pale]

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

[Michael Phillips]

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

[Michael Cheung]

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
https://filmcutter.blogspot.com

[Rosie Walunas]

“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: https://farm9.staticflickr.com/8088/8402206309_4ac402d044_o.png
EDL opened in Excel rows not columns: https://farm9.staticflickr.com/8053/8403296202_6740203dc9_o.png
Excel can’t Get External Data: https://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.

[Rosie Walunas]

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.

[Michael Cheung]

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
https://filmcutter.blogspot.com

[Rosie Walunas]

– 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?

[Michael Cheung]

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
https://filmcutter.blogspot.com

[Michael Phillips]

EDLs count +1 on the OUT by EDL specification.

Michael

[Michael Cheung]

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

Thanks Michael!

Michael Cheung
https://filmcutter.blogspot.com