Agent skill

DBF Data Analysis

This skill should be used when the user asks to "analyze DBF files", "read DBF data", "query DBF database", "convert DBF to Parquet", "analyze Thai accounting data", "explore legacy database", "run DuckDB queries on DBF", or mentions DBF, Parquet conversion, or Thai legacy accounting systems. Provides comprehensive guidance for reading, converting, and analyzing Thai legacy DBF accounting databases.

View SKILL.md on GitHub Repository
Stars 163
Forks 31

Install this agent skill to your Project

npx add-skill https://github.com/majiayu000/claude-skill-registry/tree/main/skills/data/dbf-data-analysis

SKILL.md

DBF Data Analysis

Toolkit for analyzing Thai legacy DBF accounting databases using roonpoo, Parquet, and DuckDB.

Overview

This skill enables analysis of legacy Thai accounting systems that use DBF (dBase) files with TIS-620/CP874 encoding. The workflow involves:

  1. Reading DBF - Use roonpoo library to read DBF files with proper Thai encoding
  2. Converting to Parquet - Transform DBF to columnar Parquet format for efficient querying
  3. Querying with DuckDB - Run SQL analytics on Parquet files

Environment Setup

Before starting analysis, ensure the roonpoo library is available:

bash
cd libs/python
uv sync

For Parquet conversion and DuckDB queries:

bash
uv pip install pyarrow duckdb

Reading DBF Files

Basic Usage with roonpoo

python
from roonpoo import DBF

# Stream records (memory-efficient)
for record in DBF('path/to/file.DBF', encoding='tis-620'):
    print(record)

# Preload all records
table = DBF('path/to/file.DBF', encoding='tis-620', preload=True)
print(table.records[0])

Key Parameters

Parameter Description
encoding Use 'tis-620' or 'cp874' for Thai text
preload Load all records into memory
ignore_missing_memo Skip if .FPT/.DBT memo file missing
char_decode_errors 'strict', 'ignore', or 'replace'

Inspect Table Structure

python
table = DBF('file.DBF', encoding='tis-620')

# Metadata
print(f"Version: {table.dbversion}")
print(f"Last modified: {table.date}")
print(f"Records: {table.header.numrecords}")

# Fields
for field in table.fields:
    print(f"{field.name}: type={field.type}, len={field.length}")

Field Types

Type Description
C Character (string)
N Numeric
D Date
L Logical (boolean)
M Memo (requires .FPT/.DBT)

Converting DBF to Parquet

Use the conversion script at scripts/dbf_to_parquet.py:

bash
uv run python scripts/dbf_to_parquet.py /path/to/DATA/*.DBF -o /path/to/output/

Or inline:

python
from roonpoo import DBF
import pyarrow as pa
import pyarrow.parquet as pq
from decimal import Decimal

def convert_dbf_to_parquet(dbf_path, output_path):
    table = DBF(dbf_path, encoding='tis-620', char_decode_errors='replace')
    records = list(table)

    columns = {f.name: [] for f in table.fields}
    for rec in records:
        for field in table.fields:
            val = rec.get(field.name)
            if isinstance(val, Decimal):
                val = float(val)
            columns[field.name].append(val)

    arrow_table = pa.table(columns)
    pq.write_table(arrow_table, output_path)
    return len(records)

Querying with DuckDB

Setup

python
import duckdb
con = duckdb.connect()
parquet_dir = 'path/to/parquet/files'

Common Query Patterns

Query single file:

sql
SELECT * FROM 'asParquet/ARMST.parquet' LIMIT 10

List all tables with row counts:

sql
SELECT
    replace(filename, 'path/', '') as file,
    count(*) as rows
FROM parquet_scan('asParquet/*.parquet', filename=true)
GROUP BY filename
ORDER BY rows DESC

Cross-table JOIN:

sql
SELECT
    a.ACCID,
    m.COMP,
    COUNT(*) as txn_count
FROM 'asParquet/ARTR.parquet' a
JOIN 'asParquet/ARMST.parquet' m ON a.ACCID = m.ACCID
GROUP BY a.ACCID, m.COMP
ORDER BY txn_count DESC

Schema inspection:

sql
DESCRIBE SELECT * FROM 'asParquet/ARTR.parquet'

Common Thai Accounting Tables

Table Description Key Fields
ARMST Customer master ACCID, COMP, NAME, TEL
APMST Vendor master ACCID, COMP, NAME
ARTR AR transactions DOCNO, DATEDOC, ACCID, AMOUNT
APTR AP transactions DOCNO, DATEDOC, ACCID, AMOUNT
GLTR GL transactions GLID, DEBIT, CREDIT
GLTRHD GL headers DOCNO, DATEDOC
INVLOC Inventory location PCODE, LOCID, QTY

Workflow Example

Complete analysis workflow:

python
from roonpoo import DBF
from pathlib import Path
import duckdb

# 1. Explore DBF structure
data_dir = Path('sample_company/ALLDATA/DATA2011')
for dbf_file in sorted(data_dir.glob('*.DBF'))[:5]:
    table = DBF(dbf_file, encoding='tis-620')
    print(f"{dbf_file.name}: {table.header.numrecords} records")

# 2. Convert key tables to Parquet
# (use scripts/dbf_to_parquet.py)

# 3. Query with DuckDB
con = duckdb.connect()
result = con.execute("""
    SELECT GLID, SUM(DEBIT) as total_debit, SUM(CREDIT) as total_credit
    FROM 'asParquet/GLTR.parquet'
    GROUP BY GLID
    ORDER BY total_debit DESC
    LIMIT 10
""").fetchdf()
print(result)

Handling Encoding Issues

For files with encoding problems:

python
# Replace invalid characters
table = DBF('file.DBF', encoding='tis-620', char_decode_errors='replace')

# Or ignore them
table = DBF('file.DBF', encoding='tis-620', char_decode_errors='ignore')

Additional Resources

Scripts

  • scripts/dbf_to_parquet.py - Batch convert DBF files to Parquet
  • scripts/inspect_dbf.py - Inspect DBF structure and sample data

References

  • references/table-schemas.md - Common Thai accounting table schemas
  • references/query-patterns.md - Advanced DuckDB query patterns

Didn't find tool you were looking for?

Be as detailed as possible for better results