Actions

Copy link

Feature #133

open

RG RG

Creating Shipping Bill Bulk Uploader Python code

Feature #133: Creating Shipping Bill Bulk Uploader Python code

Added by Rajesh Gotur 8 days ago.

Status:

In Progress

Priority:

Normal

Assignee:

Rajesh Gotur

Target version:

Backlogs

Start date:

10/05/2026

Due date:

% Done:

Estimated time:

Spent time:

14:00 h

Description

User Story: Shipping Bill Bulk Upload Automation to Frappe¶

Project: Shipping Bill Extractor / Shipping Bill Bulk Uploader
Work type: Automation enhancement
Implementation version: 2.0.0
Target system: Frappe / ERPNext — Shipping Bill DocType
Evidence reviewed: shipping_bill_uploader (1).py and Shipping_Bill_Upload_Report_2026-10-07_18-25-22 (1).xlsx

1. User Story¶

As an operations user responsible for creating Shipping Bill records in Frappe,
I want to process a folder of Shipping Bill PDFs in one controlled batch, automatically extract and validate their data, create the corresponding Frappe records, attach the original PDFs, and receive a detailed Excel report,
so that I can reduce manual data entry, avoid duplicate records, verify that uploaded information was saved correctly, and quickly identify any files that require attention.

2. Background and Business Problem¶

The Shipping Bill Extractor framework already contains the PDF analysis and parsing logic needed to convert Shipping Bill documents into structured JSON. However, processing many PDFs individually and then creating records manually in Frappe introduces repetitive work and creates opportunities for missed fields, duplicate entries, incorrect attachments, and incomplete follow-up.

I implemented a Shipping Bill Bulk Uploader that connects the existing extraction logic to Frappe through its REST API. The script discovers PDFs in a folder, processes them one by one, validates the extracted JSON against the target DocType, applies duplicate checks, creates the record, reads it back for verification, attaches the source PDF, and generates a formatted Excel report.

The implementation keeps the extraction rules aligned with the existing Shipping Bill Extractor rather than introducing a separate parser with different behavior.

Objectives¶

Process multiple Shipping Bill PDFs in one run.
Reuse the existing PDFAnalyzer and ShippingBillParser extraction logic.
Select the actual Customs Shipping Bill pages rather than treating unrelated attachment pages as the source document.
Validate extracted data before sending it to Frappe.
Prevent duplicate uploads at both file-content and Shipping Bill identity levels.
Confirm that Frappe saved the expected data and source PDF.
Produce an actionable Excel report with per-file validation results, outcomes, duplicate details, and troubleshooting guidance.
Support dry-run validation without creating or changing Frappe records.

3. Solution at a Glance¶

flowchart TD
    A[Folder containing Shipping Bill PDFs] --> B[Discover files and calculate fingerprints]
    B --> C{Identical PDF already seen?}
    C -- Yes --> D[Mark duplicate in folder]
    C -- No --> E[Analyze PDF with pdfplumber]
    E --> F[Select Shipping Bill pages]
    F --> G[Extract structured JSON]
    G --> H[Validate mandatory fields and DocType schema]
    H --> I{Valid?}
    I -- No --> J[Record failure and recommended action]
    I -- Yes --> K[Check duplicate SB No + Port Code in batch]
    K --> L[Check existing Frappe records / PDF attachment]
    L --> M{Already exists?}
    M -- Yes --> N[Skip or apply configured duplicate policy]
    M -- No --> O[Create Shipping Bill through REST API]
    O --> P[Read back and verify saved record]
    P --> Q[Attach source PDF and verify attachment]
    D --> R[Build Excel report]
    J --> R
    N --> R
    Q --> R
    R --> S[Dashboard, PDF Details, Action Required, Duplicates, Guide]

The batch is processed sequentially. Each file receives a recorded outcome, and a failure for one PDF is intended to be reported without hiding the results for other files. If Frappe becomes unreachable repeatedly, the run stops early and marks the remaining PDFs as not processed rather than waiting through repeated connection timeouts.

