2

I've been spending the better part of the weekend trying to figure out the best way to transfer data from an MS Access table into an Excel sheet using Python. I've found a few modules that may help (execsql, python-excel), but with my limited knowledge and the modules I have to use to create certain data (I'm a GIS professional, so I'm creating spatial data using the ArcGIS arcpy module into an access table)

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.

Improve this question
0

5 Answers 5

Reset to default
3

I eventually found a way to do this. I thought I'd post my code for anyone who may run into the same situation. I use some GIS files, but if you don't, you can set a variable to a directory path instead of using env.workspace and use a cursor search instead of the arcpy.SearchCursor function, then this is doable.

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

You are saving the file once per row, instead of just once at the end. Unindent the book.save(.....) line.
This question would probably be more useful over at GIS
Just an FYI, for the newer versions of Python you need to double slash.
@DARSIN Thanks. You could also just convert to raw string as well.
1

I currently use the XLRD module to suck in data from an Excel spreadsheet and an insert cursor to create a feature class, which works very well.

You should be able to use a search cursor to iterate through the feature class records and then use the XLWT Python module (http://www.python-excel.org/) to write the records to Excel.

Improve this answer

1 Comment

Hey Daniel, this is eventually what I did. Thanks for the direction. I'll answer my question with my code.
1

You can use ADO to read the data from Access(Here are the connection strings for Access 2007+(.accdb files) and Access 2003-(.mdb files)) and than use Excel's Range.CopyFromRecordset method(assuming you are using Excel via COM) to copy the entire recordset into Excel.

Improve this answer

1 Comment

Hey thanks Idan. I found a solution, but I looked through your links and they may come useful for some ideas I have for the future.
1

The best approach might be to not use Python for this task.

You could use the macro recorder in Excel to record the import of the External data into Excel. After starting the macro recorder click Data -> Get External Data -> New Database Query and enter your criteria. Once the data import is complete you can look at the code that was generated and replace the hard coded search criteria with variables.

Improve this answer

1 Comment

Hey Mitch. Thanks for taking the time to answer. I'm kind of handcuffed with python. I'm creating an ArcGIS tool and it can only be created using python. Their files are called feature classes, which uses Access as the underlying database. My end user is only capable of using the data I create in excel. This might be too ambitious for python?
1

Another idea - how important is the formatting part? If you can ditch the formatting, you can output your data as CSV. Excel can open CSV files, and the CSV format is much simpler then the Excel format - it's so simple you can write it directly from Python like a text file, and that way you won't need to mess with Office COM objects.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.