2

I'm trying to export a Table View to Excel (using arcpy). It works well if the Table View doesn't have hidden fields. As soon as I hide the OBJECTID when creating the View, I get a runtime error (Cannot find field '').

When I try to do the same in the ModelBuilder, it works. Any idea why the Python script fails to execute?

arcpy.MakeTableView_management(TEMP_Consumption, TEMP_Consumption_View, "", "", "OBJECTID OBJECTID HIDDEN NONE;Customer_Name Customer_Name VISIBLE NONE")
arcpy.TableToExcel_conversion(TEMP_Consumption_View, ExportFile, "ALIAS", "CODE")

The full error message:

Traceback (most recent call last):
File "C:\Users\Daniel\Desktop\exportconsumptions1.py", line 84, in <module>
arcpy.TableToExcel_conversion(TEMP_Consumption_View, ExportFile, "ALIAS", "CODE")
File "c:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\conversion.py", line 81, in TableToExcel
raise e
ExecuteError: 
Traceback (most recent call last):
File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\TableToExcel.py", line 222, in <module>
arcpy.GetParameter(3))
File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\TableToExcel.py", line 192, in table_to_excel
for row in cursor:
RuntimeError: Cannot find field ''

Failed to execute (TableToExcel).
Improve this question
7
  • Just an idea, export your model to python and see if there is any difference in the syntax?
    – Hornbydd
    Commented Apr 1, 2017 at 16:46
  • Please always place the full error message, with traceback, in the body of the question, using either a quote block or a code block. Even views should have a rowid column, so hiding it may be the root of your issue. The question title is a bit deceiving, since it's not just "a field" but "the rowid field".
    – Vince
    Commented Apr 1, 2017 at 19:45
  • @Hornbydd: I tried that and even copied the exported python syntax. It's exactly the same. The only difference is, when building the model it seems it needs something like a refresh before I can use the view for the export to Excel.
    – DanielSE
    Commented Apr 1, 2017 at 19:51
  • @Vince: I'm aware that the OID is special, but my output Excel file must be without one. Hence the workaround of creating a view (where I can hide fields). It doesn't matter which field is hidden. The error message is the same: If I hide a 'regular' field, I get exactly the same problem. The full error message is:
    – DanielSE
    Commented Apr 1, 2017 at 19:58
  • Please place critical information in the question. It's not fair to those who would answer your question to make them hunt for it in comments.
    – Vince
    Commented Apr 1, 2017 at 20:00

1 Answer 1

Reset to default
1

I am able to reproduce your issue on ArcGIS 10.4.1. This issue can be reproduced using any other field, so it's not OBJECTID specific. My guess is that because you hide a field when creating a table view, it might get misinterpreted by the Table To Excel tool and it gets an empty string internally. There are a couple of workarounds:

Using external xlsxwriter package

I use xlsxwriter Python package for all my Excel-generation related operations. It's much more flexible than the Table To Excel because it uses .xlsx Excel files:

  • Number of columns: Table To Excel: 256; xlsxwriter: 16,384
  • Number of rows: Table To Excel: 65,536; xlsxwriter: 1,048,576

You can create own styles for cells using xlsxwriter, add charts, formulas and many other things. The reason Esri is using the xlwt for the Table To Excel tool is most likely because this package comes built-in into the Python installation. Some of the functionality I mention is also possible to implement using xlwt but it's a lot of work comparing to xlsxwriter.

You can use the arcpy.da.SearchCursor for iterating rows as you need and write rows to Excel. Maybe you would like to use another columns order or add some borders. In a word, make a transition to xlsxwriter whenever you can.

This is everything it takes to export the rows from a feature class using a where_clause:

import xlsxwriter
import arcpy

excel_path = r'C:\GIS\out_fc.xlsx'
workbook = xlsxwriter.Workbook(excel_path)
worksheet = workbook.add_worksheet()

fc = r"C:\GIS\Temp\ArcGISHomeFolder\Default.gdb\cities"
fields = [f.name for f in arcpy.ListFields(fc) if not f.required]

rows = [r for r in arcpy.da.SearchCursor(fc, fields, 'POP1990 > 1000000')]
rows_structured = [list(elem) for elem in rows]
rows_structured.insert(0, fields)

for i, row in enumerate(rows_structured):
    worksheet.write_row(i, 0, row)
workbook.close()

Using the built-in xlwt module

However, if you do need to use the xlwt built-in module, you may like running the Table To Excel Python module on your own to work around the problem with hiding fields. If you print sys.path after importing the arcpy, you will see that there is a path to 'C:\\Program Files (x86)\\ArcGIS\\Desktop10.4\\ArcToolBox\\Scripts'.

The Table To Excel geoprocessing tool is just a plain Python module stored there as TableToExcel.py. You can just import this file and then call the function to generate the Excel. I am able to create an output Excel .xls file without OBJECTID using the table view obtained from the Make Table View GP tool.

import arcpy
import TableToExcel

arcpy.MakeTableView_management(in_table="Database Connections/esrigdb.sde/esrigdb.DBO.Property",
                               out_view="DBO.Property_View",
                               where_clause="", workspace="", field_info="""OBJECTID OBJECTID HIDDEN NONE""")

TableToExcel.table_to_excel(in_table="DBO.Property_View", output=r'C:\GIS\Temp\outHiddenObjId.xls',
                            use_domain_desc=True,use_field_alias=True)
Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.