-
Add additional data and/or columns to a bin using data from an excel spreadsheet
I’ve been searching extensively for a solution to this and it seems like my situation is similar to many people yet the most promising of solutions just do no seem to work even when people have reported them successful.
I have several bins full of archive material, for simplicity, the bin I’m starting with is just images. The files are all named after an internal numbering system for the production, but there is a master spreadsheet which maps those names to the original supplier IDs and also contains descriptions of each image from the suppliers’ catalogues, it also contains the year each image was produced and the location.
I have a bin which I’d started manually logging myself with a comments column and a shoot date column and a location column. I realised that this master spreadsheet existed part way through and decided it would be a good idea to see if I could copy and paste the data from the spreadsheet to the bin’s columns using ALE files and excel.
This seemed good in theory, but Avid is fickle about this and doesn’t like excel messing with it’s ALE’s. I’m not the first to fail to anticipate that and I found many people offering variations on the theme of excel and other programs producing tab delimited output which Avid Log Exchange can read, but unfortunately, an ALE file contains a ‘header’ which is supposed not to column delimited and therefore presumably not have any tabs.
My problem is firstly that what exactly a ‘header’ is is something every posted solution assumes you know, and secondly, I assume that the issue with the header on a tab delimited txt version of an ALE file is that there are tabs in it that should be just spaces.
I can only assume that the header is the text at the top of any given ALE file as opened in a text editor or spreadsheet editor which reads
Heading
FIELD_DELIM TABS
VIDEO_FORMAT 1080
AUDIO_FORMAT 48khz
FPS 25Column
Color Name Log Notes Shoot Date Source File End Tape StartData
I also gather everything after ‘Data’ is everything that’s in the bin and all the data which describes it. The received wisdom appears to be various forms of editing the ALE file in excel and producing tab delimited output from it and assuming that output to be correct except for the header and to also open a ‘correct’ unedited ALE file in a text editor which has a completely unmodified and thus not tab delimited, header, next you copy the edited material from the header onwards in to the unmodified ALE file below the header therefore producing a file with a correct and identifiable header and correctly delimited data for all the rest of the file.
Theory unfortunately would seem to be at odds with reality for me as this is not working and is also really confusing. My first point of confusion is that if the header being ‘not column delimited’ means there are no tabs in the header then right from the start this isn’t the case with either ALE files exported from avid and opened in TextEdit, or Excel; or even with tab delimited text files produced by Avid. ALE files I’ve exported from Avid, but not modified in anyway, when opened in text edit have tabs in the header. As a matter of fact, if the formatting in this post has correctly preserved what I pasted in, you should be able to see that there are still tabs.
Furthermore I get very strange results, I’ve tried a million different combinations and every modified ALE file with the additional data I’ve attempted to import either throws up syntax errors immediately, throws up no errors whatsoever and appears to have imported but does not add any data to the bin, successfully adds one line of new data for one row of the bin and then complains about errors, or starts off okay – then adds some data to a few rows – then throw errors and then crashes Avid before I can save the bin.
- I’ve tried importing with the method described above and leaving the tabs that I thought shouldn’t be there in place and get the outcome of a syntax error.
- Manually removing all tabs from the ‘header’ and got the outcome of a successfully imported ALE with none of the new data added to the bin and no ‘*’ in the bin window indicating no change at all
- Editing an ALE file in excel, outputting as tab delimited text, importing that text in to Avid Log Exchange and outputting an ALE and received the outcome of:
- Avid Log Exchange saying it can’t ‘clean the file’ then experiencing a ‘segmentation fault’ and then producing a file anyway which when imported in to Avid results in the outcome of:
- Avid reporting multiple failures of syntax and crashing unless I abort
I’ve tried a number of other things too but there are too many for me to remember accurately or list.
In any case, does anyone know a reliable of way of doing this? All the data for this logging already exists and is ready to go and mapped to clips and in columns and cells and just seems so tantalisingly close to something that should be able to just go straight in to a bin no problem. I really don’t want to manually copy all that when it shouldn’t be necessary.
-
14 Replies
-
Well it can be done, but it does take some trial and error. In order for Media Composer to import an ALE, all columns must match Avid column exact if they are to be mapped to those columns. If not, they become custom colummns or may not work. Merging an ALE is even more stringent – The following three columns must match exactly, uppercase, lowercase and timecode:
Tape or Source File
Start
EndAs mentioned timecode has to match exactly or it won’t merge. There is no concept of a fuzzy merge based on other common metadata fields (a long time request).
The header has become redundant over the years and I was really pushing for Avid to finally get rid of it with the latest version, but they did not. They kept it… arrrgh. The other issue is that line breaks are senstive and I’ve seen issues coming from Excel where they were not correct. “Cleaning an ALE” is a feature from the film to tape days that would check that there was no overlapping timecode in a file. You can turn this off.
Syntax errors can be a bunch of different things, and needs to be managed error by error. I’ll take a look at one of your ALE files from Excel if you want to see if I can fix it. Once it works, you can see what changes were done for your other files. Send to michael[at]24p[dot]com
Michael
-
Wow Michael thanks.
Here’s a link to the relevant files.
I guess I’ll send you a vanilla ALE straight from Avid, untouched by Excel or anything. In addition to a plaintext tab delimited export of that same ALE file after it has been opened in excel and had data added to it.
If you felt so inclined you could make the latter file importable in to Avid and hopefully I could learn what it is you’re doing right. Thanks mate. Not sure what time difference we’re working on. I’m at home now and the work day has been over for a few hours but hopefully some time tomorrow we’ll overlap or I can keep testing form home.
Now that you mention timecode that reminds me of one of my other results which was this it warned something about the end timecode not being good, but it did this for specific “Events” and you could skip past them. This outcome involved a good 3-4 extra rows of correct data being imported after skipping through the bits it didn’t like but it quickly crashed before anything could be saved once you skip past the last event it has trouble with.
Unfortunately, given I’m now using my home equipment there may be subtle differences in the conditions so it’d be hard to pinpoint what has changed if the outcome is different. I don’t know if it makes any difference at all but I use office 2011 for mac and at work it’s office 2008.
As you can see in the files, for testing purposes I’m only introducing data in to one column, the ‘comments’ column and I’ve only added a few rows rather than all of them.
-
There as no link in the posting, and I only need the file that Excel has updated to get started.
Michael
-
Oh, that’s not a good start.
Well I zipped everything together so it’s faster to just send the link I should have sent to begin with. My bad, sorry.
here’s the link:
8620_aledatatoavbcolumnstests.zipThe files are all clearly marked so it should be easy to spot whichever one you need. Thank you very much for your help.
-
Alright… I got one of the entries to import as an ALE and merge into an existing clip in the bin you provided. I think the fundamental issue is that your comments fields is waaay to long an it crashed my v8.3 Media Composer several times. I reduced the number of characters of that field to <128 and it worked.
For ALE import, I changed Source File to Tape
To merge I kept Source File as Source File.ALE and updated bin attached. I only did one event, but you should get the gist…
Michael
-
Right, well I guess that would explain why I was never able to successfully import more than one entry myself.
The comments are long because they were never intended for an edit system but are the descriptions given by the archive suppliers in their catalogues. That’s unfortunate because though they are unnecessarily verbose for the purposes of logging in Avid and for an editor’s eye quickly scanning, the idea was that they still contained the essential information about any given image to make them searchable and reasonably assist with some extra context when viewing the images with thumbnails in the bin. Since all the information already existed I thought it’d be much more efficient if I simply copied it in verbatim.
Guess it wasn’t to be. Honestly though since the documents exists and the data refers to the same clip names I guess I can just distribute the spreadsheet to editors and they can search outside of avid for keywords and then search in avid for clip names.
Thanks very much for the help, I think I at least learned some things and maybe in future if I’m in this situation and the data I want to copy isn’t so verbose I can successfully import it in to my bins.
-
Hello Michael,
I am having a similar issue as Jimmy in regards to making changes to an ALE in excel and trying to bring it back into AVID. I have tried so many things and have done quite a bit of research. AVID is also helping me to try and resolve this issue. I was wondering if you would be willing to look into this?
Have a great day!
Thanks!Best,
Catalina -
Sure – email me the Excel file. What project type are you trying to get this into? What info has Avid given you so far?
send to michael[at]24p[dot]com
Michael
-
One of the first things to check is invisible characters. On a Mac, I use TextWrangler. When exporting out of Excel there tends to be more TAB characters after the last item on a line versus a carriage return.
Michael
-
Thank you Michael!
I sent you an email from our post account email: bvscedit@gmail.com
I look forward to hearing from you!
Best,
Catalina
Reply to this Discussion! Login or Sign Up