No internet connection
  1. Home
  2. How to

Parse CVS file. Create markers and build timeline

By Randall Smith @Randall_Smith
    2021-07-26 13:52:38.287Z

    All,

    I know this is a bit more complex that what I usually ask, but I am hoping someone here can help me out.

    I/We do quite a bit of work where we need to check/phase cancel a source video/audio element against a video that was derived from the 'source' element.

    There are sometimes up to 100 of these. I was hoping to automate some of this away.

    Here is what I have. I can generate a CSV that has: [Child File], [Source File], [Duration (in seconds)]

    My first thought was to try and parse the CVS into an Array, but the Notes app might be an easier solution.

    Is there an easy way to convert this CSV into something that Notes will deal with?

    I am assuming that the first step is to convert the Duration column into Timecode.

    After that, I am looking that have 20 seconds of space between each video.

    Any help would be appreciated.

    Randall

    • 8 replies
    1. R
      Randall Smith @Randall_Smith
        2021-07-27 15:36:49.754Z

        Here is what I have so far:

        function openCsvAsText() {
            var path = sf.interaction.selectFile({
                prompt: 'Please Select File to Open',
            }).path;
        
            var contents = sf.file.readText({ path }).text;
        
            var arr = contents.split('\n');
        
            for (var i = 1; i < contents.length; i++) {
                var data = arr[i].split(',');
                var isci = data[0];
                var baseSpot = data[1];
                var duration = data[2];
                var duration = duration.replace(/:/g, '');
                var durationNum = parseInt(duration, 10);
                //alert(duration);
        
        
                addMarker(isci);
        
                addTimeCode(durationNum);
        
        
        
        
        
                //    alert(isci);
            }
        
        }
        
        
        function getCurrentTimeCode() {
        
            var currentTC = sf.ui.proTools.selectionGet().mainCounter.trim();
            alert(currentTC);
        }
        
        function addTimeCode(duration) {
        
            const spaceBetweenVideos = (20 * 48048);
            const videoDuration = ((Number(duration) * 48048));
        
            sf.ui.proTools.mainCounterSetValue({
                targetValue: "Samples",
            });
        
            var currentTC = sf.ui.proTools.selectionGet().mainCounter.trim();
            var currentTC = Number(currentTC);
        
        
            var timeCodeDuration = (currentTC + videoDuration + spaceBetweenVideos);
            var timeCodeDuration = String(timeCodeDuration);
        
            //alert(timeCodeDuration);
        
            sf.ui.proTools.selectionSet({
                mainCounter: timeCodeDuration,
            });
        
        
        }
        
        
        
        
        function openCsvFile() {
        
            var path = sf.interaction.selectFile({
                prompt: 'Please Select File to Open',
            }).path;
        
            var contents = sf.file.readText({ path }).text;
        
            var linesInCsv = contents.split(/\n/);
        
            alert(linesInCsv);
        
            sf.file.open({
                path: path,
                applicationPath: "/Applications/Microsoft Excel.app",
            });
        
            sf.wait();
        
            sf.ui.app('com.microsoft.Excel').appActivateMainWindow();
        
            sf.ui.app("com.microsoft.Excel").mainWindow.comboBoxes.whoseDescription.is("name box").first.mouseClickElement({
                relativePosition: { "x": 50, "y": 10 },
            });
        
            sf.keyboard.press({
                keys: "backspace, backspace, shift+a, 2, numpad enter",
            });
        
            sf.ui.app('com.microsoft.Excel').menuClick({
                menuPath: ["Edit", "Copy"],
            });
        }
        
        function proToolsHourOne() {
            sf.ui.proTools.appActivateMainWindow();
            sf.ui.proTools.mainCounterSetValue({
                targetValue: "Timecode",
            });
        
            sf.ui.proTools.selectionSet({
                mainCounter: "01:00:00:00",
            });
        
        }
        
        function addMarker(markerDescription) {
        
            sf.keyboard.press({
                keys: "numpad enter",
            });
        
            sf.keyboard.type({
                text: markerDescription,
            });
        
            //sf.keyboard.press({
            //    keys: "cmd+v",
            //});
        
        
            sf.keyboard.press({
                keys: "numpad enter",
            });
        }
        
        sf.ui.proTools.appActivateMainWindow();
        proToolsHourOne()
        //processData()
        
        openCsvAsText()
        
        //addTimeCode();
        
        
        

        With this, I am able to open a CSV that I specify. (It is formatted ISCI, Basespot, Duration).
        I set the beginning TC to 01:00:00:00
        I pull the ISCI, drop a marker for it and move the timeline down by the duration a 20 second buffer. I am doing this in samples because the math is easier than dealing with timecode addition.

        As a first step I am pretty happy. Here is what I need to deal with before moving on to the next step:

        Is there an easy way to strip/ignore the header on the CSV? Right now, I am removing it manually

        In the CSV there are durations where the length is not yet known (TBD on the form). This is returning a NaN error. How do I skip these or can I just give them a big enough number where it won't be an issue (120*48048)?

        The duration is coming in as ':'+length in the CSV (ex :30). How would I deal with a duration that exceeds 60 seconds?

        Each of the ISCI and Basespot names corresponds to a video file. My next step will be to use the markers to 'Assemble' the video/audio time line at the marker locations.

        Thanks,
        Randall

        1. Hi Randall,

          In terms of TBD and NaN, you can use the built-in Javascript function isNaN to check and skip that line.
          For example insert this before the call to addMarker in your for loop:

          if (isNaN(durationNum)) continue;

          1. The duration is coming in as ':'+length in the CSV (ex :30). How would I deal with a duration that exceeds 60 seconds?

            You could use a selectionSet and set the selectionLength property to let PT do the timecode math for you and then after it has made the selection, collapse to the right end of the selection or read back the new selectionEnd.
            SoundFlow has internal timecode math libraries but they're currently only available to registered SF developers.

            1. Is there an easy way to strip/ignore the header on the CSV? Right now, I am removing it manually

              Skipping the first line by starting on index 1 in the for loop is how this is often done, so I wouldn't sweat that :)

              1. RRandall Smith @Randall_Smith
                  2021-08-04 15:53:48.865Z

                  Great. So at this point, I have a script that will import all of the data I need from a CSV.

                  Next step it to match a clip against the marker name. This is what I am using:

                  sf.ui.proTools.mainWindow.invalidate()
                  const currentTimecode = sf.ui.proTools.selectionGet().mainCounter.trim();
                  const memoryLocationsInfo = sf.proTools.memoryLocationsFetchFromGui().collection.list.filter(x => x.name.match("VERS.019394.0"))
                  log(memoryLocationsInfo);
                  
                  sf.ui.proTools.appActivate();
                  
                  
                  function findLocationName(locationName) {
                      sf.ui.proTools.mainCounterSetValue({ targetValue: "Samples" })
                      const memoryLocations = sf.proTools.memoryLocationsFetchFromGui().collection.list.filter(x =>
                          x.mainCounterValue >= userSelection.selectionStart &&
                          x.mainCounterValue <= userSelection.selectionEnd &&
                          x.name.match(locationName))
                      return memoryLocations
                  }
                  
                  function doSomething(memoryLocations) {
                  
                  
                      sf.ui.proTools.memoryLocationsGoto({
                          memoryLocationNumber: memoryLocations.number
                      })
                  
                  
                      sf.ui.proTools.memoryLocationsGoto({
                          memoryLocationNumber: memoryLocations.number
                      })
                  
                  
                      sf.ui.proTools.menuClick({ menuPath: ["Edit", "Paste"] })
                  }
                  
                  function copyClipToPlayHead(name) {
                  
                  
                      sf.keyboard.press({
                          keys: "cmd+shift+d",
                      });
                  
                      sf.keyboard.press({
                          keys: "cmd+shift+f",
                      });
                  
                      sf.ui.proTools.windows.whoseTitle.is('Find Clips').first.checkBoxes.whoseTitle.is('By name').first.checkboxSet({
                          targetValue: "Enable",
                      });
                  
                      sf.ui.proTools.windows.whoseTitle.is('Find Clips').first.textFields.whoseTitle.is('').first.elementSetTextFieldWithAreaValue({
                          value: name,
                      });
                  
                  
                      sf.ui.proTools.windows.whoseTitle.is('Find Clips').first.buttons.whoseTitle.is('OK').first.elementClick();
                  
                  
                      sf.ui.proTools.mainWindow.popupButtons.whoseTitle.is('Clip List').first.popupMenuSelect({
                          menuPath: ["Select", "All"],
                          targetValue: "Enable",
                      });
                  
                      const row = sf.ui.proTools.mainWindow.clipListView.childrenByRole("AXRow").first;
                  
                  
                      row.children.allItems[1].children.first.popupMenuSelect({
                          isRightClick: true,
                          menuPath: ['Object Select in Edit Window'],
                          onError: "Continue",
                      });
                  
                      //copy selected clip
                  
                      sf.keyboard.press({
                          keys: "cmd+c",
                      });
                  
                      sf.wait({
                          intervalMs: 200,
                      });
                  
                      //   sf.ui.proTools.memoryLocationsTempGoto({
                      //       tempNumber: 1,
                      //   });
                  
                  
                      //   sf.keyboard.press({ keys: "numpad minus", });
                  
                      //   sf.keyboard.press({ keys: "cmd+v", });
                  
                      //   sf.keyboard.press({
                      //       keys: "cmd+shift+d",
                      //   });
                      //   sf.keyboard.press({
                      //       keys: "shift+tab",
                      //   });
                  
                      //   sf.ui.proTools.menuClick({
                      //       menuPath: ["Clip", "Group"],
                      //   });
                  
                  
                      //  sf.ui.proTools.menuClick({
                      //      menuPath: ["Edit", "Copy"],
                      //  });
                  
                  
                      // sf.ui.proTools.nudgeSet({ value: "00:00:00:01.00", });
                  }
                  
                  function clipCut() {
                      sf.keyboard.press({
                          keys: "cmd+c",
                      });
                  
                      sf.wait({
                          intervalMs: 200,
                      });
                  
                  }
                  
                  function getClipNames() {
                      sf.ui.proTools.appActivateMainWindow();
                  
                  
                  
                      //alert("Clip List Needs to be visible");
                  
                      let selectedClips = sf.ui.proTools.mainWindow.tables.whoseTitle.is('CLIPS').first.children.whoseRole.is("AXRow").allItems.map(row =>
                          row.children[1].children.first.value.value).filter(c => c.startsWith('Selected. '));
                  
                      log(selectedClips.length);
                  
                      var myPrompt = selectedClips.length;
                  
                      function clipName() {
                  
                          sf.ui.proTools.appActivate();
                  
                          sf.ui.proTools.memoryLocationsTempCreate({
                              tempNumber: 1,
                          });
                  
                  
                          sf.ui.proTools.menuClick({
                              menuPath: ["Clip", "Rename..."],
                          });
                  
                          sf.ui.proTools.windows.whoseTitle.is('Name').first.elementWaitFor();
                  
                          let currentRegionName = sf.ui.proTools.windows.whoseTitle.is('Name').first.groups.whoseTitle.is('Name').first.textFields.first.value.value;
                          let part1 = currentRegionName.split("_")[0];
                  
                  
                          sf.ui.proTools.windows.whoseTitle.is('Name').first.buttons.whoseTitle.is('Cancel').first.elementClick();
                  
                          var videoClipName = part1;
                          //clipCut()
                          alert(videoClipName);
                  
                  
                          const mainCounter = sf.ui.proTools.getCurrentTimecode().stringValue
                          let cleanMainCounter = mainCounter.replace(/[ :\+\|.]/g, '').trim()
                  
                  
                          //  if maulter is bars beats, remove last three digits, since memory locations list ignores them
                          mainCounter.match(/\|/) ? cleanMainCounter = cleanMainCounter.slice(0, -3) : null
                  
                  
                  
                          const memoryLocations = sf.proTools.memoryLocationsFetch().collection.list.filter(x =>
                              x.mainCounterValue.replace(/[ :\+\|.]/g, '').trim() > cleanMainCounter &&
                              x.name.match(videoClipName));
                  
                          try {
                              sf.ui.proTools.memoryLocationsGoto({
                                  memoryLocationNumber: memoryLocations[0].number
                              });
                               sf.keyboard.press({ keys: "cmd+v", });
                          } catch (err) { log(`End of location markers containing:\n${videoClipName}`) }
                  
                      }
                  
                  
                  
                  
                  
                      //   sf.ui.proTools.memoryLocationsSearch();
                  
                      //   sf.keyboard.type({
                      //       text: videoClipName,
                      //   });
                  
                  
                      sf.keyboard.press({
                          keys: "down",
                      });
                      sf.keyboard.press({
                          keys: "ctrl+tab",
                      });
                  
                  
                      for (let i = 0; i < myPrompt; i++) {
                  
                          sf.engine.checkForCancellation();
                  
                          clipName();
                          sf.keyboard.press({ keys: "cmd+v", });
                  
                  
                          sf.ui.proTools.waitForNoModals();
                  
                          sf.ui.proTools.memoryLocationsTempGoto({
                              tempNumber: 1,
                          });
                  
                  
                  
                          sf.keyboard.press({
                              keys: "ctrl+tab",
                          });
                          sf.keyboard.press({
                              keys: "ctrl+tab",
                          });
                  
                      };
                  
                  }
                  
                  const userSelection = sf.ui.proTools.selectionGetInSamples()
                  
                  sf.ui.proTools.appActivateMainWindow();
                  
                  sf.ui.proTools.menuClick({
                      menuPath: ["View", "Other Displays", "Clip List"],
                      targetValue: "Enable",
                  });
                  
                  sf.ui.proTools.mainWindow.gridNudgeCluster.popupButtons.whoseTitle.is('Nudge value').first.popupMenuSelect({
                      menuPath: ['Follow Main Time Scale'],
                      targetValue: 'Disable',
                  });
                  sf.ui.proTools.mainWindow.gridNudgeCluster.popupButtons.whoseTitle.is('Nudge value').first.popupMenuSelect({
                      menuPath: ['Timecode'],
                      targetValue: 'Enable',
                  });
                  
                  
                  
                  getClipNames();
                  
                  

                  So, I can pull an alert from the clip name that I get in the action, and even though it looks like it matches the name of valid markers I am not getting any result.

                  The above script is pulled from an existing script that does work (Do 'x' at each 'Airmaster' marker, etc.)

                  What am I doing wrong?

                  Randall

                  1. This here doesn't look right as match expects a RegEx but you're feeding it a raw string:

                    x.name.match(videoClipName)

                    Try instead to use contains.

                • In reply tochrscheuer:
                  RRandall Smith @Randall_Smith
                    2021-08-04 15:49:18.403Z

                    I ended up just doing the math in samples. Seems to work ok.

                  • In reply tochrscheuer:
                    RRandall Smith @Randall_Smith
                      2021-08-04 15:48:52.003Z

                      Thank You. That helps quite a bit.