Excel to PostgreSQL dump with numeric, date and boolean

PostgreSQL is strict about types, which is good for data quality and bad for quick imports. A spreadsheet column with one stray text cell, a date in the wrong order or an empty string in a numeric column makes the whole COPY or INSERT fail.

XlsConverter checks every value before writing PostgreSQL SQL, so the dump loads on the first run. For MySQL syntax see Excel to MySQL, for a lightweight file database see Excel to SQLite.

How to convert Excel to PostgreSQL in three steps

1. 1Upload the Excel file.
2. 2Pick PostgreSQL, the schema, table name and column types.
3. 3Convert, download and run the .sql file with psql.

Quoted identifiers and strict types in PostgreSQL imports

PostgreSQL folds unquoted identifiers to lowercase. A table created with "Customer Name" in quotes must be queried with the quotes forever, which annoys everyone who writes SQL against it. By default we convert headers to snake_case, so Customer Name becomes customer_name and needs no quoting. You can keep the original names if a tool depends on them.

Types are strict. An empty string is not a valid numeric or date value, so empty cells are written as NULL. Excel TRUE and FALSE become boolean true and false, and columns of Yes and No can be mapped to boolean as well. Numeric precision is taken from the data, so money columns get numeric(12,2) rather than a lossy floating point type.

Wrapping the dump in a single transaction means one bad row rolls back the entire load instead of leaving half a table. For tables with millions of rows, a CSV file loaded with COPY is faster than INSERT statements, so XLSX to CSV is a good companion here, and XLSX to SQL covers the other dialects from the same workbook.