dBase

Connect & Ingest data from dBase / FoxPro (.dbf) files

dBase is a table-oriented file format: every .dbf file holds a single table, with a header describing its columns and fixed size records holding the values. Files are still produced by many desktop and legacy systems (Visual FoxPro, FoxBase, Clipper and database exports), and Sling reads them directly with a native reader — no driver or external binary is needed.

The connection root is either a single .dbf file or a directory holding .dbf files, where every file is a table of the main schema. There is no query engine behind the connection: Sling walks the records itself, so the connection is read-only, and SQL it cannot answer is rejected with an explicit error instead of being silently ignored.

Setup

The following credentials keys are accepted:

• path (required) -> The path of a .dbf file (a single table), or of a directory holding .dbf files.

• schema (optional) -> The schema name reported for the tables. Default is main.

• trim_spaces (optional) -> Trim the trailing spaces of character values, which are stored padded to the field length. Default is true.

Using sling conns

Here are examples of setting a connection named DBASE. We must provide the type=dbase property (the type is also accepted as dbf or foxpro):

# a directory of .dbf files, where every file is a table
$ sling conns set DBASE type=dbase path=/data/tables

# a single .dbf file
$ sling conns set DBASE type=dbase path=/data/tables/customers.dbf

# Windows paths, don't use backslash (\)
$ sling conns set DBASE type=dbase path=C:/data/tables

# Or use url
$ sling conns set DBASE url="dbase:///data/tables"

$ sling conns test DBASE

Environment Variable

See here to learn more about the .env.sling file.

export DBASE='dbase:///data/tables'
export DBASE='{ type: dbase, path: "/data/tables" }'

Sling Env File YAML

See here to learn more about the sling env.yaml file.

connections:
  DBASE:
    type: dbase
    path: /data/tables
    schema: main          # optional, default is main
    trim_spaces: true     # optional, default is true

Reading Data

• Every .dbf file under the connection root is a table, named after the file without its extension. Names are matched without case, so main.Customers reads customers.dbf. When the root is a single file, that file is the only table.

• select with an explicit field list is supported, and so are limit and offset. Sling pushes the projection down to the reader, and applies the limit/offset while walking the table.

• Filters are not supported. A where clause (other than the where 1=0 Sling generates for an empty result), order by, joins, functions and expressions, count(*) and custom SQL are all rejected with an error, since there is no query engine to evaluate them.

• Deleted records (kept in the file, flagged in their record) are skipped.

• A column whose name repeats in the table is suffixed (Point_ID, Point_ID1, ...), as the file readers do with repeated headers.

• A blank number, date or logical field is read as NULL: a dBase blank is stored as spaces (or NULs), which is not a zero value.

• A memo field is read from its memo file. FoxPro .fpt memo files are supported, the dBase IV .dbt variant is not.

• There are no views, and index files (.cdx / .idx) are not read, so no primary keys, unique keys or indexes are reported.

A read of a single table:

$ sling conns exec DBASE 'select "customer_id", "name" from "customers" limit 10'

And a replication from a dBase folder into postgres:

source: DBASE
target: POSTGRES

streams:
  main.customers:
    object: public.customers

Type Mapping

dBase type	Sling type
character, varchar, memo	text
numeric (no decimals)	bigint
numeric (with decimals), float, currency, double	decimal
integer	integer
date	date
datetime	datetime
logical	bool
blob, general, picture, varbinary	binary

Limitations

• Read-only. A dBase table is a plain file, so Sling never writes to it: the connection cannot be used as a replication target.

• No query engine. Reads answer the field list with limit / offset that Sling generates; any other SQL is rejected.

• Sequential reads. The records are walked from the start of the table, so a large offset reads (and discards) the records before it.

• No memo file, no records. A table with a memo field whose memo file is missing, or is a dBase IV .dbt file, reports its columns but its records cannot be read.

• No keys or views. Index files are not read, so keys and indexes are not discovered (declare primary_key in the replication when the target needs one).

If you are facing issues connecting, give sling assist a try, or reach out to us at [e-mail], on discord or open a Github Issue here.