topic Re: Table to Excel within Python script in Python Questions

Table to Excel within Python script

CoyPotts1 — Sat, 11 Dec 2021 07:29:15 GMT

I'm trying to export my feature class to a table, and then also create a copy as an Excel document. The following code keeps kicking back with an error saying that I am using the wrong file type.

Error message:

"Failed to execute. Parameters are not valid.

ERROR 000814: Invalid file type

Failed to execute (TableToExcel)."

Code:

##########
# Create a file geodatabase for attribute tables to be saved in
##########


# Set local variables - uses variables derived earlier in script
attFolder = mapFolder + "/" + "6)Deliverables/4)Feature_Class_Attribute_Tables/" + version
outATTgdb = 'attribute_tables.gdb'

# Execute CreateFileGDB
arcpy.CreateFileGDB_management(attFolder, outATTgdb, 'CURRENT')


##########
# General Features export
##########

# Set local variables
inTable = generalFeatures
exportLoc = attFolder + "/" + outATTgdb
outTable = "general_attributes"
outXLS = "general_attributes.xls"

# Execute TableToTable
arcpy.TableToTable_conversion(inTable, exportLoc, outTable)

arcpy.env.workspace = 'attFolder'

# Execute TableToExcel
arcpy.TableToExcel_conversion(inTable, outXLS)

I've tried modifying this a bit, with no positive results. For the outXLS variable I tried to make it equal the entire save location, and then removed the arcpy.env.workspace variable, but everything that I have tried results in the same "invalid file type" error.

Any recommendations?

Thanks in advance!

Re: Table to Excel within Python script

BlakeTerhune — Tue, 16 Jun 2015 21:22:53 GMT

The TableToExcel tool requires an input of a table view, not just the path to a table. Try making the table view first, then use that as input for the table to Excel process.

Re: Table to Excel within Python script

IanMurray — Tue, 16 Jun 2015 21:25:23 GMT

What sort of table is your input for Table to Excel?

Re: Table to Excel within Python script

DarrenWiens2 — Tue, 16 Jun 2015 21:26:31 GMT

This points to a string 'attFolder':

arcpy.env.workspace = 'attFolder'

but you want the variable attfolder:

arcpy.env.workspace = attFolder

Re: Table to Excel within Python script

DanPatterson_Retired — Tue, 16 Jun 2015 21:35:24 GMT

just to make sure you aren't using 64 bit processing

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

not supported

Re: Table to Excel within Python script

BlakeTerhune — Wed, 17 Jun 2015 15:07:07 GMT

Good spot, Darren. On that note, the Python OS module can create file paths more reliably than concatenating strings. Try os.path.join()

Re: Table to Excel within Python script

CoyPotts1 — Thu, 18 Jun 2015 19:27:53 GMT

I'll have to play around with the table view.

Essentially I am porting over a very large model builder model. Following the workflow in order as it is in Model Builder, I just used the same table name thinking it would work. I'll try this with the table view option when I get a little more time to tinker with it and I'll respond with my results.

Thanks!

Re: Table to Excel within Python script

CoyPotts1 — Thu, 18 Jun 2015 19:31:37 GMT

I am taking a feature class and running the Table to Table tool to save it as a data table. Then I am taking that data table and exporting it to Excel.

Re: Table to Excel within Python script

CoyPotts1 — Thu, 18 Jun 2015 20:11:05 GMT

Good catch, Darren.

Blake, how would I construct this when I am using variables and added strings? I didn't fully understand the examples in the provided link. Also, why would concatenating strings be unreliable? Just trying to understand.

Thanks to both of you!

Re: Table to Excel within Python script

CoyPotts1 — Thu, 18 Jun 2015 20:15:19 GMT

I am indeed using 64 bit background processing. Do you have any suggestions as to a workaround that doesn't result in me and everyone else that would use this tool having to install 32 bit libraries on top of the 64 bit libraries?

Thanks!

Re: Table to Excel within Python script

DanPatterson_Retired — Thu, 18 Jun 2015 20:26:17 GMT

Coy

from the link I provided

Background Geoprocessing (64-bit) is available as a separate installation on top of ArcGIS for Desktop. The following information only applies if you have the Background Geoprocessing (64-bit) product installed; otherwise, background processing is done in 32 bit.

and the recommended workaround

The following data types are unsupported in 64-bit processing:

Personal geodatabase (.mdb)
Excel tables (.xls, .xlsx)

If your workflow involves any of the above data types, you can execute the tool in the foreground by disabling background processing or convert your data to a supported type, then execute the tool in the background.

Re: Table to Excel within Python script

BlakeTerhune — Sat, 11 Dec 2021 07:29:17 GMT

"Unreliable" may have been the wrong word. It tends to be harder to read, which could make it unreliable. It's easy to get messed up on the slashes and get one the wrong way, or accidentally add an extra one. Plus it's also less flexible than os.path. For example, instead of using string concatenation like this

attFolder = mapFolder + "/" + "6)Deliverables/4)Feature_Class_Attribute_Tables/" + version
outATTgdb = 'attribute_tables.gdb'
exportLoc = attFolder + "/" + outATTgdb

