Copy data from MS Access to MS Excel using Python

MikeMacRae

I've been spending the better part of the weekend trying to figure out the best way to transfer data from an personal geodatabase feature class into an Excel sheet using Python. I've found a few modules that may help (execsql, python-excel), but I'm not sure what the best approach should be.

All I need to do is copy 4 columns of data from access to excel and then format the excel. I have the formatting part solved.

Should I:

• Iterate through the rows using a cursor and somehow load the rows into excel?

• Copy the columns from access to excel?

• Export the whole access table into a sheet in excel?

Thanks for any suggestions.

MikeMacRae

Hey, thanks for replying Phil. I had a look at your code and after some digging, I found a way to do this. I exported my feature class to a dbf table, so I was using that in this script. I do believe, however, that you can use a feature class and still get the same results. I just haven't tested it yet. Here's my code:

import arcpy, xlwt
from arcpy import env
from xlwt import Workbook

# Set the workspace. Location of feature class or dbf file. I used a dbf file.
env.workspace = "C:\data"

# Use row object to get and set field values
cur = arcpy.SearchCursor("SMU_Areas.dbf")

# Set up workbook and sheet
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
book.add_sheet('Sheet 2')

# Set counter
rowx = 0

# Loop through rows in dbf file.
for row in cur:
    rowx += 1
    # Write each row to the sheet from the workbook. Set column index in sheet for each column in .dbf
    sheet1.write(rowx,0,row.ID)
    sheet1.write(rowx,1,row.SHAPE_Area/10000)
    book.save('C:\data\MyExcel.xls')
        

del cur, row