Server:Server Status

Bash Script to Clip Ripped Home Movies

Bash script I wrote to assist in clipping home movies. Still some more to do, but here's a summary.

Particularly useful was the code I used to find the difference under bash between start and end points using date...and keeping the result in a format ffmpeg could understand.

#!/bin/bash

# Title: HomeMoviesClipper.sh
# Description: Use ffmpeg to split raw home movie captures into clips
# Original Author: Joseph Gullo (surfrock66) (surfrock66@surfrock66.com)
#
# This script works with a very specific set of constraints and formats to 
#  take raw video captures and split them into clips of events.  Each of 
#  these movies was captured, using a Dazzle USB video capture device, 
#  into h264 video and mp3 audio, using VLC.  These original tapes were 
#  typically close to 2 hours long, however capture was often left to run, 
#  creating videos that were 8+ hours long.  The capture device produced 
#  video that was 480x480 and audio that only contains 1 channel (recorded 
#  through stereo), and the output video will retain this.  The desired 
#  format is high-bitrate h264 and aac audio, encapsulated in .mp4 files. 
#  This script also works within a predictable directory environment 
#  because of how specific the task is, and because I'm a lazy bastard 
#  that doesn't want to code bunch of directory logic.  The script first 
#  queries the input file, then works on this file recursing the steps 
#  for each clip.  Each clip requires an output name, a start timecode 
#  (formatted hh:mm:ss.nnn) and an ending timecode.  This is part of 
#  some of the headache, as ffmpeg processes clips with a start time 
#  and a duration, meaning you have to do the difference math yourself.  
#  I take care of this in there.  After producing a clip, the script 
#  will ask the user if they want to create another clip from the same 
#  source file, at which it recurses through a prompting loop.  The 
#  script also can be run from the command line...however in CLI mode, 
#  the intent is to NOT allow the script to re-prompt for additional cips 
#  from the same source video.

# The script can optionally take 4 command line arguments:
#  1) The filename for the source video, without extension
#  2) The desired resulting clip name, without extension
#  3) The start timecode for the clip, format hh:mm:ss.nnn
#  4) The end timecode for the clip, format hh:mm:ss.nnn
#
# Variables:
#  INPUTFILE - Input video filename, without extension
#  OUTPUTFILE - Clip output filename, without extension
#  STARTTIME - Clip starting timecode, format hh:mm:ss.nnn
#  ENDTIME - Clip ending timecode, format hh:mm:ss.nnn
#  NANODIFF - Difference between start and end timecode, in milliseconds
#  NUMSECS - Timecoe difference in whole seconds
#  NUMNANO - Timecode difference remainder in milliseconds
#  REPLY - Flag for checking if the script should create another clip
#  CLIFLAG - Flag set if command line mode is detected, to prevent prompting
#

# Prompt the user for all parameters, by asking for the source clip name, then calling
#  the function which prompts for the clip info.  This can safely call the clip-info 
#  prompting function, as this should only be used once per execution, if at all.
PROMPTFULL() {
  echo "---What is the source video name? (No Extension) (Inside /home/surfrock66/Videos/HomeMoviesRaw/):"
  # First point of possible declaration for INPUTFILE
  read INPUTFILE
  # Execute the PROMPCLIP function
  PROMPTCLIP
}

# Prompt the user for the clip name, start time, and end time.  
PROMPTCLIP() {
  echo "---What is the output filename for this clip? (No Extension):"
  # First point of possible declaration for OUTPUTFILE
  read OUTPUTFILE
  echo "---What is the timecode for the start of this clip? (##:##:##.###):"
  # First point of possible declaration for STARTTIME
  read STARTTIME
  echo "---What is the timecode for the end of this clip? (##:##:##.###):"
  # First point of possible declaration for ENDTIME
  read ENDTIME
}

