Query DBF dBASE Files with SQL in Your Browser

Query DBF dBASE Files with SQL in Your Browser

If you have ever downloaded a shapefile bundle, you have already met DBF. It is the dBASE attribute table used by ESRI shapefiles and a common output of legacy business systems.¹ The dBASE format dates to the early 1980s, and its compact table structure still appears in GIS downloads, government data portals, and older enterprise exports.² The SQL Workbench decodes dBASE files in the browser and loads them into DuckDB, so you can run SQL against GIS attribute tables, legacy database exports, and mainframe data extracts without any desktop GIS software.

Where DBF files still appear

The dBASE file format dates to the 1980s but remains actively used in geospatial workflows. Every ESRI shapefile consists of at least three component files: a .shp geometry file, a .shx index file, and a .dbf attribute table.² The .dbf holds all the non-geometry columns, such as census identifiers, land use codes, and population counts, that you join to geometry when making a map. Beyond GIS, DBF files appear in legacy enterprise systems: older ERP outputs, mainframe extracts converted to dBASE format, and government open data portals that still publish in shapefile bundles. Because these systems are not always easy to migrate, DBF files persist in data workflows long after the tooling around them has changed.

How the workbench loads DBF files

DBF files pass through a JavaScript parser that reads the dBASE header, extracts column names and types, and decodes the record bytes. This parsing step runs entirely in the browser, so the raw file bytes never leave your machine.³ Column names in dBASE are limited to 10 characters, so they may be truncated compared to the full attribute names you see in GIS software.

DBF type codes and encoding

The parser infers types from the dBASE type codes: C (character) becomes VARCHAR, N (numeric) becomes DOUBLE, D (date) becomes VARCHAR with an ISO date string, and L (logical) becomes BOOLEAN.⁴ Character fields in dBASE are stored as code page characters, and the dBASE header records a language-driver identifier used to interpret them.⁴ The parser attempts to detect encoding from the file header and convert to UTF-8. If accented characters appear garbled in the workbench, the file uses an encoding not identified in its header.

Numeric N fields keep any decimal precision declared in the header, so a field typed as N with two decimal places arrives as DOUBLE with its fractional part intact rather than as a rounded integer. When a column mixes numeric and blank values, dBASE stores the blanks as spaces, and the parser maps those empty cells to NULL so your aggregations do not count them as zeroes.

Practical patterns for GIS attribute tables

When working with the attribute table of a shapefile, the most common tasks are inspecting available fields, filtering by attribute values, and computing summary statistics. Run DESCRIBE to see all column names; they may be truncated to 10 characters in dBASE format.³ To find which land use categories appear in a parcel dataset: SELECT luse_code, COUNT(*) FROM parcels GROUP BY luse_code ORDER BY COUNT(*) DESC. To compute average population density by county: SELECT county_fips, AVG(pop_per_km2) FROM census_blocks GROUP BY county_fips. Export the result to CSV for use in another GIS tool or to Excel for reporting. Note that the geometry (spatial coordinates) lives in the companion .shp file, not in the .dbf; the workbench queries the attribute table only.⁵

Handling character encoding in older DBF files

Handling character encoding problems in older DBF files requires understanding the language-driver information stored with the file. The dBASE header records a language-driver identifier, and dBASE Plus language drivers describe which character set and language rules apply to a table.⁶ Older files commonly use DOS/OEM code pages such as CP437 or CP850.⁶ If the declared code page does not match what the file actually uses, the JavaScript parser converts characters using the wrong mapping and produces garbled output for any character outside the ASCII range.

Converting to UTF-8 before loading

For files with garbled accented characters, convert the file to UTF-8 before loading it in the workbench. On Linux and macOS, iconv -f cp1252 -t utf-8 input.dbf > output.dbf converts from Windows-1252 to UTF-8. Some DBF files from Western European GIS publishers use CP1252 but declare no code page in the header; try Windows-1252 as the source encoding first, then CP437 or CP850 if that produces garbled output.

Querying attributes before GIS cleanup

Once the text reads correctly, use SQL to reduce the attribute table before opening it in GIS software. Filtering by state, category, or population in the workbench gives you a smaller CSV or Parquet export to join back to geometry later. You can also compute summary statistics, identify outlier values, and flag records with missing attributes, all of which are tedious to do manually in a GIS attribute table but straightforward with a few SQL queries.

Working with shapefile bundles and the DBF row order relationship

In a shapefile bundle, the .dbf attribute table relates to the .shp geometry by row order: row 1 in the .dbf matches feature 1 in the .shp.¹ There is no explicit foreign key column; GIS software maintains that relationship internally. When you query the .dbf in the workbench, you get every attribute column but no geometry. The FID or OBJECTID column in most files tells you which geometry in the companion .shp each row corresponds to, but the workbench cannot display or query that geometry.

For workflows that combine SQL attribute filtering with GIS spatial analysis, query and filter the .dbf in the workbench first, note the FID values of the rows you want, then use QGIS or GeoPandas to select those features from the companion .shp using the FID list. Export the filtered attribute data to CSV and use the Join Attributes by Field Value tool in QGIS to attach it to the shapefile features. Filtering the attribute side reveals what a shapefile's attributes actually hold and narrows a large shapefile bundle down to the rows you need before you touch a desktop GIS tool at all.

When to use this

Use this when you have the .dbf component of a shapefile, a GIS data download, or a legacy business system export and want to run SQL aggregations or filters on the attribute data.

Examples

DESCRIBE parcels;

SELECT land_use, COUNT(*) AS n
FROM parcels
GROUP BY land_use
ORDER BY n DESC;

SELECT fips_code, county_name, pop_2020
FROM counties
WHERE pop_2020 > 1000000
ORDER BY pop_2020 DESC;

SELECT state_fips,
       COUNT(*) AS n_counties,
       SUM(area_km2) AS total_area
FROM counties
GROUP BY state_fips
ORDER BY total_area DESC
LIMIT 10;