One Excel Upload Freezes the System: Validation and Performance Design for Bulk Import and Export

The Most Used Feature, and the One That Breaks Most Often

However well a business system is built, Excel import and export never go away. Data sent by a partner, records migrated from a legacy system, a price list where hundreds of rows need to change at once: a spreadsheet is simply faster than a form for all of these.

The trouble is that this feature is usually built as a button attached to a screen. A file with 50,000 rows and 20 columns holds one million cells. Load all of it into memory inside a single request and save it row by row, and you will either exceed the 30–60 second web request limit or exhaust the server's memory. The typical incidents follow:

Half-applied data: the job died around row 30,000 and nobody knows how much went in

Duplicate records: the user got no response, uploaded again, and the file was applied twice

System-wide slowdown: one upload holds database connections and every other user's screen slows down

Import Design: Separate Validation from Commit

The core idea is to split validation and commit into two stages. Receive the file, check all of it, show the user the result, and only then write.

Template and Formatting Traps

Template version: put a version in the template and check header names and required columns first. Map by header name, not column position, so an added column does not shift every value

Dates: Excel stores dates as serial numbers. March 15, 2023 can arrive as the number 45000

Leading zeros and long numbers: item code `00123` becomes `123`, and numbers longer than 15 digits have their trailing digits replaced with zeros. Format code columns as text in the template itself

Whitespace and invisible characters: a pasted value with a trailing space makes the same customer look like a different one

Returning Errors

A single message saying "error in row 37" is not enough. Return an annotated error file: the original file with an added column stating the reason for each failed row. The user fixes that file and uploads it again.

Whether to reject the whole file or commit only the valid rows depends on the data. Where rows only make sense together, such as journal entries or a BOM, choose all or nothing. Where rows are independent, such as customer or item master data, committing valid rows works well. Either way, state the rule on the screen before the upload.

Performance Design for Large Volumes

Job queue: the upload request stores the file, registers a job, and responds immediately. A background worker does the processing while the screen shows progress

Streaming parsing: reading row by row instead of loading the whole file keeps memory use flat regardless of file size

Batch writes: saving in batches of 500–1,000 rows instead of one at a time cuts database round trips by a factor of hundreds

Duplicate key: define what makes two rows the same record, for example customer code + item code + effective date, and define what happens on a match: overwrite, skip, or error. Uploading the same file twice must produce the same result

Export Design: What Happens When You Export the List Screen As-Is

The list screen shows 20 rows at a time, but the export button reads all 300,000 rows that match the filter. Run that query on the production database and order entry slows down in the middle of the working day.

Isolate the load: send large queries to a read replica or a summary table

Asynchronous generation: build the file in the background and notify the user with a download link when it is ready

Limits and format: one xlsx sheet holds at most 1,048,576 rows. Set a policy such as capping instant downloads at around 10,000 rows and routing anything larger to asynchronous generation, split files, or CSV

Security and Control

Exports containing personal data: require a reason and record who exported what, when, with which filters, and how many rows. Mask fields the task does not need

Formula injection: values starting with `=`, `+`, `-`, or `@` can execute as formulas in Excel. Write them out as text on export. On import, check the actual file type and size, not just the extension, and reject files containing macros

Approval and rollback: route bulk changes, such as 1,000 price updates, through approval, and keep the previous values per upload job so the whole job can be reverted at once

The POLYGLOTSOFT Approach

When every screen implements its own Excel feature, validation rules, error messages, and performance problems multiply with the number of screens. POLYGLOTSOFT builds template validation, annotated error files, the job queue, progress tracking, and export history as a shared import/export module, so each screen only adds its column definitions and validation rules.

Slow Excel features in systems already in production can be improved as well. Moving a timing-out upload to asynchronous processing, adding annotated error files, and isolating large exports are all tasks that fit within subscription development, prioritized and delivered in stages. If an Excel upload has ever frozen your system, contact POLYGLOTSOFT.

One Excel Upload Freezes the System: Validation and Performance Design for Bulk Import and Export

The Most Used Feature, and the One That Breaks Most Often

Import Design: Separate Validation from Commit

Template and Formatting Traps

Returning Errors

Performance Design for Large Volumes

Export Design: What Happens When You Export the List Screen As-Is

Security and Control

The POLYGLOTSOFT Approach

Related Posts

You Only Notice After the Orders Stop: Machine Learning Churn Prediction and Sales Response for B2B Accounts

Every Customer Wants a Different Label: Product Label and Barcode Printing Management Integrated with MES

Tangled Location Codes, Tangled WMS: Designing a Warehouse Location System and WMS Location Master

Need Technical Consultation?