DBF, dBase / FoxPro

DBF format is a very old, database format originally introduced by dBase II, recycled by FoxPro and used as a database component by ESRI shapefiles.  In addition to the ubiquitous use of DBF within shapefiles, popular for many years within ESRI products and open source such as QGIS, surprisingly many data sets have been published using DBF format.   We suppose the authors found that slightly better than using cuneiform marks on clay tablets.

 

Most applications use libraries like JET or others to read DBF.   Manifold uses a specialized DBF driver written by Manifold, originally developed to automatically handle the many variations of DBF encountered within shapefiles.   Manifold, therefore, can read or link DBF files without relying on Microsoft software.

 

Manifold can import from DBF, link read/write to DBF, and export tables to DBF.

Import

Important: When importing a DBF file the tables and queries that appear in the Manifold project are Manifold components with no further connection to the DBF file from which they were imported.  

 

To import from DBF format:

 

  1. Choose File-Import from the main menu.

  2. In the Import dialog browse to the folder containing the .dbf file.

  3. Double-click the .dbf file desired.

  4. Everything found in that .dbf database will be imported into the project.

 

 

Double-clicking on the desired .dbf file in the Import dialog as seen above will import into our project everything found in that .dbf database.    

 

 

This particular .dbf database contains a single table.  

 

 

The world table contains the locations for various towns and airports, including their latitude and longitude locations.   DBF is an extremely old and limited database format, with a very poor set data types.   Manifold will capture whatever there is in the DBF.   When importing a DBF, text field values that are all spaces are imported as NULLs.

 

The Manifold DBF driver automatically adds an mfd_id field and mfd_id_x index to the table's schema, so the resulting tables are fully editable and selectable.

Linking a DBF

Important: When linking a DBF file the tables and queries that appear in that data source in the Manifold project stay resident in the DBF file.   They are DBF components even though they may appear in many respects, for the convenience of the user, to be Manifold components.   DBF files linked into a Manifold project are fully read/write.

 

To link a DBF format file:

 

  1. Choose File-Link from the main menu.

  2. In the Link dialog browse to the folder containing the .dbf file.

  3. Click the .dbf file desired.

  4. Check or uncheck the Save cache box as desired.

  5. Press Link.  A linked data source will appear in the project.

  6. Press the + icon next to the data source to expand the data source to see the tables and queries it contains.

 

 

 

The Save cache  button allows setting cache options.   Most often when linking to a format like DBF, we will ensure the Save cached box is not checked.  Working with a linked DBF database will be faster if we check the box, but if we are going to cache data within the project we may as well simply import the DBF and use full Manifold speed.   We uncheck the box and then we press Link.

 

 

That creates a data source called world that contains all of the tables and any other contents of the .dbf file's database.   We can click on the + icon by the data source cylinder to expand the world hierarchy.

 

 

Opening the world table we see it is the same table that was imported earlier in this topic.    Since it is linked the table is now resident in the .dbf file.

Exporting to DBF

DBF format profoundly limits data representation in shapefiles. Floating point numbers are stored as text (!) and many field types common in modern data storage do not exist in DBF.   Text values are limited in length to only 250 characters and, in the original version, could not be Unicode.  Skilled DBMS users who expect to have rich data types may feel like they have been thrown back in time to a world of vacuum tube computers.

 

Manifold deals with the above limitations as follows:

 

Data types - On export, Manifold automatically will convert modern types into simplified representations that can be stored in a DBF.   For example,  variable-length text data is exported as fixed-length text with 254 characters, since various third party programs do not seem to be able to handle memo fields.  Floating point types will be converted into text.  The conversion can involve data loss, for example as will happen when truncating a long, variable-length text value into a fixed 254 character field.

 

Character encoding - Manifold will write character (text) data into a DBF as UTF-8, that is, as Unicode, and will add a CPG file, for the use of third-party products that can utilize CPG files, describing the encoding within the DBF as UTF-8.   Using UTF-8 makes DBF as written by Manifold incompatible with very old software (1970's to early 1990's), if one can find any such packages that are still running.

 

Text field length optimization -  DBF files cannot store text fields longer than about 250 characters. When Manifold exports a DBF file the system computes the maximum length of each text field, increases that computed length value to the nearest multiple of 8 to accommodate for future edits, and uses that as the DBF field length.  

 

Text value truncation -  Exporting data to a shapefile automatically truncates text values to 250 characters, the maximum allowed.   When text fields are truncated, the log window will report the names of fields containing truncated values as well as the total number of truncated values in the log.

 

File and Field Names - Manifold allows longer file names than DBF.  Manifold will automatically truncate field names into the limited forms allowed by DBF and will eliminate spaces and other disallowed characters.  For example, a field name called Highest Z-value (meter) in a Manifold drawing's table will be converted into a field called HighestZva in the shapefile's DBF.  

 

Notes

Import - Because DBF is such a limited format it is not a great idea to leave data in a DBF file unless we are forced to do that, for example, to allow continued interchange with older applications that insist upon using DBF.   If all we want to do is to harvest data from a DBF it is a better idea to import data from the DBF and to save that data in a Manifold .map format file.

 

Export - DBF files cannot store text fields longer than about 250 characters. When Manifold exports a DBF file (including as part of a shapefile ensemble) the system computes the maximum length of each text field, increases that computed length value to the nearest multiple of 8 to accommodate for future edits, and uses that as the DBF field length.  

See Also

Editing Tables

 

Selection

 

File - Create - New Data Source

 

DBMS Data Sources - Notes

 

SHP, Shapefiles