Scripting Video Production for data entry

Question

I'm doing freelance work producing product videos for a company that sells millions of products (sub components for telecom equipment). They want me to produce a video for each part number. The video template they've provided to me has a roll about their company, contact info, etc and in the middle is a single title that has the static text of "We buy/sell: " followed by the part number the video corresponds to. There are millions of part numbers provided to me via many spreadsheets.

I'm not a video editor/producer by trade - I do cyber security work with freelance advanced data entry such as this. I used my cyber security skills to mostly automate the process, which now looks like this:

1. Using Adobe Premiere CC, create a sequence for the part number and insert a title with the part number in it into the sequence. (~20 seconds total PER sequence). I generate a project file with several thousand sequences.
2. Batch encode all of the sequences in a single project via Adobe Media Encoder.
3. Use a script I wrote to leverage YouTube API to upload all of the video files and pull the data from the spreadsheet (converted to CSV) and match the video's filename with the corresponding line in the CSV to add the YouTube metadata for the particular file.

My time sink is just the 20 seconds per video of data entry - everything else is done via semi-automated job batching.

I'm looking for a way to automate the last remaining data entry portion. Adobe premiere doesn't reliably import formats that I can generate, such as XML. (It does, but it fails 90% of the time - title objects are offline/missing. It fails to import an unmodified XML file that it generated.)

I'm not all that familiar with other video editing/production softwares. I'm thinking I can combine the encoding and data entry portion into a process that I can script. I started looking into VirtualDub but I decided I should ask the experts before trying to invent something that has already been invented.

Answer 1

My solution to this issue is fairly complex but stable. I feel I went well beyond the initial request here.

I built a MySQL database with a table of jobs. In that table there are columns for status, the text that needs to be in each video and then further more with youtube metadata. The table has an index ID value that auto increments with each added row.

I manipulate the data via microsoft excel and save it as a CSV. I wrote a python script that reads the CSV and then imports into MySQL. I do this because the youtube metadata column in each row is stored in JSON format for ease during the upload phase.

Once in the MySQL database, I have a script that runs once daily that generates any videos listed with the status code corresponding to needing to be rendered.

I use ffmpeg with drawtext to push the text desired during the timeline desired on top of a template version of the video that has whitespace in the timeline desired.

Files are named corresponding to the Row ID# in the database to make it easier to pick up later on in the upload phase.