Saving *.dbf as *.xls using Python?

Question

I've been let loose in the workplace to learn python to do things in Arcmap 10. so, I am 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.

• whuber: Given that Excel opens DBF files directly, without loss of information, why do you need to perform a conversion?
• gm70560: 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: 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.
• Chad Cooper: So what exactly is the problem with your posted snippet? Your dbf headers aren't coming through to Excel?
• whuber: That's because you need to write a row of field names: your code only writes the data records one by one.

Answer 1

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

• gm70560: 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.
• Chad Cooper: @gm70560 - If this indeed answered the question, please mark it as answered.
• gm70560: @ Chad Cooper- there's your upvote.
• amasephy: @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!

Answer 2

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")

Answer 3

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).