Personally I would give the following a try:
- Install python and the library xlrd once python is installed you can do this with
pip install xlrd.
- On each USB stick have a file that uniquely identifies that stick and another that lists the files and their MD5 values, (we will get to why a little further on), currently on the stick
- In the spreadsheet, assuming that it is organised with one column for each location and one row for each item, have a, possibly hidden, row that gives the same identifiers as the USB sticks have.
- In the spreadsheet have a second sheet that lists the sources for the content, again each with a unique identifier the origin path, the destination path and the MD5 of the original file.
- Make sure that the unique identifiers match those in a, possibly hidden, column on the spreadsheet.
- Write a little python script to:
- Get the date/time on the Excel spreadsheet file,
- parse the spreadsheet page 2 into a dictionary of the unique media identifiers to their MD5, origin & target locations plus
- to parse page 1 into a dictionary of the unique USB identifiers to the unique media identifiers that are required, i.e. those that have True/Yes/1 for that media item in the column for that USB device.
- Save all 3 data structures into a pickle file for later use.
- Write another python script to read the spreadsheet file date/time from both the pickle file and the spreadsheet and if they don't match to call your first script and then re-read the pickle file this will make sure that your data is current & correct.
- You second script should then run through a list of potential USB locations, (typically E:, F:, etc. on Windows), looking for the file with the USB drive identifier in and for each that it finds one it can launch a separate thread/process, see multiprocessing, to:
- read the current contents file,
- compare it to the dictionary entry for that USB drive,
- delete any files on the USB drive that are either not in the dictionary entry, or have the wrong MD5 value - this value is used so that file which have been updated but not renamed will get replaced and then
- copy to the USB drive the files that were missing or have a different MD5 value.
- Finally it should re-write the contents file to the USB drive.
Note I have assumed that your spreadsheet is organised in columns for each location, (i.e. USB device), and rows for each media item but obviously you could swap these around if your spreadsheet is organised otherwise.
An experience python programmer could probably knock this up in an hour or so but, with the steps above, a total beginner should be able to manage it in a day.
Using multiprocessing means that up to the number of cores your machine has available can be used at once, one for each USB found. Deleting the mismatched MD5 files first, at the same time as the no longer required files, ensures that fresh copies are installed but maintaining a list of the current content on the drive will minimise the operations that need to be performed and make the process as fast as possible.
You will probably also wish to have a create_usb script which will take the drive letter that a new USB is connected to and a new, (or reused if one of the current devices has been lost or damaged), unique USB ID and then:
- formats the device
- creates the identifier file plus a blank table of contents
- Invokes the same process as above to populate the contents.
Python & the xlrd library are Free, Gratis & Open Source. Both are cross platform and do not need a copy of Excel to work.
Once you have the above scripts you could even consider putting the excel spreadsheet and media content onto a network drive and connecting some Raspberry Pi 2/3 devices, to the same network. The Raspberry Pi is very reasonably priced and has a network connection plus 4 USB ports so each device could handle several drives at once. You python script with a couple of minor changes, (USB drives do not have letters under Raspbian, the network drive would have to be accessed plus I would suggest that if all jobs are done it should wait a short time then re-scan the potential drives), would then be able to do the job unattended possibly sending out a mail when all/each connected drives has been done or using the GPIO to light an LED for each socket when done. This would probably be slower for each individual drive but given the price of them, (currently about £40/$50), for a starter kit with a card, case, PSU & uSD card with the OS and cables or you can stack them), you could massively parallel your process. The case below would stack 4 Raspberry Pi boards potentially giving 16 sticks being written at once for under $300 outlay.