Simple table to excel conversion in Python

4069
6
08-13-2018 07:25 AM
JonathanPierre
New Contributor II

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'

0 Kudos
6 Replies
JoeBorgione
MVP Emeritus

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.

That should just about do it....
0 Kudos
JimCousins
MVP Regular Contributor

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
New Contributor II

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.  

0 Kudos
DanPatterson_Retired
MVP Emeritus

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
MVP Emeritus
#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()
That should just about do it....
JonathanPierre
New Contributor II

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