The short version
To convert a DBF to Excel without losing anything, go straight from DBF to .xlsx and skip CSV. A DBF knows its column types: a character (C) column of ZIP codes reaches the sheet as text with its leading zeros, and a date (D) column reaches it as real Excel dates. A CSV throws those types away, and when Excel opens a CSV it removes leading zeros and converts the value to a number by default. I tested three routes on one small file built to break things: pandas, CSV, and the free DBF to Excel converter on this site. Both direct routes kept the zeros and the dates. Only the converter kept 1234.50 showing two decimals, wrote an 18-digit amount as exact text instead of rounding it, and got past an impossible date without failing the file.
The test file
If you only need to get the file open, How to Open a DBF File on Windows 11 and Mac covers that. This guide is about what the cells look like afterwards.
I built traps.dbf byte by byte with the same small Python builder our converter's tests use. Three rows, six columns, each column a known way to lose data:
| Column | DBF type | What is in it |
|---|---|---|
ZIP |
C, 5 wide | 02134, 00501, 07030 |
ACCOUNT |
C, 10 wide | 0004417001, 0000000042, 1234567890 |
BALANCE |
N, 12 wide, 2 decimals | 1234.50, 0.10, 9999999.99 |
OPENED |
D | 19980317, 20011231, and eight spaces (an empty date) |
ACTIVE |
L | T, F, ? |
NOTES |
C, 200 wide | a short note, a 200-character note, another short one |
The type letters come from Microsoft's table file structure page for Visual FoxPro. A D field stores eight characters, year, month and day, with no separators.
Here is what dbfread 2.0.7 makes of the rows, before any spreadsheet is involved:
{'ZIP': '02134', 'ACCOUNT': '0004417001', 'BALANCE': 1234.5, 'OPENED': datetime.date(1998, 3, 17), 'ACTIVE': True, 'NOTES': 'Short note'}
{'ZIP': '00501', 'ACCOUNT': '0000000042', 'BALANCE': 0.1, 'OPENED': datetime.date(2001, 12, 31), 'ACTIVE': False, 'NOTES': 'Customer asked for t...'}
{'ZIP': '07030', 'ACCOUNT': '1234567890', 'BALANCE': 9999999.99, 'OPENED': None, 'ACTIVE': None, 'NOTES': 'No open date on file'}
That matches the dbfread field types table: C gives a string, D gives datetime.date or None, L gives True, False or None, N gives int, float or None. The empty date and the ? logical both became None.
Route 1: dbfread and pandas to_excel
This is the pattern from the dbfread export docs, written out with DataFrame.to_excel. Versions: pandas 2.2.0, openpyxl 3.1.5.
from dbfread import DBF
from pandas import DataFrame
df = DataFrame(iter(DBF("traps.dbf")))
df.to_excel("way1.xlsx", index=False)
Read back with openpyxl, each cell: address, type (s string, n number, d date, b boolean), number format, value.
...
A2 s 'General' '02134'
B2 s 'General' '0004417001'
C2 n 'General' 1234.5
D2 d 'YYYY-MM-DD' datetime.datetime(1998, 3, 17, 0, 0)
E2 b 'General' True
F2 s 'General' 'Short note'
A3 s 'General' '00501'
B3 s 'General' '0000000042'
C3 n 'General' 0.1
D3 d 'YYYY-MM-DD' datetime.datetime(2001, 12, 31, 0, 0)
E3 b 'General' False
F3 s 'General' 'Customer asked for the 1998 in...(200 chars)'
A4 s 'General' '07030'
B4 s 'General' '1234567890'
C4 n 'General' 9999999.99
D4 inlineStr 'General' None
E4 inlineStr 'General' None
F4 s 'General' 'No open date on file'
The ZIP codes and account numbers are text cells with every zero in place, because dbfread handed pandas strings and pandas wrote strings. The dates are real date cells. In the XML, D2 is stored as <v>35871</v> with a date style, which is how an .xlsx holds a date: a serial number plus a format. The empty date D4 and the unknown logical E4 are written as <c r="D4" t="inlineStr" />, a cell with no value, so they show as blank.
What it did not keep: 1234.50 became 1234.5 in a cell formatted General. The value is right; the two decimal places are a display format you have to put back.
Route 2: save as CSV, then open in Excel
Same DataFrame, df.to_csv("way2.csv", index=False):
ZIP,ACCOUNT,BALANCE,OPENED,ACTIVE,NOTES
02134,0004417001,1234.5,1998-03-17,True,Short note
00501,0000000042,0.1,2001-12-31,False,"Customer asked for the 1998 invoices to be re-sent to the new address; credit limit raised after review, see letter on file. Customer asked for the 1998 invoices to be re-sent to the new address; cred"
07030,1234567890,9999999.99,,,No open date on file
The file itself is fine, zeros and dates included. What is gone is the knowledge that 02134 was text: whoever opens a CSV has to guess types again.
Excel guesses. Microsoft's page on importing and exporting text files says that when you open a .csv, Excel "uses the current default data format settings to interpret how to import each column of data." The page on automatic data conversions lists what those defaults do, including "Removing leading zeros from numerical text and converting to a number" and "Truncating numerical data to 15 digits of precision and converting to a number displayed in scientific notation." Its own example: 00123 becomes the number 123.
Applied to this file, that means 02134 becomes 2134, 00501 becomes 501 and 0004417001 becomes 4417001. I did not have Excel on the machine I ran this on, so I am quoting Microsoft for that step and making no claim about what a particular Excel build does with 1998-03-17 in your locale.
There are two ways around it if CSV is all you have:
- Turn the conversions off. In Excel for Microsoft 365 and Excel 2024 the switches are under File, Options, Data, Automatic Data Conversion on Windows, and Excel, Preferences, Edit on a Mac, per the same page. It also says these options "don't directly affect data imported by using Power Query".
- Import instead of opening. Microsoft's keeping leading zeros page gives the steps: Data, From Text/CSV, select the column, then Home, Transform, Data Type, Text.
Route 3: our DBF to Excel converter
The DBF to Excel converter runs this code on the server. I called the same reader and writer directly from the repo:
from app.converter.router import READERS, WRITERS
src = Path("traps.dbf")
reader = READERS["dbf"]
data = reader(src)
files = WRITERS["xlsx"](data, out_dir)
DBF_ENCODING_DECLARED: The file declares code page cp1252; text was decoded with it.
column ZIP TEXT 5 None
column ACCOUNT TEXT 10 None
column BALANCE DECIMAL 12 2
column OPENED DATE None None
column ACTIVE BOOL None None
column NOTES TEXT 200 None
['traps.xlsx']
...
A2 s 'General' '02134'
B2 s 'General' '0004417001'
C2 n '0.00' 1234.5
D2 d 'yyyy-mm-dd' datetime.datetime(1998, 3, 17, 0, 0)
E2 b 'General' True
F2 s 'General' 'Short note'
A3 s 'General' '00501'
B3 s 'General' '0000000042'
C3 n '0.00' 0.1
D3 d 'yyyy-mm-dd' datetime.datetime(2001, 12, 31, 0, 0)
E3 b 'General' False
F3 s 'General' 'Customer asked for the 1998 in...(200 chars)'
A4 s 'General' '07030'
B4 s 'General' '1234567890'
C4 n '0.00' 9999999.99
D4 n 'General' None
E4 n 'General' None
F4 s 'General' 'No open date on file'
Text, dates and blanks match Route 1. The difference is the BALANCE column. The reader parses N fields with decimals as exact Decimal values instead of floats and keeps the declared width and scale (DECIMAL 12 2), so a database target gets NUMERIC(12,2). In the sheet, the writer uses that scale as the cell's number format: C2 holds 1234.5 with format 0.00, so Excel shows 1234.50, and in the XML it is <c r="C2" s="1" t="n"><v>1234.5</v></c> with style 1 carrying the format. The empty date and the ? logical are empty cells with no value. The 200-character note came through whole on every route.
Which way to convert DBF to Excel keeps what
| Trap | pandas to_excel | CSV opened in Excel | Our converter |
|---|---|---|---|
ZIP 02134 |
text, zeros kept | number per Microsoft's defaults, zeros dropped | text, zeros kept |
Account 0004417001 |
text, zeros kept | number per Microsoft's defaults, zeros dropped | text, zeros kept |
Date 19980317 |
date cell | depends on your settings | date cell |
| Empty date | blank cell | empty field | blank cell |
1234.50 |
1234.5, General | text 1234.5 in the file |
1234.5, format 0.00, shows 1234.50 |
12345678901234.5678 |
number 12345678901234.57 |
12345678901234.568 in the file, 15 digits once opened |
exact text |
Date 20010231 |
file fails | file fails | blank cell |
Logical ? |
blank cell | empty field | blank cell |
When the zeros are already gone in the DBF
All of the above assumes the ZIP or account column is type C. Some old applications stored codes in N fields. A numeric field has no leading zeros to keep. I built a second file, edge.dbf, with 0004417001 in an N field:
{'ACCTNUM': 4417001, 'OPENED': None, 'AMOUNT': 12345678901234.568}
dbfread and our reader both returned 4417001, and the converter wrote it to the sheet as the number 4417001. If you know the width, Microsoft's leading zeros page shows how to show them again in Excel with a formula such as =TEXT(A1,"00000") for a five-digit postal code. Check the field type in the header before you blame the converter.
The same row has an OPENED of 00000000, which dbfread read as None, the same as blanks.
More than 15 digits
AMOUNT in that row is N, 20 wide, 4 decimals, holding 12345678901234.5678. dbfread returned the float 12345678901234.568, already wrong in the last digit. On a copy of the file with a valid date in place of the bad one (pandas cannot read this one, see the next section), to_excel wrote the number 12345678901234.57 and to_csv wrote 12345678901234.568. Our reader returned the exact decimal, and the converter's .xlsx has:
C2 s 'General' '12345678901234.5678'
C3 n '0.0000' 0.0001
In the XML, C2 is <c r="C2" t="inlineStr"><is><t>12345678901234.5678</t></is></c>: a text cell with every digit. Excel's specifications and limits give number precision as 15 digits, so a number with 18 significant digits cannot survive as an Excel number by any route. The converter counts the significant digits of each decimal and writes anything over 15 as text, while shorter values in the same column, like 0.0001 in C3, stay numbers with the column's four-decimal format. The cost is that you cannot sum a text cell. If you need arithmetic on values that long, such as high-precision amounts, keep them out of Excel: the DBF to CSV guide and DBF files into PostgreSQL cover those routes.
An impossible date in a D field
Old DBF files sometimes hold dates that never existed. I changed one row of edge.dbf to 20010231, the 31st of February. dbfread raised on it:
ValueError invalid date b'20010231'
pandas gets no further, because the DataFrame is built from the same iterator: the whole file fails over one cell.
Our converter uses dbfread underneath with its own field parser, which turns a date it cannot parse into NULL. The same file converts, and the 31st of February is an empty cell:
column OPENED DATE None None
...
B3 n 'General' None
The only warning in that run was the code page one. Nothing in the output marks B3 as a bad date rather than a missing one, so if the difference matters to you, find the bad cells first. The dbfread introduction shows how: a custom field parser that returns InvalidValue instead of raising. I ran it:
from dbfread import DBF, FieldParser, InvalidValue
class KeepGoing(FieldParser):
def parse(self, field, data):
try:
return FieldParser.parse(self, field, data)
except ValueError:
return InvalidValue(data)
for i, record in enumerate(DBF("edge.dbf", parserclass=KeepGoing)):
for name, value in record.items():
if isinstance(value, InvalidValue):
print(i, name, repr(value))
1 OPENED InvalidValue(b'20010231')
You get the list of bad cells and the rest of the file. Decide what each bad date should be (blank, or the raw text in a separate column) before it goes near Excel, since InvalidValue is a byte string and a date column cannot hold it.
Long text and memo fields
A classic C field is short anyway: the dbfread field types page says DBF text "was originally limited to 255 bytes". Memo fields can be longer, and an Excel cell holds at most 32,767 characters per the specifications page. Memo text also lives in a separate .fpt or .dbt file that has to travel with the .dbf; FoxPro memo fields covers getting it out.
Sources
- Set automatic data conversions, Microsoft Support
- Import or export text (.txt or .csv) files, Microsoft Support
- Keeping leading zeros and large numbers, Microsoft Support
- Excel specifications and limits, Microsoft Support
- Table File Structure, Microsoft Learn, Visual FoxPro
- dbfread: Field Types, Introduction, Exporting data
- pandas.DataFrame.to_excel