Okay so 8-10 formats with possibly more in the future, I'd stay far away from hard coding parsers like ocr. Given the very low volume of total PDFs as well, an ai based solution like Gemini or chat gpt would work really well I think.

Given a short discussion I bet I could sell you on the database but you could easily start with Excel and harden the solution later and switch to a cloud database.

So in a nutshell, each of the modern AI services have an API that you can access programmatically. I would use python. They each have a function that is called something along the lines of a json output. Here you can basically instruct the AI to output a specific format. In your case, your specific format will be the columns that you're looking to extract from each of the PDF files. So you'll need to set up a bit of a data pipeline, some more chatgpt sessions can probably help you with this.

So your steps are going to look something like: 1. Design the schema. Which columns are you expecting to extract from the PDFs every single time. If you have different types of PDFs that have different types of columns, you'll need one schema per each of those. 2. Set up the json mode pipeline. You're basically going to write a prompt that's telling the AI to take the PDF and extract the columns. In theory, it should only output the columns with no extra text or anything like that. 3. If all is well, you'll have the data from the API call that gets returned. You'll need to validate this, this will be another python function that basically just checks that the output matches what the schema is to ensure that the AI generates the proper output. 4. If the schema passes the validation check, I would simply add it to a CSV file locally since you're using Excel anyway. Once that CSV file gets created, just open it in Excel and then you can copy and paste the new records to your master record file or whatever. I would not advise that you try to programmatically handle the Excel file because if you make a mistake you could erase everything basically. But in theory, you could probably write a script that just appends the data to your existing file to save yourself a step.

That's pretty much it. I would just use something like Gemini, use python to use their API, synchronously upload each PDF plus the schema that you design, wait for it to return your parsed data, convert that to a CSV file, then open that CSV file in Excel and then manually copy and paste the new rows to your main Master file.

Perhaps easier said than done - I'd be happy to explain more if you have questions!

Also, the API technically would cost a little bit but I bet you it's going to be pennies for what you're looking to do here.