Automated Bulk PDF Data Extraction & Intelligent Mapping Engine 

The project: Automated Bulk PDF Data Extraction & Intelligent Mapping Engine 

The challenge: The client was manually processing high volumes of daily RTV (Return to Vendor) PDF reports. This was a time-consuming, error-prone process that required cross-referencing store codes with a separate database to identify store names and locations. 

The Solution:

• Automated Pipeline: Built a Power Query-based system that monitors a local folder, automatically "sweeping" and extracting data from every new PDF dropped inside.

• Intelligent Mapping: Integrated a relational mapping logic that instantly pairs raw Store Codes with their corresponding Store Names and Ship-to-Party details.

• One-Click Workflow: Designed a "Refresh-All" master dashboard in Excel, eliminating manual data entry and reducing processing time from hours to seconds.

Project Workflow: Automated PDF Data Extraction & Mapping

• Step 1: Data Ingestion & Bulk Loading
  The system is configured to monitor a specific local or cloud folder where daily RTV PDF files are stored. Using Power Query's "Folder" connector, the engine automatically detects and loads all files simultaneously, eliminating the need to open PDFs individually.

• Step 2: Unstructured Data Extraction
  The engine applies custom transformation logic (M Language) to parse the unstructured PDF layout. It identifies and extracts key data points, including Store Codes, Return Order Numbers, Barcodes, and Quantities.

• Step 3: Relational Mapping & Data Enrichment
  The raw extracted data is automatically merged with a "Mapping Master Sheet". This step uses the Store Code as a unique identifier to instantly link and populate the correct Store Names and Ship-to-Party details.

• Step 4: Automated Cleaning & Formatting
  The system performs automated "data scrubbing"—removing duplicates, correcting date formats, and ensuring numerical consistency—to transform the raw output into a clean, analysis-ready table.

• Step 5: One-Click Refresh & Reporting
  All logic is encapsulated into a single "Refresh All" command in Excel. This triggers the entire pipeline, updating the master dashboard in seconds and providing the client with an immediate, accurate view of their logistics data.