Cracking PDF Catalogs: AI to Airtable for SKUs and Prices

Okay, so here's a problem that's probably familiar to anyone dealing with physical goods and a bunch of different suppliers: vendor PDF catalogs. For ages, these things were a huge pain point in my small e-commerce operation. We'd get dozens of them – some slick, some scanned, some just plain ugly. Each one was a unique snowflake of inconsistent formatting, and my team's job was to manually pull out SKUs, product descriptions, and prices into our Airtable database. It was a grind. Seriously, soul-crushing. We're talking hours spent copying, pasting, correcting typos, and always, always being behind on updates. We needed a reliable way to automate this, desperately. What I Tried (and What Broke) My first thought was, "Python to the rescue!" I've always been a fan of scripting tedious tasks. I dove into libraries like pdfplumber and PyPDF2 (before pypdf was the main player). The idea was simple: extract text, find patterns, and get the data. python import pdfplumber This often just gave me jumbled text or failed on complex layouts with pdfplumber.open("catalog.pdf") as pdf: for page in pdf.pages: text = page.extract_text() tables = page.extract_tables() # This was the real heartbreak # ...then regex magic to try and find SKUs/prices It worked for the most consistent, well-structured PDFs – maybe 20% of our inbound catalogs. But the moment a vendor switched from a neat table to text laid out in columns, or embedded product data within images, my scripts completely fell apart. I spent two solid days trying to craft robust regex patterns for each vendor. It was a constant game of whack-a-mole. The error message Table extraction failed for page X: could not find any explicit or implicit lines became a familiar, unwelcome friend. I even briefly considered converting PDFs to images and running OCR, but the quality wasn't consistent enough for reliable data extraction across all the weird fonts and layouts we encountered. Then there were the template-based SaaS tools. They were better, requiring me to define a 'template' for each PDF layout. But again, a new vendor or a catalog redesign meant more manual setup and a subscription cost that added up fast for an indie operation. What Actually Fixed It: Unleashing the LLM I'd been playing around with Large Language Models (LLMs) for other tasks and thought, "Could an LLM just read the PDF and tell me the SKU, price, and description?" The answer, after some serious tinkering, was a resounding yes, but not directly with the PDF itself. Here's the approach that finally clicked: Extracting Raw Text: Forget pdfplumber for table parsing. The simplest, most reliable first step was to just get all the visible text out of the PDF. pypdf (the modern, maintained version of PyPDF2) is great for this. python import pypdf reader = pypdf.PdfReader("vendor_catalog.pdf") text_content = "" for page in reader.pages: text_content += page.extract_text() + "\n\n" # Add extra newlines for separation This gives you a big, often messy, string of text representing the entire document. Prompting the LLM: This was the crucial part. I fed this raw, often unstructured, text into an LLM. I'm using a self-hosted llama-3-70b-instruct instance via Ollama – saved me a ton on API costs. The prompt needs to be extremely clear and specify the desired output format. You are an expert at extracting product information from vendor catalogs. From the following text, extract the SKU, product description, and price. The product description should be concise and accurate. The price should include the currency symbol and be in a consistent format (e.g., $XX.XX). If multiple products are listed, extract details for EACH product, even if not in a table. If you cannot find a specific piece of information, respond with "N/A". Respond in JSON format as an array of objects. Example Output: [ {"sku": "ABC-123", "description": "Widget Deluxe", "price": "$19.99"}, {"sku": "XYZ-456", "description": "Gadget Pro", "price": "$299.00"} ] --- START CATALOG TEXT --- {text_content} --- END CATALOG TEXT --- This worked like magic. The LLM, with its understanding of language and context, was able to parse the varying layouts, infer relationships between product names, SKUs, and prices, and output a structured JSON list. It wasn't 100% perfect on the first pass – maybe 90% accuracy – but good enough for a quick human review and correction. Airtable Integration: From there, it was straightforward. A simple Python script takes the JSON output and uses the pyairtable library to push the extracted products into our Airtable base. I added a Review Status field that defaults to 'Needs Review' for new AI-extracted entries, making the human verification step super clear. The Payoff What used to be a half-day ordeal for a complex 20-page catalog now takes about 30 seconds per PDF, end-to-end. My team is no longer dreading catalog updates. This approach handles wildly different layouts, even from new vendors, without me needing to rewrite regex or define new templates. It’s effectively 'set it and forget it' for the majority of our PDFs. I'm still refining the prompt, maybe adding more few-shot examples for edge cases, but honestly, this felt like finally cracking a problem that had been nagging me for years. It's a huge boost to our productivity and just makes life easier.