Before you import a spreadsheet into another system: the checks that stop a failed load

Why imports fail on row 4,000

A person reads a spreadsheet by eye and forgives a lot. A loader does not. It expects every column to hold one kind of value, every row to have the same number of fields and every key to be unique. The first row that breaks any rule stops the load or, worse, loads wrongly. Fixing the data in the order of the load's rules is faster than fixing errors one at a time as they appear.

Build a readiness sheet, one line per column

For every column, record the type it should hold, the number of blank values, the number of distinct values and, for numbers and dates, the smallest and largest. For the column that identifies a record, record whether every value is unique. Microsoft's guidance on PivotTable source data gives the shape a clean table has: one header row of distinct labels, no merged cells, no blank rows or columns and one kind of data in each column. A readiness sheet shows where a table departs from that.

Mixed types in one column are the most common finding: numbers and text together, or dates and text. Resolve those before anything else.

• Type, blank count, distinct count, smallest and largest.
• Is the key column unique?
• Do the header names match the target's column list?

Blanks are not all the same

An empty cell can mean not applicable, not known or zero. The target system may treat them differently. PostgreSQL's COPY documentation shows one example: in CSV format the default null string is an unquoted empty string, while an empty string value must be quoted, and an option exists to read empty values as zero-length strings instead. Whatever system you use, decide what each blank means and make it explicit before loading.

Dates, codes and the CSV file itself

Hold dates in the year-month-day form, which the W3C date note describes as unambiguous. Keep identifiers that look like numbers as text: Excel removes leading zeros and keeps only 15 significant digits. Microsoft says that opening a .csv directly applies Excel's default format to each column, while its import route allows more control, such as keeping leading zeros.

Microsoft says saving as CSV keeps only the text and values as they are displayed, and that formatting, graphics and other content are lost, so the file carries what a cell shows, which is not always the stored value. The CSV format itself is looser than it looks. RFC 4180 says fields containing commas, double quotes or line breaks should be enclosed in double quotes, that each line should have the same number of fields, and that implementations differ considerably. Check the target's own rules for delimiter and quoting.

Test on a copy, load under your own checks, and where the paid job fits

PostgreSQL's documentation says COPY fails by default if it meets an error such as a value that cannot be converted to its column type. From PostgreSQL 17 an option, ON_ERROR ignore, skips such rows; earlier versions, such as 16, have no such option and stop at the first error. Neither behaviour is a plan. Load a copy into a test system first, count rows loaded against rows sent and keep the rejected rows.

A defined set of up to eight workbooks can be repaired, flattened, date-fixed and, for product, parts, asset or company lists with no personal data, de-duplicated into consistent tables, each with a readiness sheet, as a project from £1,500 (an untested proposal, quoted after we read the list). The steps run in one fixed order: formulas, then layout, then dates and numbers, then duplicates. The project is accepted by per-step reconciliations, the readiness sheets and your written acceptance of each table. It does not load data into any live system, decide what the data means or give accounting advice, and a passing readiness sheet does not prove a target will accept the data. Workbooks of people's details are not covered, and work starts only after a secure way to hand the files over has been agreed in writing; no upload portal exists yet. The first enquiry needs a list of the workbooks and what they must feed, never the data.