Random Neurons Firing

Thoughts about Software, Networking, and Life

  • I regularly record public meetings in my town and post them on Youtube. This allows residents who could not attend the meeting in person to see what occurred. It also supports creation of accurate minutes.

    I like to add a label (showing the committee name and date) and a timecode (a running clock showing the current time in the meeting). I found a free program Shutter Encoder that does this. It works well, but has a detailed (very fussy) user interface that requires a long set of steps for every new video.

    Since I only deal with a couple variations of the recordings, I decided to write scripts that use the ffmpeg program directly to accomplish these repeated, well-defined tasks.

    I now move the video/audio files into this directory and issue a command like the one below, and wait a few minutes. The resulting video is ready for uploading to Youtube.

     sh ./AddTimecode.sh "Lyme Committee Meeting-6Feb2024" 09:58:00 

    Here are the notes I use to remind myself how to make the videos using my Mac. This information and the resulting scripts are also available on my github repo.

    NB: You may need to install the ffmpeg program. Download a pre-built binary from ffmpeg.org, or read more on that site for getting a version for your computer.

    Add image, label, and timecode to an audio track

    To convert an audio-only recording into a video suitable for uploading to Youtube, this script combines a static JPEG photo with an audio track. The repo contains a photo of the Lyme Town Offices which is used by default. To use the script:

    1. Open the audio .mp4 file with QuickTime Player, and use the Trim (Cmd-T) function to remove the dead air from the start and end of the recording, as necessary. Save as Meeting Name_ddMMMyyyy.mp4
    2. Move the resulting audio file to this folder and run this script, where hh:mm:ss is the actual start time of the recording:

    sh AddTimestamp.sh "Meeting Name and Date" hh:mm:ss

    The script looks for a .mp4 file (and the image of Town Offices) to produce an output file named Meeting Name and Date-timecoded.mov

    3. Cleanup: Move the original audio file and the resulting …-timecoded.mov file to the CompletedFiles folder so they won’t interfere with subsequent runs. Discard after they have been uploaded

    Add label and timecode to AVCHD files

    A Panasonic HDC-TM80 video camera produces a AVCHD meta-file that contains the video from a recording session. The files in an AVCHD file are within the AVCHD/BDMV/STREAM directory with filenames 00000.MTS, 00001.MTS, etc.

    Drag the AVCHD file into this folder and run this script:

    sh ./TimecodeAVCHD.sh "Meeting Name and Date" hh:mm:ss

    The script looks for a file named AVCHD and outputs file named MeetingName-timecoded.mov

    Add label and timecode to Zoom video recordings

    Sometimes, a meeting is recorded by an Owl Camera. This results in a good video with good audio, but it’s still helpful to display the meeting name and timecode at the bottom.

    Drag the Zoom file (a .mp4 file) into this folder and run this script:

    sh ./TimecodeZoom.sh "Meeting Name and Date" hh:mm:ss

    The script looks for any file with a .mp4 extension and outputs file named MeetingName-timecoded.mov

    Background Information

    StackOverflow/SuperUser and similar sites are your friends.

    So is ChatGPT. I gave it this initial prompt, “give me a ffmpeg command to read a .mts file and add a label and a timecode and output a .mov file. Be sure the audio of the .mts file is preserved.” and iterated to get the final commands.

    Experiments with ffmpeg From: https://superuser.com/questions/1041816/combine-one-image-one-audio-file-to-make-one-video-using-ffmpeg

    1. Accepted answer: ffmpeg -loop 1 -i image.jpg -i audio.wav -c:v libx264 -tune stillimage -c:a aac -b:a 192k -pix_fmt yuv420p -shortest out.mp4

    ffmpeg -loop 1 -i Lyme-Town-Hall-Offices-cropped-1024.jpeg -i SB-20231130.mp4 -c:v libx264 -tune stillimage -c:a aac -b:a 192k -pix_fmt yuv420p -shortest 11Nov2023-first.mp4

    Takes a while to encode

    1. PJBrunet and Kokizzu: ffmpeg -r 1 -loop 1 -y -i 1.jpg -i 1.m4a -c:a copy -r 1 -vcodec libx264 -shortest 1.avi

    time ffmpeg -r 1 -loop 1 -y -i Lyme-Town-Hall-Offices-cropped-1024.jpeg -i SB-20231130.mp4 -c:a copy -r 1 -vcodec libx264 -shortest 11Nov2023-second.mp4

    ~5 seconds; result 18.6mbytes. Creates file that cannot be opened by QuickTime Player

    1. Same as #2 with image that has odd number of pixels

    ffmpeg -r 1 -loop 1 -y -i Lyme-Town-Hall-Offices-cropped-1024-1.jpeg -i SB-20231130.mp4 -c:a copy -r 1 -vcodec libx264 -shortest 11Nov2023-three.mp4

    Blows big chunks with “odd dimensions” error

    1. Same as #2 with odd number of pixels, using -pix_fmt yuv444p right before name of output file.

    time ffmpeg -r 1 -loop 1 -y -i Lyme-Town-Hall-Offices-cropped-1024-1.jpeg -i SB-20231130.mp4 -c:a copy -r 1 -vcodec libx264 -shortest -pix_fmt yuv444p 11Nov2023-four.mp4

    Fast (takes 5-10seconds). Creates file that cannot be opened by QuickTime Player

  • We promise not to fix it if we don’t know it’s broke. My mentor at Dartmouth College, Stan Dunten, said this all the time.

    That’s why I always send in short reports to webmaster@…. mentioning something that didn’t look right. Most of the time it’s something silly. And sometimes I get a heartfelt “Thanks!” because I pointed out a real problem.

  • I was playing with ChatGPT and Klavier.ai the other day. It purports to import documentation so that you can ask questions about it. I let it read in the PRQL Language Book, and you can see the rest of my experimentation at the PRQL site.

  • I often hear people saying they have bad responsiveness / high latency on their high-speed internet connection. Even though they have a contract for 500 mbps+ service from their ISP, a speedtest shows high ping times when the link is loaded.

    The answer is well-known – install a router with SQM that knows how to control latency (“bufferbloat”). But… they counter, a router to handle my high-speed network is expensive! And that blows out my budget!

    I posted a contrarian viewpoint in a post on the OpenWrt forum:

    Save money and purchase a 250mbps to 350mbps connection and use good SQM (for example, the IQrouter v3, or any reasonable performance OpenWrt compatible router.)

    Unless you’re unusual, and the transfer speeds of your bulk up/downloads are unacceptable, it’s likely that a lower speed to your ISP with a modestly-priced router that controls latency will make you just as happy.

    Not only will you save money with a less-expensive router, you save every month with a lower ISP bill.

  • Many people are surprised to hear that their web browsing doesn’t have to be crowded out by intrusive ads. There is an entire industry of “ad blockers” and “content blockers” that claim to fix this.

    uBlock Origin is the one I use. Its author, “gorhill” states that it’s Free. Open-source. For users by users. No donations sought.

    Accept no substitutes! The other products carry names that combine the words “ad”, “block”, “guard”, “plus” and a whole host of synonyms. And I wouldn’t trust any of them – they all come with with strings (or fees) that make them less than perfect.

    For Chrome or Firefox:

    • To install uBlock Origin, Google “ublock origin” (use that exact phrase) and then click the link for “ublockorigin.com
    • Follow the instructions

    For Safari:

    • uBlock Origin is no longer available for Safari
    • Use AdGuard for Safari. It’s also free, and seems to follow the same ethos as uBlock Origin. (Tested with current Safari on 12Feb2023).
    • For a state of uBlock Origin for Safari, and the recommendations that lead me to AdGuard for Safari, see https://github.com/el1t/uBlock-Safari/issues/158

    For Other Browsers:

  • I can no longer bring myself to write bare SQL – PRQL makes building queries so easy.

    I have become enamored with PRQL – Pipelined Relational Query Language, pronounced “Prequel”. It’s a simple, powerful, pipelined SQL replacement.

    From the PRQL Github page: Like SQL, it’s readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it compiles to SQL.

    I wrote a blog post over on the prql-lang.org site that tells how to make SQL queries “look good” using PRQL. See more »

  • I had some data in SQLite, and wanted to sort on the columns (and on a pair of columns). I really like DB Browser for SQLite because it’s free, powerful, has a nice GUI. It turns out that a stable sort on multiple columns is easy, but not well-documented. Here’s the how you do it:

    • Open a SQLite file in DB Browser for SQLite
    • In the Browse Data tab, click a column head to sort by that column
    • Click it again to reverse-sort
    • To sort by multiple columns, Control-Click (Command-click in macOS) on a second column heading. You’ll see small numbers next to the field names to indicate their order.
    • Ctl/Cmd-click a second time to reverse-sort that column
    • Click another column head to clear the previous selection(s)
    • Here’s a video that shows how it works: https://youtu.be/KMowPpzZ2Vc
  • I recently gave a talk to the local Dartmouth-Lake Sunapee Linux User Group describing some similarities between Docker and Ansible.

    Both give you a repeatable configuration by executing some sort of script of actions. Here is a link to the slides I used for the talk. https://docs.google.com/presentation/d/1W_vEPSZ-rhareK5q56pd10m2Y7JT2mc-LxrHAE6yaSI/edit#slide=id.g14247b9f80b_2_75

    Update: March 2023 – I recently learned about Dev Containers that provide the ability to bundle up all the tools required for a development environment. The PRQL project is using it to set up a complicated Rust/Javascript/Hugo/mdbook/Python environment.

  • What a crock! How badly documented can a facility be? There are lots of tutorials on the basics of Microsoft Mail Merge, but there is a dearth of information for the INCLUDEPICTURE facility to include images, especially if the images are retrieved from a link/url.

    That said, I needed to use it for a project to create a document that plucks up variables from a spreadsheet and drops them into a pretty template for each page. I wrote this note so that the next time I need to revise the document (and stumble over this process), I’ll have these notes. I’m not claiming this is the only way/best way, just that it worked for me. I think I described everything, but please let me know in the comments if I’ve missed something.

    I had the following hassles:

    • Word uses voodoo magic to accomplish this. [The documentation for how to include images in a mailmerge document is terrible (at least, my google-fu wasn’t strong enough) and it’s worse for images specified by URLs.]
    • You need to use (poorly-documented) keyboard commands to type the commands
    • Once I had images inserted into the document, I needed to figure out how to adjust their size.

    I succeeded – here’s what I did…

    1. Create an Excel spreadsheet with links (URLs) to the desired images.
    2. The Excel document should have headers in the first row that describe the data. If you’re retrieving images from a website, you’ll need a field with the links.
    3. Create a mailmerge document in Word. To do this:
      • New Document, then from the Ribbon:
      • Click the Mailings tab
      • Click Start Mail Merge and choose Letters
      • Click Select Recipients – you’ll need to choose the Excel document (and proper tab) that has the data to be merged
      • Ignore the “Filter recipients…” stuff
    4. Click “Insert Merge Field” in the Ribbon and select your fields. The field name appears within «…».
    5. They’re voodoo magic. They’re kind of cool. When you click the Preview Results button (in the Ribbon), you’ll see your mail merge results, with substitutions of the live data for your «variables»
    6. Click Preview Results again to un-preview results and continue editing the document.

    More voodoo magic

    1. If you type Alt-F9 Ctl-F9 (Option-Fn-F9 on Mac), the «variables» in the document turn into their underlying representation: «Field1» turns into { MERGEFIELD Field1 } It’s editable text.
    2. Don’t do it. It’ll leads to tears. But at least you can see how these are formatted.

    Adding an image…

    1. If you want to insert an image (say, using a URL from that Excel file) you need to do even more voodoo magic. The format is straightforward, but a bit gnarly: { INCLUDEPICTURE { MERGEFIELD Name-of-URL-Field } * MERGEFORMAT \d }
    2. BUT… You can’t just type that string: remember, the { ... } are voodoo magic.
    3. Type Alt-F9 (Cmd-F9 on a Mac) to insert an empty set of braces in the document. Then fill in the text between. If you need a new set of braces inside, just type another Alt-F9 (Cmd-F9) in the right place, and keep typing ’til you enter the correct command.
    4. Preview Results to see if it’s working.

    Resizing an image

    1. This is Microsoft Word. So there are no rules. Actually, there are, but they’re not known by mere mortals. (Sorry for the snark.)
    2. The images will be any size they want to be.
    3. To constrain the images, I found it useful to insert a one-by-one table, and let the content fill the cell. To do this, insert a 1×1 table, and adjust the size of the cell with Table Properties
      • I set the Preferred Width (Table tab)
      • I set “Specify Height” (Row tab)
      • I set “Automatically resize to fit contents” (Options button)
    4. I then copy/pasted the voodoo magic for the image into the cell.

    Finishing up…

    1. OK – You think you’re almost done. Maybe.
    2. The Finish and Merge button (in the Ribbon) actually performs the mail merge.
      • Edit Individual Documents seems to create a new Word document containing a concatenation of all the mail-merged pages. You can save this (in Word or PDF format) or print it.
      • Print Documents will print, or let you save to a PDF.
    3. You may need to force-load the images, to replace the (single) placeholder image with the right ones. To do this, Select All, then hit F9. You may see a spinner while Word retrieves all the images, but they should all be there.

    Additional Thoughts

    • I was successful with Microsoft for Mac 2019 using a .docx file, despite the fact that Google brings up a lot of (old) advice about using .doc format, not the (new-fangled) .docx format.
    • Keymaps: This article gave me a breakthrough, showing how to insert the voodoo magic { } in a document https://wordmvp.com/Mac/FieldsInMacWord.html
    • It’s easiest if the images are all the same size, to minimize resizing effort.
    • I think the \d option of the INCLUDEPICTURE command causes Word not to save the images in the document, making the source file considerably smaller.
    • I could not find documentation about what the \* MERGEFORMAT string does. It works for me.
    • Let me know in the comments if you find find out more. I’ll update this article.
  • I continue to enjoy using Docker to encapsulate developer tooling so that it doesn’t pollute my laptop with varying versions of software I don’t use regularly. (See Jonathan Bergknoff’s Run More Stuff in Docker and Andrew Welch’s Docker for all the things for further justification.)

    In addition to using Andrew Welch’s vitejs-docker-dev project for Javascript development, I converted a couple of my personal projects to use a Dockerfile. I have submitted PRs to the upstream repo’s to incorporate the Dockerfile – we’ll see if they are accepted.

    TL;DR Using a Docker container for these tools doesn’t really get in the way. Startup may be slightly slower (adding a second or so), but otherwise these tools run plenty fast. Plus, Docker eliminates a whole raft of hassles getting the software installed, and maintaining it across OS upgrades (say, on my laptop). I’m content.

    Update: I am now contributing to the PRQL project. They have a highly-developed Docker container that encapsulates the build tools which mean that all the versions of Rust, Javascript, and other tools are isolated from my host operating system.