Convert dBase to SQLite

Working from a dBase file

dBase III and IV gave the format its five original field types, and that small vocabulary is why a dBase table converts so cleanly: there is very little in one that a modern database cannot hold exactly.

• →Character, Numeric, Date, Logical and Memo are the whole type system. A Numeric field declared with no decimals becomes a 64-bit integer; one with decimals becomes an exact decimal of the declared width and scale.
• →dBase has no time-of-day type at all, so a Date field converts to a pure date and never to a timestamp at midnight.
• →dBase IV memo blocks carry an 8-byte header and dBase III's do not, so a .dbt written by either generation is read with the block layout it actually uses.

What the .sqlite file looks like inside

The output here is a real database rather than a script, so the interesting question is not how to load it but what SQLite did with the types on the way in. Its storage classes are looser than the declarations the file ends up carrying.

• →Names are carried over exactly as they were, with none of the sanitising the script targets apply: a table called Sales Report keeps its space and a column called Order ID keeps its own, so your queries have to quote them.
• →Underneath the declared types sit only five storage classes, and NUMERIC affinity - what a decimal column gets - is the loosest of them: it normalises inserted text to whatever a plain INTEGER or REAL column would hold. The precision and scale in the schema are for a human reading it, not a guarantee SQLite enforces on the bytes; the Writing notes below cover what that means for money specifically.
• →Declared type and stored type are different things here: a BOOLEAN column holds the integers 1 and 0, and a DATE column holds ISO text, because those are the storage classes SQLite actually has. Every client reads them back the same way.

Reading dBase

• →Numeric fields with decimals are read as exact decimals, not floats, so currency does not drift.
• →Character field widths are read from the header and carried into the target.
• →The code page declared in the file header is used, so Cyrillic, Western European and other legacy DOS encodings decode correctly instead of turning into mojibake. 157 code pages are recognised.
• →Files written by dBase III or Clipper declare no code page at all — the header byte for it did not exist yet. For those, the most plausible encoding is chosen by inspecting the text, and the conversion tells you it had to guess. The API takes a sourceEncoding parameter (e.g. cp866) to force it instead of guessing, though the widget above does not have a control for it yet.
• →Memo columns keep their text in a separate .dbt or .fpt file. If the .dbf declares one and it was not uploaded, the conversion stops and tells you which file to add, rather than handing you a table whose long text is silently blank.

Writing SQLite

• →Columns get real declared types rather than everything being TEXT.
• →Primary keys and NOT NULL constraints are preserved.
• →A money column is declared NUMERIC(10,2), but that declaration does not stop SQLite’s own numeric affinity from converting the inserted value into a float once it lands, so 19.99 can come back with a rounding tail. Pick the DuckDB target instead when the exact decimal has to survive.

Convert dBase to something else

• MySQL
• PostgreSQL
• Oracle
• SQL Server
• DuckDB
• CSV
• Excel

Convert something else to SQLite

• CSV
• Excel
• SQL dump
• JSON
• XML
• Parquet
• DuckDB
• MS Access
• PostgreSQL backup