Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

i've been let loose in the workplace to learn python to do things in Arcmap10. so, i've learning python as i go and trying to remember the programming i have done. where i am in this project is converting a dbf, or csv, xls in a simple fashion. from there, all the files will be copied together into one file. i've got the all-in-one xls working, but i can't find an easy, simple dbf to xls solution.

i condensed code found here: http://blog.gmane.org/gmane.comp.python.education/page=12

into:

from xlwt import Workbook
import dbfpy.dbf

input0 = '...file.dbf'
output = '...file.xls'

def test1():
    dbf = dbfpy.dbf.Dbf(input0)
    book = Workbook()
    sheet1 = book.add_sheet('Sheet 1')
    for row in range(len(dbf)):
        for col in range(2):#chop to two for my purposes, gm 
            sheet1.row(row).write(col, dbf[row][col])
    book.save(output)

test1()

this works, minus the lack of field names.

share|improve this question
2  
Given that Excel opens DBF files directly, without loss of information, why do you need to perform a conversion? –  whuber May 9 '12 at 18:04
    
you use arcmap lately? the quickexport_interop doesn't support xls or xlsx. dbfpy one does one sheet (i think). csv only has one sheet, as well. I'm tryin to summarize many files at once, some with 10k+ rows. Within arcmap with the file open, this takes way too long. –  gm70560 May 9 '12 at 18:12
    
to clear things up: yes, i know that excel will open dbf's and csv's. if i can get QuickExport results into xls, i know how to merge them by column into one file. i'll take suggestions for merging dbf's, but i'll still need to have it end up in xls. once done with my end, the file will get all pretty'd up, formatted and sent out to clients, managers, etc. –  gm70560 May 9 '12 at 18:24
    
So what exactly is the problem with your posted snippet? Your dbf headers aren't coming through to Excel? –  Chad Cooper May 9 '12 at 19:32
1  
That's because you need to write a row of field names: your code only writes the data records one by one. –  whuber May 9 '12 at 19:49

3 Answers 3

up vote 8 down vote accepted

Like whuber says, you have to write out the headers explicitly. I loaded up dbfpy and xlwt in a virtualenv and ran this:

from xlwt import Workbook, easyxf
import dbfpy.dbf
from time import time

def test1():
    dbf = dbfpy.dbf.Dbf("pipelines.dbf", readOnly = True)

    header_style = easyxf('font: name Arial, bold True, height 200;')

    book = Workbook()
    sheet1 = book.add_sheet('Sheet 1')

    for (i, name) in enumerate(dbf.fieldNames):
        sheet1.write(0, i, name, header_style)

    for (i, thecol) in enumerate(dbf.fieldDefs):
        name, thetype, thelen, thedec = str(thecol).split()
        colwidth = max(len(name), int(thelen))
        sheet1.col(i).width = colwidth * 310

    for row in range(1,len(dbf)):
        for col in range(len(dbf.fieldNames)):
            sheet1.row(row).write(col, dbf[row][col])

    book.save("pipelines-xls.xls")

if __name__ == "__main__":
    start = time()
    test1()
    end = time()
    print end - start

This gives me headers in my xls:

enter image description here

share|improve this answer
    
thank you! i was monkeying around with things, but for (i, name) in enumerate(dbf.fieldNames): sheet1.write(0, i, name) was what i was attempting with much fail. can't upvote, sorry. –  gm70560 May 9 '12 at 21:22
    
@gm70560 - If this indeed answered the question, please mark it as answered. –  Chad Cooper May 10 '12 at 13:56
    
@ Chad Cooper- there's your upvote. –  gm70560 Aug 8 '12 at 2:59
1  
@ChadCooper - Thanks for the code, it was very helpful. A quick note... I realized that at least for me your final for loop was skipping the first row of data it was supposed to have when writing the xls. I believe this is because the headers in the dbf are above the 0 row, while in the xls they become the 0 row. I changed the range to start at 0 and did "sheet1.row(row + 1) which makes it write to row 1 but still starts reading the dbf from row 0. Hope this helps others! –  amasephy Oct 6 '12 at 11:40

Export an Excel table to .dbf format in ArcGIS.

In Excel 2007, save the table as, 'Excel 97-2003 Workbook' (.xls format).
Navigate to the .xls table location through ArcCatalog, and expand the file to view the worksheets.
Right-click on the worksheet representing the table. Select Export > To dBase (single).

share|improve this answer

At ArcGIS 10.2 for Desktop a new tool called Table To Excel (Conversion) was introduced to export a table to an Excel file.

Summary

Converts a table to a Microsoft Excel file.

Usage

• Table To Excel is able to convert only to Microsoft Excel 5.0/95 Workbook (.xls) format.

Consequently, your Python code can now sometimes be as simple as:

arcpy.TableToExcel_conversion("C:/temp/SumStats.dbf","C:/temp/test.xls","NAME","CODE")
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.