Designing an Excel Bulk Import Feature: 7 Rules

Picture a building materials wholesaler. Every month, suppliers send their new price lists as Excel files. The purchasing manager opens the file, rearranges the columns into the order the system expects, fixes a few rows and presses "Upload". A single message appears on the screen: "The file could not be processed." It doesn't say which row, which cell or why.

The manager splits the file in two and tries again, then into four. An hour later they find the cause: Excel had dropped the leading zero from a product code. The following month the same thing happens in a different column. After a while, prices start being typed in by hand, and the "bulk import" feature becomes a menu nobody opens.

In business software, bulk import is usually added in the last weeks before handover and left at the level of "choose a file, save". Yet unlike a one-time data migration, it is a job repeated every week or every month, and the person doing it is rarely a developer. The seven rules below add a few days of work when they are discussed before this screen is built; when they are not, they cost the same hour again every month.

1. Let the system provide the template

Instead of writing help text that says "the columns must be in this order", put a "Download template" link on the upload screen. The template should include the column headings, a mark on the required fields and one example row. A second sheet should describe in short sentences what each column expects, for example "Unit: one of PIECE, KG, M2".

Because the template is generated by the system, it updates itself when a new field is added. Nobody struggles with an old file they saved to their desktop a year ago.

2. Read columns by name, not by position

The file that arrives from a supplier is never exactly the same as your template. There is an extra column, two have swapped places, one is called "Unit Price" instead of "Price". If the system reads columns from left to right in order, a single shift can write every price into the stock field, and without raising an error.

When a file is uploaded, read the header row and show the user a mapping screen: a list of which system field each column in the file will go to, with unrecognised columns marked as "not used". Remember the last mapping for files from the same source; from the second month on, this screen comes down to a single confirmation click.

3. Show what will happen before saving

The "Upload" button should not write straight to the database. First read the file and show a summary: how many rows will be added as new records, how many will update an existing record, and how many will be skipped because they contain errors. For the records that will be updated, show a few examples of the old and new values as well.

This summary catches the most expensive mistakes. Seeing that a file which should only change prices will also rename 1,200 products gives the user a chance to press "Cancel".

4. Report each error with its row and cell

"The file could not be processed" is not an error message. A good message answers where and why together: "Row 214, Price column: '1.250,00 TL' could not be read as a number" or "Row 87, Product Code is empty; this field is required."

There may be hundreds of rows with errors. Instead of listing them on screen, give them back as a file containing only the failed rows, each with its error description next to it. The user fixes that file and uploads it again; they don't have to search for or re-upload the correct rows.

One more decision is needed: if some rows fail, should the rest be imported, or none at all? For rows that are independent of each other, like a price list, "import the valid rows, return the failed ones" is usually right. For rows that belong together, like the lines of an order, it should be all or nothing.

5. Plan for local formats from the start

Files used in Turkey run into the same problems again and again, and all of them are known in advance:

• Decimal separator. "1.250,50" and "1,250.50" are the same number. The system should know which one it expects, be able to recognise both, and ask when it isn't sure.
• Dates. "03.04.2026" means 3 April in Turkey; some systems read it as 4 March. The fact that Excel stores date cells as numbers needs its own conversion as well.
• Leading zeros. Product codes, tax numbers and phone numbers look like numbers but are text. Excel turns "00123" into "123". Format these columns as text in the template, and compare codes as text, not as numbers, in the system.
• Character encoding. In a file saved as CSV, letters such as "ş", "ğ" and "İ" can be corrupted. Recognise both UTF-8 and the encoding Excel uses on Turkish Windows, or accept only .xlsx.
• Invisible spaces. "PIECE " and "PIECE" don't look different but don't match. Trim the spaces at the start and end of each cell while reading.

6. Put the update rule in writing

What happens when a row in the file already exists in the system? To answer this, first decide what identifies a record: the product code for a product, the tax number for a customer account, the employee number for a staff member. A name is not suitable for this; the same company can arrive spelled two different ways in two files.

Then answer two more questions. Should the existing record be updated, skipped, or should the user be asked? And does a cell left empty in the file mean "delete this value" or "leave it alone"? If the answer to the second question isn't written down, a file with only the price column filled in can wipe out every product description. In most cases the right answer is "an empty cell means leave it alone; deleting uses a separate marker".

7. Make every upload reversible

However good the preview is, one day the wrong file will be uploaded. Save each upload as a batch: who uploaded it, when, with which file, and how many records it added and changed. Store the previous values of the changed records together with that batch.

That way, "undo yesterday's upload" becomes a single action. A user who knows an upload can be undone is not afraid to use bulk import. Otherwise the software team is asked to take a backup before every upload, and the feature falls out of use again.

Give this part a limit as well: undoing should not overwrite changes made by hand to the same records after the batch. The system should notice this and show the user which records will be skipped.

In short

• Make the template downloadable from the system, with column descriptions and an example row.
• Read columns by name, show a mapping screen and remember the last mapping.
• Before writing anything, show a summary of the rows to be added, updated and skipped.
• Report errors with their row and column, and give the failed rows back as a file.
• Handle the decimal comma, dates, leading zeros, encoding and spaces from the start.
• Put in writing what identifies a record and what an empty cell means.
• Store every upload with who, when and which file, and make it reversible.

In custom software and ERP projects, we treat the bulk import screen as a separate line item and test it with several of the files users actually upload every month.