Simple table to excel conversion in Python

[JonathanPierre]

Good day everyone,

I've used the table to excel tool in model builder but now I want to have it in a form of a python script. Using the ESRI Table to Excel Help paged and I ended up with this.

     # Run the Intersect analysis
    Input_Table = arcpy.Intersect_analysis([IntersectFeature, AreaOfInterest], SpecifiedSites, "ALL", "", "INPUT")
   
    # Report a success message   
    arcpy.AddMessage("Intersect analysis made!")
   
    #Process: Export to CSV table
    arcpy.TableToExcel_conversion(Input_Table, wrk+"\Ouput_Excel_File.xls", "NAME", "CODE")
  
    # Report a success message   
    arcpy.AddMessage("Results exported to a Excel file!")

The intersect analysis works fine but the conversion failed to execute.  Here's the specific error message:

Running script TableToExcel...

Failed script TableToExcel...

　

Traceback (most recent call last):

File "c:\program files (x86)\arcgis\desktop10.2\ArcToolbox\Scripts\TableToExcel.py", line 222, in <module>

arcpy.GetParameter(3))

File "c:\program files (x86)\arcgis\desktop10.2\ArcToolbox\Scripts\TableToExcel.py", line 216, in table_to_excel

workbook.save(output)

File "C:\Python27\ArcGIS10.2\lib\site-packages\xlwt\Workbook.py", line 662, in save

doc.save(filename, self.get_biff_data())

File "C:\Python27\ArcGIS10.2\lib\site-packages\xlwt\CompoundDoc.py", line 261, in save

f = open(file_name_or_filelike_obj, 'w+b')

IOError: [Errno 2] No such file or directory: u'C:\\Users\\pierrej\\Desktop\\GISData\\Ouput_Excel_File.xls'

[JoeBorgione]

You might want to take a look at using Pandas to create Excel files directly...  I've done it, and if I can find the scripts I created, I'll post them.

[JimCousins]

It seems like there is an issue with your parameter entries. 

Try using the Table to Excel tool from the toolbox and see if that provides any insight.

Regards,

Jim

[JonathanPierre]

Thanks,

The Table to Excel tool works perfectly from the toolbox/model builder. However, if I export the script generated from the Model and try to incorporate it in my Script Tool it doesn't work.  

[DanPatterson_Retired]

Jonathan... quit using the users folder

u'C:\\Users\\pierrej\\Desktop\\GISData\\Ouput_Excel_File.xls

it is unicoding stuff and you are using python 2.7.

PRO would be better, and change your workspace to

r"c:\test_folder"

r is for raw formatting if you are specifying paths.

Ouput_Excel_File.xls is missing a t

[JoeBorgione]

#one table to one excel

import pandas as pd
import arcpy
from arcpy import env
fields = ['TaxingEntity', 'ProjectID']
targetXL = r'J:\RDA\ProjectAreaIDs.xls'
arcpy.env.workspace = r'J:\RDA\RDA_TIF.gdb'
table = r'J:\RDA\RDA_TIF.gdb\MyMasterProjectAreaName_Agency'
fields = ['ProjectAreaName', 'ProjectAreaID']
tableArray = arcpy.da.TableToNumPyArray(table,fields)
writer = pd.ExcelWriter(targetXL)
df = pd.DataFrame(tableArray)
df.to_excel(writer,table)
writer.save()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

#use listTables and loop through
# gets fancy with a Numpy array...

import pandas as pd
import arcpy
from arcpy import env
fields = ['TaxingEntity', 'ProjectID']
targetXL = r'J:\RDA\ToBlake.xls'
#
arcpy.env.workspace = r'J:\RDA\Temp2.gdb'
tables = arcpy.ListTables()
writer = pd.ExcelWriter(targetXL)
for table in tables:
    if table == 'AllTables_5_16':
        pass
    elif table == 'NeedTaxEntRecords':
        pass
    elif table == 'BlakesTaxinEntities':
        pass
    else:
        inTable = table
        tableArray = arcpy.da.TableToNumPyArray(inTable,fields)
        df = pd.DataFrame(tableArray)
        df.to_excel(writer,table)
        del tableArray
        del df
        writer.save()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JonathanPierre]

The error was that I didn't double back slashed the path of my file output. Thanks everyone.