4. Detailed Processing Workflow¶

Step 0 — Discover files and detect identical PDFs¶

The uploader scans the configured input folder for PDF files. Subfolder scanning is configurable and is disabled in the supplied configuration.

For each discovered file, the implementation calculates file fingerprints, including SHA-256 and MD5. Identical file contents are recognized even if the filenames differ.

Why this matters: filename-based checks alone cannot detect the same PDF saved under a different name.

Expected result: an identical copy is marked as a duplicate in the folder and is not uploaded again.

Step 1 — Validate and analyze the PDF¶

The PDFAnalyzer opens each PDF using pdfplumber and gathers page-level information, including:

total page count;
extractable text;
words and their positions;
detected tables;
page drawing lines;
text-based, image-only, or mixed-PDF classification;
analysis duration and word/table counts.

The analyzer uses multiple table-detection strategies to support both ruled and whitespace-aligned tables.

Important limitation: the supplied implementation uses pdfplumber only. It does not run OCR or AI. A fully scanned/image-only PDF without a selectable text layer is rejected with a clear explanation. A mixed PDF can proceed if it contains extractable text, subject to the remaining parser checks.

Step 2 — Select the actual Shipping Bill pages¶

A PDF may include unrelated invoices, packing lists, scans, or supporting attachments before or after the Customs Shipping Bill.

The parser searches for the Customs Shipping Bill identity using document-header and structural signals such as:

Indian Customs EDI System;
Central Board of Indirect Taxes and Customs;
Part I Shipping Bill Summary;
Port Code, SB No, and SB Date;
IEC/BR and CB Code.

When available, the embedded Page X Of N numbering is used to determine the relevant page range. If that numbering cannot be read, the parser uses structural section markers as a fallback.

This makes page selection depend on the document's structure rather than assuming that every PDF starts with the Shipping Bill on physical page 1.

Step 3 — Extract structured JSON¶

The uploader reuses the PDFAnalyzer and ShippingBillParser logic from the Shipping Bill Extractor framework. The extraction code is embedded in the supplied uploader and is documented as being synchronized with the framework's analyzer and parser.

The parser uses a multi-pass process:

Extract parent/header and party fields.
Extract invoice and item sections.
Extract known Shipping Bill sections.
Recover missing data from generic tables.
Recover remaining parent and child fields.
Revisit tables to enrich partial rows.
Reconcile child-table rows.
Normalize values and complete the expected schema.

The parser defines parent fields and child-table schemas, including invoice, item, manifest, challan, licence, drawback/ROSL, job, single-window declaration, supporting-document, AR4, third-party, manufacturer/producer/grower, RoDTEP, and container details.

The output is structured JSON, not a manually assembled Frappe record. The uploader can also save each extracted JSON file for troubleshooting and review.

Step 4 — Validate JSON before upload¶

Before sending data to Frappe, the uploader checks that the extracted record contains the required fields:

shipping_bill_no
shipping_bill_date
port_code

When live DocType validation is enabled, it retrieves the target Shipping Bill schema from Frappe and checks relevant constraints, including:

mandatory fields;
field types;
date, time, and number formats;
field-length limits;
Select-field options;
parent and child-table field compatibility.

Invalid JSON is not sent to Frappe. The report records the failed validation step, the reason, and a suggested corrective action.

Step 5 — Prevent duplicate Shipping Bill records¶

The uploader checks duplicates at more than one level.

Duplicate check	How it works	Intended outcome
Identical file in the folder	Compares content fingerprints, not just filenames	Skip the repeated copy
Same Shipping Bill within the batch	Uses the configured identity fields, `shipping_bill_no` and `port_code`	Process only the first matching Shipping Bill
Existing Shipping Bill in Frappe	Checks the configured identity fields against existing records	Apply the configured duplicate action
Same source PDF already attached	Uses the file content hash and existing attachment information	Avoid attaching/uploading the same source PDF again

The configured duplicate policy supports skip, update, and create. The supplied configuration uses skip for an existing Shipping Bill with the same Shipping Bill number and port code.

