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