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

Ya. dBase. The ending of the file is in dbf. The code I have so far is:

import os, os.path, arcgisscripting
from dbfpy import dbf
filepath = os.path.join(os.path.dirname("E:/Yinsuo/MODIS/EVI.NDVI.STATS/"))
outfile = os.path.join(os.path.dirname("E:/Syed Workspace/Yinsuo.xlsx")) for IntFiles in os.listdir(filepath):
if IntFiles.find("EVI.dbf") != -1:
db = dbf.Dbf(filepath + "/" + IntFiles)
print db — user1603825, Aug 17 '12 at 11:39
You know you can just choose File->Open and select *.dbf for file types to simply open dBase files in Excel? No import or anything required. — Fionnuala, Aug 17 '12 at 11:47
I need to take data from hundreds of different dbf files and compile it into one excel worksheet. Doing this one at a time will take forever. — user1603825, Aug 17 '12 at 14:51

score 3 · Answer 1 · answered Aug 22 '12 at 12:52

3

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.

answered Aug 22 '12 at 12:52

Ethan Furman

63,992
20
159
237

I am having problems to use a field name in the dbf file in place of `some_count`. The dbf file comes from ArcGIS and every field name shown within ArcGIS is refused as `FieldMissingError: 'FID: no such field in table'`. In this case FID is the ID of each row in my file. – FaCoffee Dec 14 '16 at 14:47
@CF84: field names are only accessible as lower case (so `fid` instead of `FID`. – Ethan Furman Dec 14 '16 at 14:51
Same error as above. How can I see what the fields are once the dbf file is read in? – FaCoffee Dec 14 '16 at 14:52
1

@CF84: `print` the table, or `dbf.field_names(thing)` where `thing` is a table or a record. – Ethan Furman Dec 14 '16 at 15:05
Wait, this snippet does not convert from dbf to xls only, it sums the records of a field you specify. To just convert, I assume I need to get rid of the lines from `total` to `total+=...`, correct? – FaCoffee Dec 14 '16 at 15:10
1

@CF84: It's not quite that easy. If you ask a new question I'd be happy to answer it. It's hard to put code in comments. – Ethan Furman Dec 14 '16 at 19:00

score 1 · Answer 2 · answered Aug 17 '12 at 16:34

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

Importing data from dbf to excel spreadsheet using Wing IDE

2 Answers2