Exporting data from DBF to Excel template using ModelBuilder?

Question

I have created a model in ModelBuilder that uses the iterator tool to run through a polygon layer and clip a raster based on the polygon's unique name in the attribute table. The model then copies the rows of the clipped raster's attribute table and creates a dbf table saved with the polygon name.

I now need the dbf table data to be exported or copied and pasted into an excel workbook that contains formulas in a separate worksheet to analyze the data.

Is there any way to do this in ModelBuilder?

I have used the do.TransferSpreadsheet command in VBA to transfer data from MS Access to an Excel Template in past. The command works great for automated analysis of complex data and would like something similar for ArcGIS.

Answer 1

In python it's possible. I have done something similar.

This tutorial was very helpful for me: basic-excel-driving-with-python

With this, you can open an existing excel file and add/change values to whatever you want.

Once you have setup a python script that works for you, you can change it for reading parameters so you can add it as a script in ArcToolbox. Then you can call it in ModelBuilder.

Some code that can help you, basically it export to excel an attribute table:

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()

#Fields List
desc=arcpy.Describe(LayerToExport)
Fields=[]
for Fi in desc.fields:
    if Fi.type != "Blob" and Fi.type != "Geometry": 
        Fields.append(Fi.Name)

iFi = 1
for Fi in Fields:
    excel.ActiveSheet.Cells(1,iFi).Value = Fi
    iFi+=1

#Each row
rows = arcpy.SearchCursor(LayerToExport) 
iRow=2
for row in rows:
    iFi = 1
    for Fi in Fields:
        excel.ActiveSheet.Cells(iRow,iFi).Value = row.getValue(Fi)
        iFi+=1
    iRow+=1

# Clean up cursor and row objects
del row
del rows
wb.SaveAs(outFile)          
wb.Close(False) 

Note: As this use win32com, VBA function are supported. So it's possible you can use your "TransferSpreadsheet" function.

Answer 2

You do not mention the version of ArcGIS Desktop that you are using so I will assume that 10.2 or later is an option.

I recommend that you investigate adding the Table To Excel tool to your model/script.