Moving data to SQL, CSV, Pandas etc.¶
CSV¶
This uses the standard library csv
module:
"""Export to CSV."""
import sys
import csv
from dbfread import DBF
table = DBF('files/people.dbf')
writer = csv.writer(sys.stdout)
writer.writerow(table.field_names)
for record in table:
writer.writerow(list(record.values()))
The output is:
NAME,BIRTHDATE
Alice,1987-03-01
Bob,1980-11-12
Pandas Data Frames¶
"""
Load content of a DBF file into a Pandas data frame.
The iter() is required because Pandas doesn't detect that the DBF
object is iterable.
"""
from dbfread import DBF
from pandas import DataFrame
dbf = DBF('files/people.dbf')
frame = DataFrame(iter(dbf))
print(frame)
This will print:
BIRTHDATE NAME
0 1987-03-01 Alice
1 1980-11-12 Bob
The iter()
is required. Without it Pandas will not realize that it
can iterate over the table.
Pandas will create a new list internally before converting the records to data frames. This means they will all be loaded into memory. There seems to be no way around this at the moment.
dataset (SQL)¶
The dataset package makes it easy
to move data to a modern database. Here’s how you can insert the
people
table into an SQLite database:
"""
Convert a DBF file to an SQLite table.
Requires dataset: https://dataset.readthedocs.io/
"""
import dataset
from dbfread import DBF
# Change to "dataset.connect('people.sqlite')" if you want a file.
db = dataset.connect('sqlite:///:memory:')
table = db['people']
for record in DBF('files/people.dbf', lowernames=True):
table.insert(record)
# Select and print a record just to show that it worked.
print(table.find_one(name='Alice'))
(This also creates the schema.)
dbf2sqlite¶
You can use the included example program dbf2sqlite
to insert
tables into an SQLite database:
dbf2sqlite -o example.sqlite table1.dbf table2.dbf
This will create one table for each DBF file. You can also omit the
-o example.sqlite
option to have the SQL printed directly to
stdout.
If you get character encoding errors you can pass --encoding
to
override the encoding, for example:
dbf2sqlite --encoding=latin1 ...