Reading DBF files with pyodbc

I DID IT!!!!

There where several problems with what I was doing so, here I come with what I did to solve it (after implementing it the first time with Ethan Furman's solution)

The first thing was a driver problem, it turns out that the Windows' DBF drivers are 32 bits programs and runs on a 64 bits operating system, so, I had installed Python-amd64 and that was the first problem, so I installed a 32bit Python.

The second issue was a library/file issue, according to this, dbf files in VFP > 7 are diferent, so my pyodbc library won't read them correctly, so I tried some OLE-DB libraries with no success and I decided to to it from scratch.

Googling for a while took me to this post which finally gave me a light on this

Basically, what I did was the following:

import win32com.clientconn = win32com.client.Dispatch('ADODB.Connection')db = 'C:\\Profit\\profit_a\\ARMM'dsn = 'Provider=VFPOLEDB.1;Data Source=%s' % dbconn.Open(dsn)cmd = win32com.client.Dispatch('ADODB.Command')cmd.ActiveConnection = conncmd.CommandText = "Select * from factura, reng_fac where factura.fact_num = reng_fac.fact_num AND factura.fact_num = 6099;"rs, total = cmd.Execute() # This returns a tuple: (<RecordSet>, number_of_records)while total:    for x in xrange(rs.Fields.Count):        print '%s --> %s' % (rs.Fields.item(x).Name, rs.Fields.item(x).Value)    rs.MoveNext()        #<- Extra indent    total = total - 1

And it gave me 20 records which I checked with DBFCommander and were OK

First, you need to install pywin32 extensions (32bits) and the Visual FoxPro OLE-DB Provider (only available for 32bits), in my case for VFP 9.0

Also, it's good to read de ADO Documentation at the w3c website

This worked for me. Thank you very much to those who replied

I would use my own dbf package and the code would go something like this:

import dbffrom glob import globfor dbf_file in glob(r'p:\data\*.dbf'):    with dbf.Table(dbf_file) as table:        for record in table:            do_something_with(record)

A table is list-like, and iteration through it returns records. A record is list-, dict-, and obj-like, and iteration returns the values; besides iteration through the record, individual fields can be accessed either by offset (record[0] for the first field), by field-name using dict-like access (record['some_field']), or by field-name using obj.attr-like access (record.some_field).

If you just wanted to dump the contents of each dbf file into a csv file you could do:

for dbf_file in glob(r'p:\data\*.dbf'):    with dbf.Table(dbf_file) as table:        dbf.export(table, dbf_file)

I know this doesn't directly answer your question, but might still help. I've had lots of issues using ODBC with VFP databases and I've found it's often much easier treating the VFP tables as free tables when possible.

Using Yusdi Santoso's dbf.py and glob, here's some code to open each table in a directory and run through each record.

import globimport osimport dbfos.chdir("P:\\data")for file in glob.glob("*.dbf"):    table = dbf.readDbf(file)    for row in table:         #do stuff