You can use os.path to define your pieces separately and put them all together in a more readable way without worrying about the slashes.

mapFolder = r"C:\temp"
version = "v99"
outATTgdb = "attribute_tables.gdb"

attFolder = os.path.join(mapFolder, "6)Deliverables", "4)Feature_Class_Attribute_Tables", version)
exportLoc = os.path.join(attFolder, outATTgdb)

Notice the r in front of the string for mapFolder. That specifies the string as "raw" so a single backslash doesn't need a second escape backslash. I find it easier this way so you can just copy/paste directory paths.

Re: Table to Excel within Python script

CoyPotts1 — Fri, 19 Jun 2015 15:44:56 GMT

I have background geoprocessing turned off, so I'm wondering if this isn't the issue.

Re: Table to Excel within Python script

CoyPotts1 — Fri, 19 Jun 2015 16:43:13 GMT

Okay, that makes more sense now. Thanks for the clarification.

Re: Table to Excel within Python script

BlakeTerhune — Sat, 11 Dec 2021 07:29:20 GMT

I just tried it without a Table View and it worked. Here's my code.

import arcpy
import os

myDir = r"C:\Temp"
myGDB = "TEMP.gdb"
myTable = os.path.join(myDir, myGDB, "SomeTableName")
myExcel = os.path.join(myDir, "myExcel.xls")

arcpy.TableToExcel_conversion(myTable, myExcel)

Re: Table to Excel within Python script

CoyPotts1 — Sat, 11 Dec 2021 07:29:22 GMT

I moved past this portion of the script and on to a different part that pulls the feature classes exactly how they are in the map and does the exact same thing (TableToTable and then TableToExcel). That part works just fine, so I'm thinking that there's no compatibility issues going on.

# Export TableToTable & TableToExcel

# Set Local Variables
exportTable = updatedTable # This table is the result of the summary statistics tool in the previous step
exportLoc = kmfFolder + "/" + outKMFgdb
outTable = "general_KMF"
outXLS = "general_KMF.xls"

# Execute TableToTable
arcpy.TableToTable_conversion(exportTable, exportLoc, outTable, "", "", "") # this part works fine

# Set Local Variables
inTable = outTable # hoping to grab the result of the TableToTable function above

# Execute TableToExcel
arcpy.TableToExcel_conversion(inTable, outXLS)

In this instance, the TableToExcel keeps kicking back saying that the file type is invalid just as it did before, though. I've tried various ways of setting the inTable variable in line 13, but none of them have worked. I also tried to create a table view between the two tool functions and it still said invalid file type. I can't hard code the variable name because the location will be variable depending on the name of the map. Essentially the kmfFolder variable grabs the maps file location and adds a few characters that are standard between maps, and that is where everything is saved for the current map document.

Examples of what I've tried using for the inTable variable:

inTable = outKMFgdb + "/" + outTable

inTable = exportLoc + "/" + outTable

inTable = (combo of all above) + "/general_KMF"

inTable = (combo of all above) + "/general_KMF.dbf"

etc...

*NOTE*: I intend on implementing the os.path.join suggestion mentioned above, but I'm just trying to get it working for now. Since the method I am using works in other areas of the script, especially in areas that due essentially the same thing, I don't think it's what's causing this particular issue.

Re: Table to Excel within Python script

DanPatterson_Retired — Mon, 29 Jun 2015 20:27:19 GMT

You should split this off into a new thread. From the original thread, no apparent resolution seems to be found. You should close it by turning it into a discussion or marking it assumed read or mark one of the threads as providing the answer. At 15+ posts long, it is way too long to follow and new requests provide a confusing fork in the road

Re: Table to Excel within Python script

CoyPotts1 — Sat, 11 Dec 2021 07:29:25 GMT

I was looking at this the wrong way. I was assuming that the error was referring to the input table that was incorrect, but it was actually referring to the output file as being incorrect. Instead of simply giving the output a name, I gave it a save location with a name, and it worked just fine. See the code below:

# Set local variables
inTable = outTable
outXLS = kmfFolder + "/general_KMF.xls" #this is where I had to give the file path and then the file name

# Execute TableToExcel
arcpy.TableToExcel_conversion(inTable, outXLS)
ersion(inTable, outXLS)

Re: Table to Excel within Python script

CoyPotts1 — Mon, 29 Jun 2015 20:32:27 GMT

Duly noted for future reference. I ended up resolving the issue, though. Please see the code pasted below.

Thanks again for your help in this thread and all others. I've seen your name quite a bit in some of my threads, so thank you for all of your time.

Re: Table to Excel within Python script

DougHaller1 — Tue, 13 Jun 2017 17:23:15 GMT

Hi Coy

Did you resolve all the issues discussed above? If so, would you be willing to share the script?

I'm running into several similar challenges. I would like to do the following: convert all the shapefiles in a gdb into spreadsheets and store the spreadsheets in their own folder or gdb. Eventually, I would like to convert this to a script tool that includes parameters for which gdb to search for shapefiles and where to store the final spreadsheets.

Thanks, and I am new to Python, too.