CSV Data Types: Preserve IDs and Clean Numbers

Use TEXT for identifiers

Account numbers, postal codes, telephone numbers, and product codes are usually text. Keeping 00123 as TEXT preserves the leading zeros. SQLite Lab defaults to TEXT so the import does not silently make that decision for you.

Use INTEGER for whole numbers

INTEGER is suitable for counts and whole numbers within SQLite’s signed 64-bit range: -9223372036854775808 through 9223372036854775807. Larger values should remain TEXT. Result previews represent integers outside JavaScript’s safe integer range as strings to preserve their exact value.

Use REAL for approximate numeric values

REAL uses floating-point representation and is appropriate for many measurements. It is not an exact decimal type. For exact money calculations, consider integer minor units such as cents. Invalid numeric fields stop the import instead of being silently converted.

Convert only when you need a calculation

You can preserve source values as TEXT and cast a column when querying. Inspect the values first: SQLite may convert non-numeric text to zero when casting.

SELECT category,
       SUM(CAST(amount AS REAL)) AS total
FROM imported_data
GROUP BY category;

Distinguish empty text and NULL

In this importer, empty TEXT fields remain empty strings. Empty or whitespace-only INTEGER and REAL fields become NULL. NULL means missing or unknown; it is not the same thing as zero. Use IS NULL when checking for it.