Data Types per Column in Templates That Feed a Database

Excel has one idea of a type per cell, and the database has one per column. The template is where the two meet. If a column in the database is date and the template lets people type "3rd Oct", the loader has to guess, and the guess is wrong often enough to matter. If a column is numeric(10,2) and a cell holds 12.499999999 from a formula, the value goes into the table rounded in a way nobody chose. Types in the template are the first defense against both. This article maps database types to spreadsheet cell formats, validation and loader checks, with the traps that each type brings. It is one of the layout rules in building Excel templates that import cleanly, and the type behaviors are described in Excel stores dates as serial numbers and a cheat sheet of Excel data types.

How Excel stores a value

A cell holds one of a few kinds of value: a number (a 64-bit floating point value), text, a boolean, an error, or empty. Dates and times are numbers with a date format. Currency and percentages are numbers with a format. There is no integer type, no decimal type, no UUID, no enum. Everything beyond number, text, boolean and error is a convention enforced by format and validation.

This explains the failures. A "date" typed as text is a text cell, which does not sort or compare as a date. A "zip code" typed as digits is a number, and the leading zero was dropped by the number parser. A long ID of 18 digits is stored as a double and loses precision beyond 15 digits. A "yes" is text, and so are "Yes", "Y" and "yes ".

Text: identifiers, codes and names

Use text for anything that is an identifier, even if it looks numeric: postal codes, product codes, phone numbers, account numbers, invoice numbers, tax IDs. The test: would you ever add two of them? If not, it is text.

Set the column's format to Text (@) before distribution, so that typing 00417 stores the characters. From code, write the cells with a string and set number_format = "@" on the column:

for row in range(2, 501):
    ws[f"B{row}"].number_format = "@"

Format a column as Text only before data is entered; changing the format afterward does not convert existing numbers. The loader should check cell.data_type: a numeric cell in a text column is an error, because the leading zeros may already be lost (leading zeros). The database type is text or varchar(n); add a length validation to match n.

Trim and normalize in the loader. Do not change the meaning: keep case unless the column is documented as case-insensitive.

Dates and times

Dates must be real date cells: a number with a date format. A date written as text is a different thing, and parsing it is a guess about order ("03/04/2026" is March 4 or April 3). The template should make the right thing the default:

• Set the column format to an unambiguous display, such as yyyy-mm-dd.

• Add date validation with a sensible window ("between 2020-01-01 and today plus 1 year").

• Tell people to enter dates with Excel's date entry (or paste real dates), and to use the format 2026-10-29, which Excel recognizes as a date in every locale.

The loader reads the cell. In openpyxl a real date arrives as datetime.datetime; a text date arrives as str. Reject the string with a message: "counted_on: not a date (use a date cell, not text)". Do not parse text dates unless the format is fixed and documented (the loader should read date cells and not parse text).

Remember that Excel dates carry no time zone and that 1900 and 1904 date systems exist. A date-only column maps to date. A timestamp column maps to timestamp or timestamptz, and the template must say which zone the sender means (serial dates convert to real dates in the loader).

Numbers: integers, decimals, amounts

Numbers in Excel are floating point. A database wants more precision about what kind of number.

Whole numbers (integer, bigint): quantities, counts. Validation: whole number between a minimum and maximum. The loader checks that the value has no fractional part (float.is_integer()) and converts to int. A quantity of 3.0 is fine; 3.5 is an error.

Decimals (numeric(p,s)): prices, rates, weights. Validation: decimal between bounds; format with the scale (0.00). Excel shows two decimals but stores whatever was typed or calculated, so 12.499999 is displayed as 12.50 and loaded as 12.499999 unless the loader rounds or rejects. Decide: reject more decimals than the scale allows, or round with a stated rule. For money, avoid floats in the database: use numeric, or integer minor units (rounding and formatting numbers for finance readers).

Large integers beyond 15 digits cannot be numbers in Excel. Make them text, and let the database cast.

Percentages are numbers: 12.5% is stored as 0.125. Say in the instructions whether the column is a fraction or a percent, and format with 0.0% so people type "12.5%". A column labeled "rate" where some people type 12.5 and others 0.125 is a classic error; validation with a maximum of 1 catches it.

Currency: store the amount as a number and the currency as a separate column or a header note. A format with a symbol hides the currency rather than defining it (handling currency and locale in generated workbooks).

Check for numbers stored as text: left-aligned values with a green triangle. A column formatted as General that receives "1,250" with a thousands separator typed in a text-forced cell arrives as text. The loader should reject text where a number is required, or convert with a documented rule.

Booleans and flags

Excel has TRUE and FALSE as boolean cells, and people type "Yes", "Y", "yes", "1", "x" and a tick. The template should offer one spelling. A dropdown of yes,no (or TRUE,FALSE) with Stop validation keeps it to two values. The loader maps them to a boolean. Reject anything else, including blanks if the flag is required. If you accept blanks, say whether blank means no, not asked or unknown, and map accordingly (NULL for unknown).

Categories and enumerations

A column that takes one of a few values (status, country, unit) should be a dropdown driven by a list on a lists sheet, and a database enum, a lookup table or a check constraint. The loader compares against the same list. When the list changes, regenerate the template and bump the version (data validation lists and their limits in Excel sheets).

Case and spelling are the usual problems: "Open", "open" and "OPEN". Dropdowns prevent typing variants; pasting does not, so normalize in the loader (lower().strip()) and reject unknown values.

Free text

Descriptions and notes are text with a length limit and no other rule. Validate the length against the database column. Strip control characters. Treat leading =, +, - or @ as a formula risk if the text will be exported again (formula injection in Excel exports). Do not apply case changes or spell fixes.

Keys and uniqueness

A key column, alone or in combination, must be unique and not blank. Excel cannot enforce this; a custom validation like =COUNTIF($A$2:$A$1000, A2)=1 rejects typed duplicates, with the usual caveat about pasting. The loader enforces it and reports the duplicate rows. In the table, add the primary key constraint so the database agrees.

From database types to template: a mapping

From the schema, generate the CREATE TABLE, the template formats and the loader checks together, so they cannot disagree (checking an incoming workbook against a template by code shows a schema driven checker).

Test the types

Create a file that breaks each rule: a numeric cell in a text column, a text date, a decimal in an integer column, 12.499999 in a two-decimal column, "Y" in a yes or no column, a number above the maximum. Assert the loader's message for each. Add a clean file that passes. Include an 18-digit identifier written as text to confirm it survives intact.

When a spreadsheet type cannot express the database type

Some database types have no spreadsheet counterpart: arrays, JSON, ranges, geometry, intervals. Do not force them into cells. Use text with a documented format (a JSON string, validated by the loader) or a separate child table with one row per element. For exactness beyond 15 digits, use text. And for types where the meaning depends on context, such as a timestamp with a time zone, add a column that states the context. The PostgreSQL data types chapter lists the targets, and Microsoft documents number formats in Excel.