DBFTables

Read xBase / dBASE III+ .dbf files in Julia. Supports the Tables.jl interface.

Shapefile.jl uses this package to read the information associated to the geometries of the .shp file.

Usage

using DBFTables, DataFrames

df = DataFrame(
    x = 1:5,
    y = rand(Bool, 5),
    z = ["a", "b", "c", "d", "e"]
)

# Write any Tables.jl source to a .dbf file
path = tempname()
DBFTables.write(path, df)

# Read the data back in from the .dbf file
dbf = DBFTables.Table(path)

# Retrieve columns by their name
dbf.x

# Iterate over the rows (values can be accessed by column name)
for row in dbf
    @info (row.x, row.y, row.z)
end

# Pass the DBFTables.Table to any Tables.jl sink
df2 = DataFrame(dbf)

Format description resources

• https://en.wikipedia.org/wiki/.dbf
• https://www.clicketyclick.dk/databases/xbase/format/dbf.html
• http://www.independent-software.com/dbase-dbf-dbt-file-format.html

Implementation details

The DBF header contains information on the amount of rows, which columns are present, what type they are, and how many bytes the entries are. Based on this we can create a Tables.Schema. Each row is a fixed amount of bytes. All data is represented as strings, with different conventions based on the specified type. There are no delimiters between the entries, but since we know the sizes from the header, it is not needed.

The DBFTables.Table struct holds both the header and data. All data is read into memory in one go as a Vector{UInt8}. To provide efficient access into the individual entries, we use WeakRefStrings. WeakRefStrings' StringArray only holds the offsets and lengths into the Vector{UInt8} with all the data. Then we still need to convert from the string to the julia type. This is done on demand with dbf_value.

Note that the format also contains a "record deleted" flag, which is represented by a '*' at the start of the row. When this is encountered the record should be treated as if it doesn't exist. Since normally writers strip these records when writing, they are rarely encountered. For that reason this package ignores these flags by default right now. To check for the flags yourself, there is the isdeleted function. A sample file with deleted record flags is available here.

Quirks and Gotchas

The DBF format is quite old (introduced in 1983). As such, it has some quirks that may not be immediately obvious:

1. An empty string is equivalent to a missing value. Thus an empty string in a table will not survive a write/read round trip.
2. Strings are limited to 254 characters. Attempting to write longer Strings results in an error.
3. In order to support as many versions of DBF as possible, DBFTables.jl will only write data as one of the following DBF data types:

• 'C' (Character): Strings (and anything else that doesn't doesn't match one of the other three types).
• 'N' (Numeric): Integers and AbstractFloats.
• 'L' (Logical): Bools.
• 'D' (Date): Dates.

4. The 'N (Numeric) data type restricts values to fit within 20 printed characters. All Int64s fit within 20 characters, but Float64s may not. E.g. string(nextfloat(-Inf)) is 23 characters. DBFTables.jl will remove the least significant digits (loss of precision) in order to fit within the 20 character limit.