# This function handles the timecode calculations to find the clip duration, 
#  then executes the actial ffmpeg command.
PROCESSVID() {
  # Calculate the actuall difference, in milliseconds (but we'll call them 
  #  nanoseconds, because that's how the date command works) between the 
  #  start time and end time.  This relies heavily on the "date" command.  
  #  It can convert formatted timecodes between different format 
  #  conventions, but to go from something resembling a standard video 
  #  editing timecode to a raw nanosecond count is a PAIN IN THE ASS.  
  #  This strips out the amount of hours in milliseconds, then the number 
  #  of minutes in milliseconds, then the number of seconds in milliseconds, 
  #  then the number of nanoseconds in milliseconds.  It then adds them 
  #  together, creates a full millisecond count for the start an end time, 
  #  and then finds the difference, in milliseconds.
  NANODIFF=$(($(($(($(date -d $ENDTIME +%-H) * 3600000)) + $(($(date -d $ENDTIME +%-M) * 60000)) + $(($(date -d $ENDTIME +%-S) * 1000)) + $(($(date -d $ENDTIME +%-N) / 1000000)))) - $(($(($(date -d $STARTTIME +%-H) * 3600000)) + $(($(date -d $STARTTIME +%-M) * 60000)) + $(($(date -d $STARTTIME +%-S) * 1000)) + $(($(date -d $STARTTIME +%-N) / 1000000))))))
  # Convert the resulting difference, in millisecodns, into seconds
  NUMSECS=$(($NANODIFF / 1000))
  # Convert the reaminder of the resulting difference, in milliseconds,
  #  to milliseconds
  NUMNANO=$(($NANODIFF % 1000))
  # Print out the ffmpeg command that is generated, mostly for debugging
  echo "\nCommand: ffmpeg -i \"/home/surfrock66/Videos/HomeMoviesRaw/$INPUTFILE.mp4\" -ss $STARTTIME -t $NUMSECS.$NUMNANO -vcodec libx264 -b 1500k -s 480x480 -acodec libfaac -ab 192k -ac 1 -threads 8 \"/home/surfrock66/Videos/HomeMoviesRaw/$INPUTFILE/$OUTPUTFILE.mp4\"\n"
  # Execute the ffmpeg command, this is the big kahuna.
  ffmpeg -i "/home/surfrock66/Videos/HomeMoviesRaw/$INPUTFILE.mp4" -ss $STARTTIME -t $NUMSECS.$NUMNANO -vcodec libx264 -b 1500k -s 480x480 -acodec libfaac -ab 192k -ac 1 -threads 8 "/home/surfrock66/Videos/HomeMoviesRaw/$INPUTFILE/$OUTPUTFILE.mp4"
}

# Start of the main script.  Section 1: Input validation
#  Check that there is a first parameter.  If not, Prompt the user for the
#   parameters.  If there is one, validate the rest of the params, then 
#   either prompt the user, or use the passed params.
if [ -z "$1" ]
then
  # Call the prompt function, including asking for the source video
  PROMPTFULL
else
  # Assuming the first parameter exists, check for parameters 2,3, and
  #  4.  I'm not validating these strings, mostly because ffmpeg will 
  #  flip out if there's something wrong with them...as you know, it's
  #  quite verbose.
  if [ -z "$2" -o -z "$3" -o -z "$4" ]
  then
    # If any parameters are invalid, prompt for them all. Even if param 1
    #  is successfully detected, re-ask for it.  
    echo "--Parameters are incomplete, switching to prompt mode."
    PROMPTFULL 
  else
    # Pass all the command line parameters to the variables
    INPUTFILE="$1"
    OUTPUTFILE="$2"
    STARTTIME="$3"
    ENDTIME="$4"
    # Change the flag to indicate later that this initiated by CLI
    CLIFLAG="yes"
  fi
fi

# Create the default output directory, if it's not already created
if [ ! -d /home/surfrock66/Videos/HomeMoviesRaw/$INPUTFILE ]; then
  mkdir /home/surfrock66/Videos/HomeMoviesRaw/$INPUTFILE
fi

# Now...initiate the final video processing
#  Declare and initiate the REPLY flag...can be set to anything byt n/N
REPLY="y"
# As long as the REPLY flag isn't n or N, repeat the loop
while [ "$REPLY" != "n" ] && [ "$REPLY" != "N" ]
do
  # Call the function PROCESSVID
  PROCESSVID
  # Check to see if the script was run with command line parameters,
  #  If not, issue a bunch of logic to see if we want to re-run the
  #  code for new clips
  if [ -z "$CLIFLAG" ]
  then
    # Now that we know the script was run in prompt mode, ask the user
    #  if they want to make another clip, repeating this loop
    echo "---Would you like to create another clip from this input video? {Y/n}"
    read REPLY
    if [ "$REPLY" != "n" ] && [ "$REPLY" != "N" ]
    then
      PROMPTCLIP
    fi
  # If it was run by command line, essentially abort the loop,
  #  and the script.
  else
    REPLY="n"
  fi
done
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Critical Excel Macro

