Importing data from dbf to excel spreadsheet using Wing IDE

Question

I am trying to move a column of data (mean values) from a dbf file to an excel spreadsheet. I have been trying this with Wing IDE with no success so far. I am not a progamming student and this is a short term assignment. I am stuck on the part where I have to retrieve the file from the specific network drive and copy the data onto my local excel sheet. Help would be great. Thanks

Answer 1

As I understand it, you can use ADODB with Python. You can run a query against a connection to insert into a Excel file from a DBF.

This works in VBA, hopefully you can translate.

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" _
  & "Data Source=z:\docs\myexcel.xlsm;Extended Properties=""Excel 8.0;HDR=No"";"

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon

strsql = "SELECT * INTO [mynewsheet] " _
  & "FROM [dBASE III;DATABASE=z:\docs\].[mydbf.dbf] "
  cn.Execute strsql

Answer 2

You need the Python Excel tools, and I would also recommend my own dbf package.

import dbf
import xlwt

dbf_files = ('file1.dbf','file2.dbf','file3.dbf')

output_xls = xlwt.Workbook()
sheet = output_xls.add_sheet('sheet_name')

for i, filename in enumerate(dbf_files):
    total = 0
    with dbf.Table(filename) as table:
        for record in table:
            total += record.some_count   # some_count being a field name in the dbf files
    sheet.write(i, 0, filename)
    sheet.write(i, 1, total)

output_xls.save('final.xls')

Hopefully this will give you an idea of how to handle your use-case. Let me know if you have any questions.