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.

[PhilMorefield]

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.

I won't pretend to tell you what the "best" approach for this is. But I can tell you how I've done it.

I like to use the win32 library like so:

# first build the Microsoft Office Object library using the PythonWin makepy utility

import win32com.client 
xl = win32com.client.Dispatch("Excel.Application")
xl.Visible = 1 # good for testing things interactively

wb = xlWorkbooks.Add()
xl.ActiveSheet.Name = "Whatever"
datasheet = xl.Worksheets("Whatever")

# set format for text
xl.Columns("A:A").NumberFormat = "@" 

# set format for lots of decimal places
xl.Range("B2:ZZ45").NumberFormat = "0.0000000000" 

# setting a cell value
datasheet.Cells(1, "A").Value = "SampleText" 

xl.ActiveWorkbook.SaveAs("C:\temp\Whatever.xlsx")
xl.ActiveWorkbook.Close(SaveChanges = 1)
xl.Quit()

I've never used Access this way, but I'm sure you could pretty easily copy data from one to the other.

[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