Step 6 — Create the Frappe document¶

After the file passes extraction, schema validation, and duplicate checks, the uploader sends the structured data to the Frappe REST resource endpoint for the Shipping Bill DocType.

The implementation supports the DocType's default naming series when no custom series is configured. The supplied report shows records created with names such as SB-26-00046.

The uploader records the returned Frappe document name and URL so that the result can be traced to the saved record.

Step 7 — Read back and verify the saved record¶

A successful HTTP response alone is not treated as sufficient proof that the intended record is correct.

The uploader reads the document back from Frappe and compares the saved data with the expected extracted data. It also checks that only one Shipping Bill exists for the configured identity.

This step is designed to catch cases where the record was created but important values or child rows did not persist as expected.

Step 8 — Attach and verify the source PDF¶

The original Shipping Bill PDF is attached to the created record when PDF attachment is enabled. The supplied configuration enables attachment and marks the file as private.

The uploader then verifies the attachment by checking the related Frappe File record, including file size/content-hash information. A record that was saved but whose read-back or attachment verification has a problem can be marked for review rather than being presented as fully successful.

Step 9 — Generate the Excel report¶

At the end of the run, the uploader creates a professionally formatted Excel workbook. The report is saved to the configured report folder; if that setting is empty, the default is the user's Downloads folder. The workbook is opened automatically unless disabled.

The report is designed to answer three operational questions:

What happened? — Dashboard summary and outcome counts.
Which file failed, and at what step? — PDF Details and Action Required.
Why was a file skipped? — Duplicates sheet and explanation.

5. Excel Reporting and User Experience¶

The workbook generated by the implementation contains five worksheets.

<title size="md">1. Dashboard</title> A summary of the run with KPI tiles, outcome breakdown, success rate, and an overall verdict. It makes it possible to assess the batch before reviewing individual files. <title size="md">2. PDF Details</title> One row per input PDF, including the extracted Shipping Bill number/date/port, exporter, Frappe record, PDF details, processing time, and PASS/FAIL/WARN/not-run indicators for each validation stage. <title size="md">3. Action Required</title> A focused list of files that need intervention, including the failed step, error explanation, recommended fix, and file path. When no action is needed, the sheet explicitly says so. <title size="md">4. Duplicates</title> Lists files skipped because the same file or same Shipping Bill identity was already processed, along with the original file and reason for skipping. <title size="md">5. Guide</title> Explains the report's statuses, the validation steps, and the corrective action expected for common failures.

The workbook uses formatting, status indicators, dashboard metrics, and a doughnut chart to make the batch results easier to interpret. It is intended to be both an audit record and an operational troubleshooting tool.

6. Implementation Evidence: 7 October 2026 Run¶

The supplied Excel report records a live upload run on 7 October 2026 at 18:25, targeting https://eapx.canofy.tech.

Run summary¶

Metric	Result in supplied report
PDFs processed in the report	7
Shipping Bill records created	6
Duplicate Shipping Bill identities skipped within the folder	1
Files needing attention	0
Reported success rate	100%

The report's 100% result means that all seven files received an expected successful or safe-skip outcome; it does not mean that seven records were created. Six records were created and verified, and one same-Shipping-Bill duplicate was safely skipped.

Example records from the report¶

Shipping Bill No.	Date	Port	Frappe record	Outcome
1020964	2026-02-25	INBLR4	`SB-26-00046`	Created
4242360	2026-06-17	INBOM4	`SB-26-00047`	Created
4494151	2026-06-26	INBOM4	`SB-26-00048`	Created
5632009	2026-08-04	INBOM4	`SB-26-00049`	Created
5963345	2026-08-15	INBOM4	`SB-26-00050`	Created
7472441	2025-12-01	INBLR4	`SB-26-00051`	Created

The report shows that the second PDF for Shipping Bill 5632009 at port INBOM4 was skipped because the same Shipping Bill identity had already been processed from an earlier PDF. It points to SB-26-00049 as the corresponding Frappe record.

