topic Re: TableToExcel - Loop in Python Questions

TableToExcel - Loop

DougHaller1 — Tue, 13 Jun 2017 20:24:55 GMT

Hi GeoNet and thanks in advance. I'm new to Python.

My goal is to write a script that converts all the shapefiles in a gdb into a csv (although I will take an Excel spreadsheet, too). I plan to convert the final program into a script tool with parameters for which gdb to access and where to save the spreadsheets. I can get a single shapefile to convert into an Excel spreadsheet. I would like to loop through the gdb and convert each shapefile to a unique csv (for this case, let's assume that the gdb contains only shapefiles).

My code fails and I have received a couple of different error messages depending on the variable I use for the in_table argument (hope I have that right).

I suspect that I am not correctly naming my in_table and out_xls variables.

Here's a screen shot of my code. Sorry for the small size, not sure how to enlarge the text.

Re: TableToExcel - Loop

BruceHarold — Tue, 13 Jun 2017 20:45:18 GMT

Doug

It looks like you are writing into a File Geodatabase directory, which isn't recommended.

It also looks like you want to push the data to OneDrive.

Data Interoperability extension can do this all in one hit with a Spatial ETL tool, and the tool is scriptable with Python like any geoprocessing tool. You do of course need the extension and there is a learning curve, but we're here to help. Web as a filesystem is a growing use case.

Re: TableToExcel - Loop

BlakeTerhune — Tue, 13 Jun 2017 20:48:21 GMT

Table to Excel needs a table as input. You're giving it a feature class. You'll probably need to start with copy rows to an in_memory workspace. That gives you just a geodatabase table of the feature class. Then you can try using that as the Input_Table parameter for Table to Excel. If that doesn't work, try make table view on the table first

Re: TableToExcel - Loop

IanMurray — Tue, 13 Jun 2017 20:53:22 GMT

Hi Doug,

A few things, first this should be a question not a discussion if you are able to fix it, if not its no big deal. Second if you want to post code in the future you can use the advanced editor to post code and select the language (See https://community.esri.com/people/curtvprice/blog/2014/09/25/posting-code-blocks-in-the-new-geonet?sr=search&searchId=a87e3927-580f-456c-be47-430463fb88b3&searchIndex=2‌ for examples on how to do so.).

Right now in line 26 you are using the variable fc and it isn't declared til the loop in line 30, which probably causing it to crash at the moment.

If you moved it to line 31 and put the current line 31 after it, it would likely work. I would run the program without the Table to Excel Conversion first(comment it out) while printing the variable fc and out_xls to make sure they are what you are expecting them to be, then run it with the conversion. Also if you are receiving errors its good to post what they are since they can help other posters identify potential problems with your code.

Also I would recommend using the os module for working with filepaths(see os.path.join), its cleaner than using string backslashes and concatenating filenames together. There are examples in the List Feature Class help as well as python help pages.

http://pro.arcgis.com/en/pro-app/arcpy/functions/listfeatureclasses.htm

https://docs.python.org/2/library/os.path.html

https://docs.python.org/2/library/os.html

Re: TableToExcel - Loop

BlakeTerhune — Tue, 13 Jun 2017 20:58:34 GMT

Also I would recommend using the os module for working with filepaths(see os.path.join), its cleaner than using string backslashes and concatenating filenames together.

I agree. Dan Patterson wrote an insightful blog post on the topic.

/blogs/dan_patterson/2016/08/14/filenames-and-file-paths-in-python

Re: TableToExcel - Loop

DanPatterson_Retired — Tue, 13 Jun 2017 23:10:29 GMT

For code formatting, it is best to past it in so it can be retrieved... Code Formatting the Basics ++ if people want to test

Re: TableToExcel - Loop

RebeccaStrauch__GISP — Wed, 14 Jun 2017 00:07:59 GMT

A few things, first this should be a question not a discussion if you are able to fix it,

done...I switched it to a question.

Re: TableToExcel - Loop

FC_Basson — Sun, 12 Dec 2021 07:54:13 GMT

You can try this code, taking into account that Excel files are limited to 65,535 (XLS) or 1,048,576 (XLSX) rows:

import arcpy
arcpy.env.workspace = r'D:\MyfolderOrGDB'
fcs = arcpy.ListFeatureClasses()
for fc in fcs: 
  rows = int(arcpy.GetCount_management(fc).getOutput(0))
  if rows < 65535:
    desc = arcpy.Describe(fc)
    xlsfile = '{}{}{}'.format('D:/OutputFolder/', desc.name, '.xls')
    arcpy.TableToExcel_conversion(fc,xlsfile)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: TableToExcel - Loop

FC_Basson — Wed, 14 Jun 2017 08:51:36 GMT

It works on Feature Classes too. The input table does not necessarily have to be a table object.

Re: TableToExcel - Loop

DougHaller1 — Wed, 14 Jun 2017 14:47:47 GMT

THANKS ALL. I will review each of the replies in detail and try each suggestion.

Doug

Re: TableToExcel - Loop

BlakeTerhune — Wed, 14 Jun 2017 14:50:51 GMT

Excellent, thanks for the info!

Re: TableToExcel - Loop

DougHaller1 — Wed, 14 Jun 2017 20:13:13 GMT

Thanks FC

I will try your code. It looks like I might need to make some changes for it to work on my data.

Line 2 - Do I need to change my workspace?
Line 5 - What is getOutput? Do I need to create a script tool from this code and add a parameter? If so, is getOutput a folder where I store the results?

Thanks again

Re: TableToExcel - Loop

DougHaller1 — Wed, 14 Jun 2017 20:28:03 GMT

Thanks Bruce.

I read about Spatial ETL. It looks like I may not have the extension. In my toolbox under Data Interoperability Tools I see quick import and export tools. When I select Customize - Extensions, Spatial ETL is not listed. I have access to about 8 other extensions on my current student subscription to 10.3 desktop

With respect to OneDrive - it is a pain. At the moment all my work is stored on OneDrive. I believe it is set as the default and I left it this way while a student to ensure backups of my work were saved when I was working on my personal computer. I think I could save all my data to my laptop hard drive rather than OneDrive. If I did this, would you recommend I back up everything to an external hard drive or to a service?

Thanks.

Re: TableToExcel - Loop

DougHaller1 — Wed, 14 Jun 2017 20:29:38 GMT

So, based on FC's response that TableToExcel works on feature classes, should I still implement your suggestions and copy rows? Thanks.

Re: TableToExcel - Loop

DougHaller1 — Wed, 14 Jun 2017 20:33:25 GMT

Thanks for the comments, Ian. I the future I will post to questions with questions.

As for the code edits (lines 26/31) I will try this.

I understand the advantage of os.path.join(). I will read the links you shared to learn more on how to use the OS module.

Re: TableToExcel - Loop

BlakeTerhune — Wed, 14 Jun 2017 21:53:40 GMT

I just tested and FC Basson‌ is correct, Table To Excel does work with a feature class. That means your code should work as-is. However, I think Ian Murray is correct about line 26 in your code so read through his comment carefully.

If you get stuck, try going back to square one and run the geoprocessing tool manually in ArcCatalog to make sure you're using the correct inputs. You can copy the python snippet from a completed process in the geoprocessing results as a starting point.

Re: TableToExcel - Loop

curtvprice — Thu, 15 Jun 2017 04:30:13 GMT

What is getOutput?

.getOutput is a method called on a result object. This is the most direct way to get the result value from GetCount (or any other tool that returns a result.) The result is a string, so that's why he wrapped it with int() -- to convert '45' to 45 for a count of 45 rows.

Re: TableToExcel - Loop

curtvprice — Thu, 15 Jun 2017 04:33:05 GMT

Excel files are limited to 65535 rows:

This is true for .xls format, but not .xlsx.

Understanding how to use Microsoft Excel files in ArcGIS—Help | ArcGIS Desktop

Re: TableToExcel - Loop

FC_Basson — Thu, 15 Jun 2017 05:45:53 GMT

Yes just change the workspace and output folder on your side.

Re: TableToExcel - Loop

DougHaller1 — Sun, 12 Dec 2021 07:54:16 GMT

Thanks FC.

Question:

This part of your code (below) looks like a parameter in a script tool. Do I need to create a script tool and designate this as a parameter? If so, I think it should be an output. What data type is it considered. Thanks again.

.getOutput(0))