AInvoice

Project Type: Data Engineering + Dashboarding + NLP/OCR
Tech Stack: Python, OpenAI API, Tesseract OCR, SQLite, Power BI
Project Focus: Multicurrency invoice parsing, FX normalization, interactive dashboard

---

🚀 Project Overview

AInvoice is a fully automated pipeline to extract structured data from scanned invoices or images (in multiple currencies), normalize the amounts via live FX conversion, and store the results in a SQLite database. The pipeline outputs a .csv file ready for visualization in Power BI, featuring a dashboard that tracks monthly expenses, high-value invoices, and spending per supplier.

This project combines OCR, NLP, FX APIs, and data modeling — all orchestrated in Python, and visualized in a polished dashboard.

---

🧾 Folder Structure & Components

AInvoice/
│
├── main.py               # Main execution script
├── functions.py          # OCR, API, and processing functions
├── prompt.py             # Prompt template for OpenAI extraction
├── queries.sql           # SQL file with useful queries
├── environment.yml       # Conda environment config
├── .env                  # Template for API keys (OpenAI + FX)
│
├── invoices/             # Monthly folders with invoice files (add your invoices manually here)
│   ├── 01_January/
│   ├── ...
│   └── 12_December/
│
├── PowerBI/                     # Power BI visual templates
│   ├── Dashboard_AInvoice.pbix               # Power BI clean template
│   ├── Dashboard_AInvoice (reference).pbix   # Layout reference
│   └── IMPORTANT - READ.txt                  # Dashboard instructions
│
└── invoices_export.csv          # Optional CSV export from SQLite queries for Power BI

📌 Important: Make sure the .env file is configured with your OpenAI and FX API keys.

---

⚙️ Environment Setup

1. Install prerequisites:

• Tesseract OCR

• sqlite3 (already included in most Python installs)

2. Create the Conda environment:

conda env create -f environment.yml

3. Activate the environment and install Jupyter kernel (for interactive development):

conda activate invoice-extractor
pip install ipykernel
python -m ipykernel install --user --name=invoice-extractor

---

🧠 How the Pipeline Works

1. OCR Extraction
   Each file in /invoices/ is parsed using Tesseract and OpenAI to identify fields like supplier, description, date, import, currency.

2. Currency Conversion
   A live FX rate API (like Fixer.io) is used to convert all amounts into euros.

3. Data Storage
   Results are saved into a local SQLite database (invoices.db) with automatic schema creation.

4. CSV Export
   A final invoices_export.csv is created for use in Power BI or Excel.

📌 Execution of main.py in VS Code (Interactive Window):

---

🧪 SQLite Queries

Use the queries.sql file to interact with your database. To launch SQLite in Anaconda Powershell or CMD (from the project root where invoices.db is located):

sqlite3 invoices.db
.read queries.sql

Example queries include:

• Selecting total spending

• Filtering invoices by date or supplier

• Exporting to .csv (optional)

---

📊 Power BI Integration

1. Load invoices_export.csv

Go to Home > Get Data > Text/CSV and select the file. Configure encoding as 65001: Unicode (UTF-8).

2. Handle Import Formatting

To avoid issues with decimal/comma separators and scientific notation:

1. After selecting invoices_export.csv, click Transform Data instead of Load.

2. In Power Query Editor, change the import column type to Text first.

   • When prompted, click Replace current.

3. Then open the type dropdown again and choose Using Locale....

   • Set the type to Fixed decimal number

   • Set Locale to English (United States)

This double conversion (Text → Fixed decimal with Locale) ensures proper parsing of both scientific notation and decimal separators across regional settings.

3. Close & Apply

Click Close & Apply to load the cleaned data into Power BI.

4. Format Column

In the Column Tools tab:

• Data type: Fixed decimal number

• Format: Currency

• Decimal places: 2

📌 Formatted table in Power BI:

---

📈 Dashboard Output

The dashboard shows:

• 🔢 Number of invoices

• 💰 Total spending

• 📊 Monthly trend of expenses

• 🏢 Breakdown by supplier

• 🧾 Highest value invoices

All visuals are updated dynamically based on the contents of invoices_export.csv.

📌 Dashboard Preview:

---

✅ Final Notes

• Ensure monthly folders are not empty, or they'll be skipped automatically.

• Unsupported or missing currency labels will be logged and excluded.

• Add or remove currencies from currency_map in main.py.

• The project supports adding more FX providers if needed.

• You can modify the OpenAI model in functions.py (e.g. gpt-4, gpt-3.5-turbo).

• Add or change fields by editing the prompt template in prompt.py.

---

🔐 Security Tip

Never upload your real .env file to GitHub. Always include only the .env.example template in version control.

---

🌐 Explore the Project

• 📂 GitHub repo

• 📊 Power BI dashboard

---

💬 Feedback & Contact

If you found this useful or have questions, feel free to reach out or fork the repo on GitHub!

---

Built with 💻 Python, 📊 Power BI, and 🤖 OpenAI — by Tayfur Akkaya