I use excel constantly, and some things I require are freezing panes, autofitting the columns, adding auto filters, and standardizing the row heights. Here's the code for the macro I use, and I shortcut it with Ctrl+J.

Sub MakeSpreadsheetReadable()
'
' MakeSpreadsheetReadable Macro
' Macro recorded 5/23/2011 by Gullo, Joseph T. *HS
'
' Keyboard Shortcut: Ctrl+j
'
    Rows("1:1").Select
    Selection.AutoFilter
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.Zoom = 70
    Cells.Select
    Cells.EntireColumn.AutoFit
    Selection.RowHeight = 12.75
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Minecraft Skins

The wife and I are playing some minecraft, look how adorable we are:

Mrs. Pinkin (I can only embed one of us at a time to view :( )

Surfrock66

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

My Retinas

I went to the optometrist and they took pictures of my retinas, check them out:

Left RetinaLeft EyeRight EYeRight Eye

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Excel Formula: Double (And More) Conditional Sums

Let's say you have an excel spreadsheet for guys that sell billboard space with 3 columns: Seller, Billboard Location, and number of days. Let's say you wanted to find out how many days John sold a certain billboard for. Excel doesn't have an elegant way built in for double-conditional sums. There's a plugin you can install, but the formula is UGLY, and really doesn't work past 2 variables. If you wanted to do a triple-conditional sum, you're basically out of luck.

The solution I've come up with uses 3 features:

  1. SUMIF: SUMIF is a conditional sum formula based on a single variable. It takes 3 arguments: The range of cells to check against, the value you're searching for, and the corresponding range of cells to add if the first cell meets the conditions. Read about it here.
  2. CONCATENATE: This function just combines text into one big text. If A2="Joe" and B2="South St." then CONCATENATE(A2,B2)="JoeSouth St."
  3. Filter Uniques: Excel can take a list of items and separate it out into JUST the unique entries; this will be critical to pare down the unnecessary data. Select your range, go to the Data menu, then advanced filter, then choose your range (just the Unique ID column) and check the box for "Unique Records Only." This feature doesn't work on formulas; what you must to is select your whole column with the formula in it, copy it, then instead of pasting it in the same place chose "paste special" and select values...this just leaves the formula's result in place.

First, we need to take all of our indexes and concatenate them, so that we have a unique field for each possible combination. This works for any number of indexes. In this case, like I showed above, we're concatenating the name and location. Then, you can run the SUMIF command with the unique column we created being the range to check. Let me create an example below:

RepBoardDays
Sold
Unique IDSUM
JoeOne5=CONCATENATE(A2,B2)=SUMIF(D:D,D2,C:C)
JoeOne10=CONCATENATE(A3,B3)=SUMIF(D:D,D3,C:C)
JoeOne12=CONCATENATE(A4,B4)=SUMIF(D:D,D4,C:C)
JoeThree6=CONCATENATE(A5,B5)=SUMIF(D:D,D5,C:C)
ChrisTwo8=CONCATENATE(A6,B6)=SUMIF(D:D,D6,C:C)
ChrisOne9=CONCATENATE(A7,B7)=SUMIF(D:D,D7,C:C)
DaveOne4=CONCATENATE(A8,B8)=SUMIF(D:D,D8,C:C)
DaveTwo2=CONCATENATE(A9,B9)=SUMIF(D:D,D9,C:C)
DaveTwo7=CONCATENATE(A10,B10)=SUMIF(D:D,D10,C:C)

This produces a table like this:

RepBoardDays
Sold
Unique IDSUM
JoeOne5JoeOne27
JoeOne10JoeOne27
JoeOne12JoeOne27
JoeThree6JoeThree6
ChrisTwo8ChrisTwo8
ChrisOne9ChrisOne9
DaveOne4DaveOne4
DaveTwo2DaveTwo9
DaveTwo7DaveTwo9

which you can then use the "Filter Uniques" function on the Unique ID column to pare down to this:

RepBoardDays
Sold
Unique IDSUM
JoeOne5JoeOne27
JoeThree6JoeThree6
ChrisTwo8ChrisTwo8
ChrisOne9ChrisOne9
DaveOne4DaveOne4
DaveTwo2DaveTwo9

So, finally, once we remove the unnecessary columns and re-title the result column:

RepBoardDays Sold Per User, Per Board
JoeOne27
JoeThree6
ChrisTwo8
ChrisOne9
DaveOne4
DaveTwo9
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -