'Just add an Excel import' — that sentence sounds like half a day of work. After enough of these, we've learned to reply first: send us the file you actually use today, exactly as it is. Don't clean it up.
A cleaned-up file is the most dangerous kind. The client kindly deletes empty rows and tidies the formatting, we see the ideal version — and after launch, users keep uploading the original.
When the file arrives, we count a few things:
- How many columns. One client said 'about ten'; the file had 17 fields, 4 of them named 'Spare 1' and 'Spare 2,' never filled in by anyone.
- Whether the first row is the header. One sheet had company name, export date, and prepared-by in the first 3 rows, with the header on row 4.
- Merged cells. Only the top-left cell of a merged range holds a value; the rest read as empty, and we have to fill downward ourselves.
- Date columns: real dates or text. They look identical in Excel; one reads as 2017-11-09, the other as a serial number like 43048.
- Line breaks inside cells. Someone hit Enter in a remark field, splitting one row into two — the classic way CSV imports fail.
- Hidden sheets. Once we built the import against the first sheet, all tests passed, and after launch the client said the data was wrong — the real data was in sheet 2; the first was a template from three years ago.
One more: an 'amount' column looked numeric, but a few cells contained full-width commas and read in as strings.
So the code itself usually isn't much: read the file, validate row by row, record row numbers for anything wrong so a human can fix it. What takes the time is clarifying all of the above, then deciding what to block hard, what to auto-fix, and what to ignore.
Our import pages now show a preview first: the first 5 rows, the detected column count, per-row issues — the user confirms, then we actually write to the database. One extra step, far fewer call-backs.
That 3-sheet file became one of our test cases; the file name is 'DontDelete.xls'. New engineers taking over import work still start with the same step — ask for the original file. That has never changed.
