Excel to DBF

From a spreadsheet to a dBase table

Lists often live in Excel long after the program that needs them was written: a price list for an old till or stock program, member records for a Clipper or FoxPro application, attribute data for a map layer. Those programs read DBF tables, not workbooks. This page turns the first sheet of a workbook into a dBase III table directly, without a detour through CSV.

Skipping the detour helps. Dates, numbers and true/false values come straight from the cells, so nothing depends on how your regional settings would have spelled them out as text.

Converting an Excel workbook to DBF

1. Put the sheet you want first in the workbook; only the first sheet is converted. Its first filled row becomes the field names.
2. Choose the Text encoding for the DBF, or keep Western (Windows-1252).
3. Drop the .xlsx file, or press Choose file. Older .xls files and .ods files need saving as .xlsx first.
4. Check What changed on the finished row. It lists renamed columns, counts anything cut or replaced, and names the sheet that was used when the workbook has more than one. Then download the .dbf.

These rows come from turning our test tables into Excel workbooks and back into DBF files. Values come through, apart from changes the notes report and one they don't see: a Windows line break inside a cell (CR LF) comes back as a plain line feed, because the library we read workbooks with drops the CR. Excel itself puts a line feed in a cell when you press Alt+Enter, so this mostly touches workbooks made from other files. A workbook goes through the same DBF writer as a CSV, so the same limits apply: the cut at 254 bytes, the field names and the characters an encoding lacks. What differs is how the sheet is read, which is the next section.

What we take from each cell

We read the value Excel stored, not the way the cell is formatted. Most of the time that's exactly what you want, but a few cases are worth knowing:

• Formatting isn't carried. 12.5% is stored as 0.125 and a price shown as $1,250.00 as 1250, and those are the values the DBF gets.
• Numbers keep 15 significant digits when they have a fraction, the precision Excel shows, so a stored 0.30000000000000004 arrives as 0.3. Whole numbers up to 9,007,199,254,740,991 keep every digit.
• Leading zeros from a number format are lost. A postal code shown as 00501 through a 00000 format is stored as 501. Format the column as Text in Excel and retype the codes, and they stay text in the DBF too.
• Formulas arrive as their last calculated result. A workbook written by a program that never calculated it can have empty formula cells; opening and saving it in Excel fills them in.
• Dates come from cells with a date format and become date fields. A cell whose format also shows the time becomes text such as 2024-01-31 14:30:00, since dBase III has no date-time field.
• TRUE and FALSE become a logical field. Error values such as #N/A arrive empty.
• Merged cells keep their value in the top-left cell only, so the rest of a merged block arrives empty.
• Hidden rows and columns are converted too. Delete them first if the DBF shouldn't have them. Completely empty rows are skipped.

Once the values are read, field names and types follow the same rules as for CSV files, which the CSV page sets out under how columns become fields.

Picking an encoding for Excel text

Excel keeps text in Unicode, so a workbook can hold any alphabet. A single-byte code page has room for 256 characters, so the encoding you choose decides which ones survive. Choose the code page of the program that will read the DBF: Western for English and Western European text, Central European, Cyrillic or Turkish for those languages, a DOS page for DOS-era software. UTF-8 keeps every character, with one catch for older programs, which the encoding section of CSV to DBF explains along with the other choices.

Large workbooks

The whole sheet is unpacked in memory before the DBF is written, so we ask before starting a workbook over 50 MB. XLSX files are compressed, which means a sheet can be far bigger than its file size suggests. The DBF itself holds up to 255 columns.

If something goes wrong

• "This file is password protected…" A workbook with a password to open is encrypted as a whole. Remove the password in Excel, save, and try again. Protection that only stops a sheet from being edited doesn't get in the way.
• "This file looks damaged…" The file isn't a complete workbook, often because a download or copy didn't finish. Open it in Excel if you can, and save it again as an Excel Workbook (.xlsx).
• "This format isn't on our list yet." This page takes .xlsx only. Save .xls, .xlsm or .ods files as an Excel Workbook first.
• The wrong sheet came through. Only the first sheet is read, and the notes name it. Drag the sheet you want to the front and convert again.
• A number column became text. At least one cell in it holds text: a number stored as text, which Excel marks with a small green triangle, or a note such as "n/a". Convert or clear those cells and try again.
• "This table doesn't fit in a DBF here." The sheet has more columns than a DBF allows, or is too big for this tab's memory. Split it into two workbooks and convert each one.

Try it with a sample file

Download sample-products.xlsx (9 KB), a small workbook with two sheets, and drop it above. Only the first sheet, Products, is converted, and the notes say so. Its five columns become number, text, number, logical and date fields, and the empty cells arrive blank. With Western (Windows-1252), the two Turkish letters in one product name become "?" and are counted; choose Turkish (Windows-1254) or UTF-8 to keep them.

For a bigger sheet, download sample-world-countries.dbf (519 KB), convert it on the DBF to Excel page, and drop the new .xlsx here with UTF-8 chosen. Edit it first if you like, deleting a few countries or adding a column, and watch the change land in the DBF.

The products workbook is our own, free to reuse (CC0); the country table is public domain. Source and details: sample file notes.