Our Music Library is in the process of uploading our entire collection of audio files to an Online Music Delivery System called SourceAudio. Currently all of our Master Audio Files are on their site with all of the correct metadata. In addition to the Master Audio Files, we have alternate versions of those files - Stems (Alternate mixes of the original Master Audio File but still the same length as the original) and Cutdowns (60, 30, and 15 second versions of the Master Audio File). The next step is to upload all of the Stem Files and Cutdown Files so that they will "nest" themselves into their corresponding "parent" Master Audio File.
We are looking to create a comprehensive Master Excel Sheet that accounts for all of our Master Audio Files along with their corresponding Stem Files and Cutdown Files. This Master Excel will primarily be used to upload and assign metadata to the remaining audio files (Stems and Cutdowns) once they have been uploaded to the SourceAudio site.
RAW DATA EXAMPLE - This is the data we have available to us in its current form. To help give a bit more insight into the project we've added 3 sheets (MASTER FILES, STEM FILENAMES, CUTDOWN FILENAMES) that contain the entire batch of RAW DATA to be used in creating our final excel. We have roughly 4,200 Master Audio Files, 12,000 Stem Files, and while Cutdown File creation is still a work in progress, we are probably looking at 7,000-8,000 Cutdown Files. Add it all up and we are looking at a Master Excel with roughly 25,000 rows.
RESULT EXAMPLE - This is exactly how the Excel would need to appear (Color formatting is NOT necessary for the final Master Excel). We've started with the metadata from the 2 Master Audio Files listed, added in the pertinent data from the CUTDOWN and STEM FILE DATA, copied what needed to be copied, and entered in everything else.
NESTING WITH MASTER ID - The algorithm analyzing this metadata will take it's cue for how to "nest" related files into it's parent file by looking at the Master ID column. If a group of Stems and Cutdowns are to be "nested" into the Master File, those related files will need the parent file's SourceAudio ID pasted into their Master ID column. In other words the Master ID column will point to the file to be nested under.
YELLOW - Means this metadata just needs to be copied down from the Master Audio File.
RED - This would need to be left blank
GREEN - This metadata needs to be created. Notice the Title is a stripped down version of the Filename. It is required they be in this format. Also, when the Filenames have a (-1, -2, or -3) they are the same audio file but have been categorized in different Genres and therefore needed unique Filenames and metadata to fit SourceAudio's scheme. Stem Filename's are copied from above. Cutdown Filename's have to be created. Master ID comes from the parent file's SourceAudio ID. As mentioned before, Length for the Master Audio File and the Stems is the same. The Cutdown lengths are self explanatory. 60 is 1:00, 30 is 0:30, and 15 is 0:15. Cue ID is our own internal ID. SA4544 is actually the first ID in line. When this Excel is made the first newly created audio file row will get this first ID and then it will increment downward. The Master Audio Files need to keep their currently listed Cue ID. File Type just needs to be filled in with STEM or CUTDOWN depending on the type of file.