How do you parse a 1.8 GB Tally Prime XML export on office hardware?

Stream it with lxml.etree.iterparse() so the file is read piece-by-piece from disk instead of loaded into RAM, which caused MemoryError crashes on standard office PCs.

How do you survive mixed UTF-8/UTF-16 and damaged nodes in Tally exports?

A text pre-processor strips invalid Unicode before the stream reaches the XML parser, and custom exception handling skips corrupted nodes (14 in the logged run) without halting ingestion.

What comes after the ETL in this pipeline?

The forecasting stage fits one Prophet model per SKU with ProcessPoolExecutor across 8 workers, dropping runtime from ~18 hours to 4h 25m into a decision-ready CSV (see the forecasting-tool case study).

Tally Prime XML to SQLite ETL Cookbook

Q: What does the SQLite layer look like after parsing?

Parsed rows land in an indexed SQLite store: a 782,904-row sales velocity frame in the logged run, file-based with zero maintenance, ready for per-SKU modeling.

Outcome: a 1.8 GB Tally Prime XML export that crashed Excel becomes a 782,904-row indexed SQLite frame, parsed on existing office desktop hardware with no server and no cloud bill. The ETL half of the forecasting-tool case study: the plumbing behind the 18-hours-to-4-hours post.

No new tools here. Every fact below is already in the work entry, the uses notes for Python ETL and Prophet/SQLite, or the run log quoted there.

Ingredients

Input: Tally Prime branch-wise sales export, ~1.8 GB XML per run (individual files often 500MB+), three years of history across four branches.
Parser: Python + lxml (C-library wrapper) with lxml.etree.iterparse().
Pre-processor: a text stage that strips invalid Unicode before the stream reaches the XML parser.
Store: SQLite, file-based, indexed, zero maintenance, fits an unattended cron job.
Hardware: existing office desktop PCs. Python, SQLite, and Prophet are all free.

Step 1: Never load the file

Standard import scripts read the whole export into memory. On a 1.8 GB file with office-PC RAM, that is a MemoryError before parsing even starts.

The fix, verbatim from the work entry: lxml.etree.iterparse() streams the file piece-by-piece from disk. Memory stays constant regardless of export size (the Python ETL uses entry calls it “stream the gigabyte, never load it”).

Step 2: Normalize the dialect before parsing

Tally ERP generates legacy XML with inconsistent tags, unescaped characters, and unpredictable UTF-16 encoding errors that throw continuous UnicodeDecodeErrors in standard Python decoders.

The pre-processor stage strips invalid Unicode characters from the text stream before it reaches the XML parser. Order matters: clean text in, parseable stream out. Unparseable input never gets a chance to kill the run.

Step 3: Skip damaged nodes, don’t halt

The raw exports contain missing tags and malformed nodes. The work entry’s answer is custom exception handling around the parse loop: bypass corrupted nodes without halting the multi-gigabyte ingestion.

The logged run skipped 14 damaged nodes and kept going. The run log records parse counts, skipped nodes, and converged models: monitoring is the log itself, so a bad export shows up in the output before it reaches the CSV.

Step 4: Land in indexed SQLite

Parsed rows go through Pandas sanitization into an indexed SQLite store: a 782,904-row sales velocity frame in the logged run. File-based, zero maintenance, perfect for offline cron jobs, as the Prophet/SQLite uses entry covers for why this shape fits.

Step 5: Hand off to the model stage

The ETL ends where forecasting begins: one Prophet model per SKU over the velocity frame, distributed with concurrent.futures.ProcessPoolExecutor across 8 workers: ~18 hours sequential down to 4h 25m (transcript [12:11:04]–[14:29:18]), 7,800 models converged, buyer opens a CSV with Recommended_Reorder_Qty per SKU.

Honest limitations

No backtesting in the logged run. The pipeline never compared forecasts against actuals, so no accuracy figure is claimed. Per-SKU error logging is the documented next step: wire that before trusting reorder quantities blindly.
New SKUs need a fallback. Items with fewer than 30 transactions fall back to a simple moving average; Prophet cannot forecast from nothing.
SKU hygiene is a prerequisite. A product recorded under different names per branch splits its time-series across SKUs. Standardize naming (e.g. LOREAL-SERUM-30ML everywhere) before modeling, or the frame lies.
Dirty-data tolerance is bounded. Skipping 14 damaged nodes is resilience; silently skipping thousands would be data loss. Watch the skipped-node count in the run log and investigate spikes.

Stream It, Clean It, Count the Skips

My exports still come out of Tally Prime by the gigabyte, and Excel still cannot hold them. That part has not changed. What changed is where the money sits while I fumble the plumbing: every week the velocity frame builds late is another week of capital on the wrong shelves. So I stay defensive at every stage, and the work case study carries the receipts. Stream the file. Clean the dialect. Skip the damaged fourteen, but count them. Land the frame. The forecasting post is what 782,904 clean rows buy.