For each of the six created records, the report shows successful checks for file uniqueness, PDF readability, JSON extraction, JSON validation, batch identity uniqueness, absence of an existing record, Frappe save, read-back verification, and PDF attachment verification.

What this evidence demonstrates¶

The uploader processed a batch of multiple source PDFs in a single run.
It created Frappe records and retained their record names in the report.
It identified a repeated Shipping Bill identity and prevented a second record from being created.
It verified both the saved record and its source PDF attachment according to the checks recorded in the workbook.
It generated an operational report with no files listed for follow-up in this run.

These are results from the supplied run report, not a guarantee that every future PDF or Frappe environment will produce the same outcome.

7. Configuration and Execution¶

The script centralizes its main settings in a CONFIG section and allows environment variables with matching names to override settings. Relevant settings include:

Setting	Purpose
`FRAPPE_URL`	Target Frappe site
`PDF_FOLDER`	Input directory containing Shipping Bill PDFs
`INCLUDE_SUBFOLDERS`	Whether to scan subdirectories
`DOCTYPE`	Target DocType (`Shipping Bill`)
`DUPLICATE_ACTION`	Behavior when the same Shipping Bill already exists
`DUPLICATE_MATCH_FIELDS`	Fields used for Shipping Bill identity
`ATTACH_PDF_TO_SHIPPING_BILL`	Whether to attach the source PDF
`ATTACH_AS_PRIVATE_FILE`	Whether the attachment is private
`VALIDATE_AGAINST_DOCTYPE`	Whether to validate against the live Frappe schema
`REQUIRED_FIELDS`	Minimum fields required before upload
`REPORT_FOLDER`	Excel report destination
`SAVE_JSON_FILES`	Whether to save extracted JSON for review
`LOG_FOLDER`	Log destination
`REQUEST_TIMEOUT_SECONDS`	HTTP request timeout
`MAX_RETRIES`	Retry count for read requests
`VERIFY_SSL`	SSL certificate verification
`MAX_CONSECUTIVE_CONNECTION_FAILURES`	Early-stop threshold for repeated Frappe connection failures

Installation¶

The script requires Python 3.9 or later and these packages:

python -m pip install pdfplumber requests openpyxl

Run modes¶

# Process the configured folder and upload records
python shipping_bill_uploader.py

# Override the input folder
python shipping_bill_uploader.py --folder "D:\SB"

# Extract and validate without sending data to Frappe
python shipping_bill_uploader.py --dry-run

# Do not automatically open the Excel report
python shipping_bill_uploader.py --no-open

The dry-run mode is intended for checking extraction and basic validation without writing to Frappe. Because it does not contact Frappe for live DocType schema validation, its validation scope is more limited than a normal live run.

8. Error Handling and Recovery¶

Condition	Uploader behavior / operational guidance
Corrupt, password-protected, or unreadable PDF	Mark the PDF as failed and provide a corrective action
Scanned/image-only PDF	Reject it because OCR is not implemented in this uploader
Required Shipping Bill fields are missing	Stop that file before upload and show the failed validation
Extracted value violates the live DocType schema	Do not submit the invalid payload; report the schema issue
Duplicate PDF content	Skip the repeated file
Same SB number and port appear twice in the input folder	Process the first matching file and record the later one as a duplicate
Shipping Bill already exists in Frappe	Apply the configured duplicate policy; the supplied configuration uses `skip`
Frappe rejects the API request	Record the response and provide guidance based on the HTTP status
Frappe becomes unreachable repeatedly	Stop the run after the configured consecutive-failure threshold and mark remaining files as not processed
Document saved but verification/attachment check has an issue	Flag the result for review instead of treating it as fully verified
User interrupts the run	Write a report for files processed so far

The report separates actual failures from expected duplicate skips. This distinction matters operationally: a duplicate skip protects data integrity and should not be treated as an upload failure.

9. Acceptance Criteria¶

