bhel.py

import pdfplumber
import pandas as pd
import tempfile

def extract_bhel_data(pdf_file):
    data = []
    columns = ["Sl No", "Material Description", "Unit", "Quantity", "Dely Qty", "Dely Date", "Unit Rate", "Value", "Material Number", "HSN Code", "IGST"]
    start_si, end_si = 10, 1150

    with pdfplumber.open(pdf_file) as pdf:
        for page in pdf.pages:
            text = page.extract_text().splitlines()
            for line in text:
                parts = line.split()
                try:
                    si_no = int(parts[0])
                    if start_si <= si_no <= end_si:
                        material_desc = " ".join(parts[1:3])
                        unit = parts[3]
                        quantity = int(parts[4])
                        dely_qty = int(parts[5])
                        dely_date = parts[6]
                        unit_rate = float(parts[7])
                        value = float(parts[8])
                        material_number = parts[9] if len(parts) > 9 else ""
                        hsn_code = parts[10] if len(parts) > 10 else ""
                        igst = parts[11] if len(parts) > 11 else ""
                        data.append([si_no, material_desc, unit, quantity, dely_qty, dely_date, unit_rate, value, material_number, hsn_code, igst])
                except (ValueError, IndexError):
                    continue

    df = pd.DataFrame(data, columns=columns)
    temp_file = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx")
    df.to_excel(temp_file.name, index=False)
    return temp_file.name