DBF to Excel

When a workbook beats a text file

Someone hands you a .dbf from an old FoxPro ledger, a dBase mailing list or a GIS project, and you want to look through it, filter it and perhaps pass it on. A workbook is the friendliest home for that. Excel, Numbers and LibreOffice all read XLSX, and the cells arrive typed: dates are dates, numbers are numbers, and codes stored as text, such as 00742, keep their leading zeros.

That typing is the main reason to pick XLSX over CSV. A CSV is plain text, so the program that opens it has to guess each column's type, and the guess depends on your regional settings. In a workbook, there's nothing to guess.

Converting a DBF to Excel, step by step

1. Leave Text encoding on From the file to begin with. If letters come out wrong, change it and add the file again; the encoding guide shows how to tell which one a table needs.
2. Drop the .dbf, together with its .dbt or .fpt memo file if it has one, or press Choose file and select both.
3. Each table becomes its own workbook, on a sheet named after the file. The first row holds the field names, so filters and pivot tables pick them up straight away.
4. Download the workbook, or use Download all (.zip) once you've converted several tables.

The rows that apply only to Excel come from the workbook format itself. A cell holds a limited amount of text, so a very long memo is cut there, and the notes count each one. A sheet holds a limited number of rows, so a longer table continues on sheets named like "Orders (1)" and "Orders (2)", each with its own header row. And a workbook made in a browser tab has to fit in memory all at once, which is where the cell budget comes from. None of these limits applies to DBF to CSV.

How the cells come out

• Dates become real date cells shown as 2024-01-31, so they sort and filter by date. Date-time fields from Visual FoxPro keep their time of day.
• Dates before 1 March 1900 stay as text. Excel's calendar counts a 29 February 1900 that never existed, so earlier dates can't be stored safely as date cells.
• Numbers stay numbers. A field declared with two decimals is formatted to show both, so 3 appears as 3.00 and still adds up in formulas.
• Whole numbers above 9,007,199,254,740,991, where spreadsheet numbers start to skip values, arrive as text, so no digit is rounded away. Excel shows only 15 significant digits, so a 16-digit ID below that limit looks rounded there, though the file holds every digit.
• True/false fields become TRUE and FALSE cells; a field that was never set stays empty.
• Memo text arrives in full, up to Excel's limit for a single cell.

Large tables

A workbook is built whole, in memory, before you can download it. That's why we ask before starting a DBF over 25 MB here, a much lower point than for CSV, and why a table with more cells than the budget above is stopped before its rows are read. You'll see "This table has more cells than one workbook can hold here": convert that table with DBF to CSV instead, which writes rows as it reads them and has no cell budget.

If something looks off

• A memo cell stops mid-sentence. The text was longer than an Excel cell holds. The notes count how many were cut, and a CSV keeps them whole.
• The workbook has several numbered sheets. The table had more rows than one sheet holds. Each sheet repeats the header row, so the parts line up if you join them in a database later.
• Some dates look like text. Dates before 1 March 1900 are written as text such as 1850-01-01, and the notes don't count them. A value that isn't a valid date keeps its original characters, and the notes count it as an unreadable date.
• An empty memo column, odd letters, or "This file looks damaged…" These come from reading the DBF, which works the same way as for CSV. The DBF to CSV troubleshooting list covers each one.

Try it with a sample file

Download sample-world-countries.dbf (519 KB) and drop it above. It's the country table behind Natural Earth's world map, and it's in the public domain. Once it opens in your spreadsheet, sort the population column, POP_EST, from largest to smallest: the values are numbers, so the most populous countries rise straight to the top.

Source and details: sample file notes.