ID	Acceptance criterion	Evidence/status
AC-01	Multiple PDFs can be processed from a configured folder in one run	Implemented in batch processor
AC-02	The existing Shipping Bill extraction logic is reused	Documented in source comments and implementation
AC-03	The parser identifies the Customs Shipping Bill page range from document structure	Implemented with header and page-number signals
AC-04	The uploader rejects image-only PDFs when no selectable text is available	Implemented; OCR is not part of this script
AC-05	Required fields are checked before upload	Implemented
AC-06	Extracted JSON can be validated against the live Frappe DocType	Implemented when live validation is enabled
AC-07	Identical PDF files are not uploaded twice	Implemented with content fingerprints
AC-08	Repeated Shipping Bill number + port code within a batch is safely skipped	Implemented and demonstrated in the supplied report
AC-09	Existing Frappe records are checked before creation	Implemented
AC-10	Successful record creation is followed by read-back verification	Implemented
AC-11	The source PDF is attached and attachment integrity is checked	Implemented; checks passed for the six created records in the supplied report
AC-12	The Excel workbook contains a dashboard and file-level details	Implemented with five worksheets
AC-13	Failures and recommended actions are shown in an Action Required worksheet	Implemented
AC-14	Dry-run mode does not write records to Frappe	Implemented
AC-15	A repeat run can safely detect already-created records under the configured duplicate policy	Intended by duplicate checks; validate on the target site before rerunning a production batch

10. Security and Operational Considerations¶

Frappe API credentials must not be committed to source control, shared in documentation, or exposed in logs.
Use environment variables or a protected secrets/configuration mechanism for API credentials. The script supports environment-variable overrides.
Because the supplied source file contains credential values in its configuration section, rotate those credentials if they are active and have been shared outside the intended secure environment.
Keep SSL verification enabled unless a documented environment-specific reason requires otherwise.
Limit the API user's permissions to the operations required by the uploader, including access to the relevant Shipping Bill and File resources.
Treat the generated Excel report and saved JSON files as business data. Store and share them according to the site's data-access policy.
Confirm duplicate policy and target site before running a batch against production.

11. Definition of Done¶

12. Outcome and Business Value¶

I implemented a bulk-processing layer around the existing Shipping Bill extraction framework so multiple PDFs can be converted into structured data and uploaded to Frappe through a controlled, auditable process.

The solution reduces repeated manual work while adding validation and safeguards around data integrity. It does not rely solely on a successful API response: it checks the extracted payload before submission, checks for duplicates, reads the saved record back, and verifies the PDF attachment. The generated Excel report gives operations users a clear view of each file's status and the action required, if any.

In the supplied run on 7 October 2026, six Shipping Bill records were created and verified, one repeated Shipping Bill identity was skipped, and no files required follow-up. The result demonstrates the bulk uploader's intended workflow for the tested batch while preserving the distinction between successful creation and safe duplicate handling.

The deliverable is a reusable PDF folder → extraction → validation → duplicate control → Frappe creation → verification → PDF attachment → Excel audit report workflow.

13. Source Artifacts Reviewed¶

shipping_bill_uploader (1).py — implementation source for the bulk uploader, extraction pipeline, Frappe API client, duplicate controls, verification, and Excel report generation.
Shipping_Bill_Upload_Report_2026-10-07_18-25-22 (1).xlsx — run evidence dated 7 October 2026, including Dashboard, PDF Details, Action Required, Duplicates, and Guide worksheets.

Scope note: This story describes only behavior supported by the supplied implementation and report. It does not claim OCR support, AI-based extraction, or universal compatibility with every Shipping Bill PDF format.

Files

Download all files

shipping_bill_uploader (1).py (204 KB) shipping_bill_uploader (1).py	Shipping Bill Bulk Uploader Python code	Rajesh Gotur, 10/14/2026 10:53 AM
Shipping_Bill_Upload_Report_2026-10-07_18-25-22 (1).xlsx (19.7 KB) Shipping_Bill_Upload_Report_2026-10-07_18-25-22 (1).xlsx	Report	Rajesh Gotur, 10/14/2026 10:53 AM

Spent time