topic Simple table to excel conversion in Python in Python Questions

Simple table to excel conversion in Python

JonathanPierre — Mon, 13 Aug 2018 14:25:43 GMT

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'

Re: Simple table to excel conversion in Python

JoeBorgione — Mon, 13 Aug 2018 15:33:31 GMT

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.

Re: Simple table to excel conversion in Python

JimCousins — Mon, 13 Aug 2018 15:34:42 GMT

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

Re: Simple table to excel conversion in Python

DanPatterson_Retired — Mon, 13 Aug 2018 15:46:58 GMT

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

Re: Simple table to excel conversion in Python

JoeBorgione — Fri, 10 Dec 2021 22:14:04 GMT

#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()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Simple table to excel conversion in Python

JonathanPierre — Tue, 21 Aug 2018 12:26:06 GMT

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.

Re: Simple table to excel conversion in Python

JonathanPierre — Tue, 21 Aug 2018 15:36:14 GMT

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