I have an excel formula that helps me alter info in in an Avid ALE. However, saving an ALE From Excel isn’t the best options as you have to bring it back through ALE to fix everything. That’s why I’m here asking if someone would be able to help create an AppleScript Droplet to Automate the process. Basically, drop the ALE on the droplet, it runs the formulas and then saves the changes as an ALE.
Let me know if that makes sense. I’m in the process of trying to google how to script this but I am definitely not a strong scripter.
Basically, I am able to edit the original how I want to, save it as a Tab Delimited file, bring it into Avid’s ALE program and then rebuild. Just looking for a way to automate the process.
The new File I have added two columns “Name Temp & Camroll”
Name temp is taking the Scene, Take & first character of the Name column (example Scene 1, Take 1, A001 with my excel formula is 1-1a)
Camroll is taking the first & last 4 characters of the Name column and putting them together (example A001C001.mov with my excel formula would be A001.mov)
I know this is probably asking a lot and I’m still trying to work things out and don’t even have a start besides figuring out my work around and the excel formulas. Hope this helps and I can provide the Excel formulas if you need.
Okay, so I already sent you the original ALE and the final ALE I was able to create. I’m going to send to you now the excel spreadsheet that is my edited ALE, which I save as a Tab Delimited and then bring it back through Avid’s ALE program to restructure it all
Now, the columns that I insert and use a formula in are “Name Temp” & “Camroll”. Formula’s below:
Name Temp: J2&“-”&E2&LOWER(LEFT(A2))
Camroll: LEFT(A2,4)&RIGHT(A2,4)
The way the ALE is originally made, I ALWAYS use those columns, hence why things can be automated into a droplet since the excel formulas should never changed, except if the Name Temp needs to change in a certain way. Does this all make sense? Thanks again!
Thanks for the download, I am going to make a droplet for you that uses Excel and your spreadsheet, so that you can easily go in and change the formulaes for converting anything, should you ever need it.
I’m sorry this have taken some time, but now I am trying to figure something out.
First question up:
I see that some fields, having nothing to do with csv has been altered at the beginning:
from:
to:
Is this something you have done/must do, or is this something ALE has done?
Next question:
Is it so that the the Take and Shot field, may or may not have any data?
Are the fields that are empty always empty? (Reel, Manufacturer, Description).
This is what I got so far, no reason to commence to a droplet before we know that the meat works, the script below
lets you choose a file to convert, then lets you save it with a new name, please tell me if the conversion is right, or eventually, what is wrong.
set AppleScript's text item delimiters to ""
property name_col : 1
property take_col : 5
property scene_col : 10
tell application "Finder"
set thef to (choose file)
set thep to container of thef as alias
set raw_file to every paragraph of (read file (thef as text))
-- your code goes here...
end tell
# We are skipping the columns with the uninteresting data and goes for the meat
# here we are finding the lines that we are about to alter
set i to 0
repeat with aLine in raw_file
set i to i + 1
if contents of aLine is "Column" then
set header_pos to (i + 1)
else if contents of aLine is "Data" then
set first_data_row to (i + 1)
exit repeat
end if
end repeat
# Ok, so we got the positions, it is time alter the contents
# Headers up first!
set orig_headers to item header_pos of raw_file
set new_header to createNewHeader for orig_headers
# Updates the original file with new header columns
set item header_pos of raw_file to new_header
# It is time to convert the data: we have the start
set last_data_row to (count raw_file)
# but we need to assert the last line with data
if item last_data_row of raw_file is "" then
set last_data_row to last_data_row - 1
end if
# set orig_data to item data_row of raw_file
# set orig_data_columns to text items of orig_data
repeat with i from first_data_row to last_data_row
set orig_dataLine to item i of raw_file
set new_dataline to alterDataLine for orig_dataLine
set item i of raw_file to new_dataline
end repeat
# the columnar data has been changed, and we'll write it out to a new file of your choice
set {tids, AppleScript's text item delimiters} to {AppleScript's text item delimiters, linefeed}
set raw_file to raw_file as text
set AppleScript's text item delimiters to tids
set fn to (choose file name default location thep) as text
set fRef to (open for access file fn with write permission)
try
set eof fRef to 0
write raw_file as text to fRef
close access fRef
on error e number n
error e number n
end try
to alterDataLine for orig_dataLine
set {tids, AppleScript's text item delimiters} to {AppleScript's text item delimiters, tab}
set orig_data_columns to text items of orig_dataLine
set AppleScript's text item delimiters to tids # we set the delimiters back for now!
# We rebuild the data into the new format with the added columns
set col_nameTmp to item scene_col of orig_data_columns & "-" & item take_col of orig_data_columns & character 1 of item name_col of orig_data_columns
set col_camroll to text 1 thru 4 of item name_col of orig_data_columns & text -4 thru -1 of item name_col of orig_data_columns
set new_data_columns to items 1 thru 10 of orig_data_columns
set end of new_data_columns to col_nameTmp
set end of new_data_columns to col_camroll
set new_data_columns to new_data_columns & items 11 thru -1 of orig_data_columns
set {tids, AppleScript's text item delimiters} to {AppleScript's text item delimiters, tab}
set new_data_columns to new_data_columns as text
set AppleScript's text item delimiters to tids
return new_data_columns
end alterDataLine
to createNewHeader for orig_header
set {tids, AppleScript's text item delimiters} to {AppleScript's text item delimiters, tab}
set orig_header_columns to text items of orig_header
set AppleScript's text item delimiters to tids # we set the delimiters back for now!
# We rebuild the data into the new format with the added columns
# I surmise that change of UNC to UNC Path is something that Avid ALE does. (column nr 7 )
set col_nameTmp to "Name Temp"
set col_camroll to "Camroll"
set new_header_columns to items 1 thru 10 of orig_header_columns
set item 7 of new_header_columns to "UNC Path" # comment out this one if trouble some
set end of new_header_columns to col_nameTmp
set end of new_header_columns to col_camroll
set new_header_columns to new_header_columns & items 11 thru -1 of orig_header_columns
set {tids, AppleScript's text item delimiters} to {AppleScript's text item delimiters, tab}
set new_header_columns to new_header_columns as text
set AppleScript's text item delimiters to tids
return new_header_columns
end createNewHeader
Thank you so much! It is Canada Day here so I will have to give this a try later tonight and post responses. The header information originally comes in the ALE file, but for me to manipulate it manually in Excel, I have to remove all the header information, make my changes then save it to Tab Delim. Once I do that, I run it through Avid’s ALE program and it instantly adds the header information and changes the file to be a correct ALE file.
Reel & Description are more often than not going to be empty. The manufacturer column will have information there (but it is not important info at all). The “Shot” Column will be left empty and the Scene/Take columns will always have information as this is where we need to get the info to put into a “Name_Temp” column.
I’ll fix the removal of the superfluous header later on, what I really want to know, is if Avid ALE finds all columns to be in correct position, (and that you do).
Since the Name column and Tape columns are essentially the same (minus the .mov) I was wondering if those could be switched around. I went through and added the Tape Column property and then altered the Name Temp and Camroll columns and it works. I’d actually rather use the Tape column instead of Name because I don’t want the .mov to be there and the Tape column never has it.
Is there a way to make characters Uppercase and Lower case? In the test files I sent, the Name Temp column was
eg: 1-1a. I have been messing around with trying to add in something to the script that would allow for lowercase so it isn’t always upper (sometimes we need it lower).
I am not sure if I understood what you meant, but you’d really want to force the column Name-Temp to always be lowercase? That is how I interpreted it. There is no problem in changing case, or moving columns around, as long as you know it works with Avid ALE.
Please upload a file showing the differences, in the columns, with the specifics of what you have done column wise, then I won’t misinterpret anything.
Please just give me a list from left to right of what you have done, (on separate lines) like:
Removed column a
added new column before c
Renamed column d to z.
(Youll get accurate results if you provide a list like that! :))
It would awesome to give the option of having Lowercase or Uppercase just for the Name Temp column. Sometimes we need to name the files specifically. I have tested this script with an ALE and brought it into Avid and yes, it is working perfectly!
As far as me working on changing columns in the current script, I just replaced
“property of name_col : 1” with
“property of Tape_col : 6”
then went through and changed all
“name_col” to
“Tape_col”
and it works exactly the same and doesn’t include “.mov” in the Camroll anymore :). I’m trying to come up with a better test ALE and can post it once I get it. However, using the columns we have discussed are the only ones I need to use, and from the older ALEs I sent, up to the “Comments” column, nothing after that will need to be affected and all info is carrying into Avid :).
You do just fine, here I have made some pieces for you, that you’ll take out of the “Driver” and implement in your script, the dialog asking the question, if you don’t prefer to set a property in the script. I also tossed in an uppercase function while I were at it. Being Canadian, I hope you are English speaking, because those upper and lower case functions really only work within the range a-z of characters. Just tell me if you need some more sophisticiated ones!
set script_title to "Avid ALE Converter"
set mn to "MCUSR"
set shall_lowerCase to false
tell current application
activate
try
display dialog "Lowercase the name temp column?" buttons {"No", "Yes"} with title my script_title default button 2 cancel button 1 with icon 1
set shall_lowerCase to true
end try
end tell
if shall_lowerCase then
set mn to lowercase(mn)
display dialog mn
else
display dialog mn
end if
to lowercase(astring)
return do shell script "sed 'y/ABCDEFGHIJKLMNOPQRSTUFWXYZ/abcdefghijklmnopqrstuvwxyz/' <<<" & quoted form of astring
end lowercase
to uppercase(astring)
return do shell script "sed 'y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUFWXYZ/' <<<" & quoted form of astring
end uppercase
I made a couple of handlers for lowercase/Uppercase, using applescript instead of sed, since it is an AppleScript forum.
to uppercase(astring)
script o
property l : {}
property caps_diff : 32
property lowerA : 97
property lowerZ : 122
end script
repeat with aChar in every character of astring
tell aChar
if id ≥ o's lowerA and id ≤ o's lowerZ then
set aChar to character id (id - (o's caps_diff))
else
set aChar to contents
end if
end tell
set end of o's l to aChar
end repeat
return o's l as text
end uppercase
to lowercase(astring)
script o
property l : {}
property caps_diff : 32
property capsA : 65
property capsZ : 90
end script
repeat with aChar in every character of astring
tell aChar
if id ≥ o's capsA and id ≤ o's capsZ then
set aChar to character id (id + (o's caps_diff))
else
set aChar to contents
end if
end tell
set end of o's l to aChar
end repeat
return o's l as text
end lowercase
I tried implementing this into the current script you created and no success. I will keep trying to implement it though. Yes, I speak English so English scripts work
Anyways: her are caps handlers, that outperform sed.
script caps
# copyright 2013 McUsr and put into public domain, use it as you feel for
# Keep the line above and state any changes you do.
# 97 ID_LOWER_CASE_A : (id of "a")
# 122 ID_LOWER_CASE_Z : (id of "z")
# 65 ID_UPPER_CASE_A : (id of "A")
# 90 ID_UPPER_CASE_Z : (id of "Z")
# 32 CASE_DIFFERENCE : (97 - 65)
to uppercase(astring)
set l to {}
repeat with aChar in every character of astring
tell aChar
if id ≥ 97 and id ≤ 122 then
set aChar to character id (id - 32)
else
set aChar to contents
end if
end tell
set end of l to aChar
end repeat
return l as text
end uppercase
to lowercase(astring)
set l to {}
repeat with aChar in every character of astring
tell aChar
if id ≥ 65 and id ≤ 90 then
set aChar to character id (id + 32)
else
set aChar to contents
end if
end tell
set end of l to aChar
end repeat
return l as text
end lowercase
to Capitalize(astring)
set l to {}
tell every character of astring
tell item 1
if id ≥ 97 and id ≤ 122 then
set aChar to character id (id - 32)
else
set aChar to contents
end if
end tell
set end of l to aChar
repeat with aChar in rest of it # of every character of a string
tell aChar
if id ≥ 65 and id ≤ 90 then
set aChar to character id (id + 32)
else
set aChar to contents
end if
end tell
set end of l to aChar
end repeat
end tell
return l as text
end Capitalize
on run # Test driver:
local mstr, newstr
set mstr to "mCUSR"
log "original: " & mstr
set newstr to uppercase(mstr)
log "All upper case: " & newstr
set newstr to lowercase(mstr)
log "All lower case: " & newstr
set newstr to Capitalize(mstr)
log "Capitalized " & newstr
end run
end script
# tell caps to run
