TableToExcel - Loop

1947
28
06-13-2017 01:24 PM
DougHaller1
New Contributor III

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.

tabletoexcel loop

Tags (2)
0 Kudos
28 Replies
BruceHarold
Esri Regular Contributor

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.

DougHaller1
New Contributor III

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.

0 Kudos
BlakeTerhune
MVP Regular Contributor

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

FC_Basson
MVP Regular Contributor

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

BlakeTerhune
MVP Regular Contributor

Excellent, thanks for the info!

0 Kudos
DougHaller1
New Contributor III

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

0 Kudos
BlakeTerhune
MVP Regular Contributor

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.

0 Kudos
RhettZufelt
MVP Regular Contributor

Not sure if you are running into this, but if converting to an in_memory dataset, keep this bug in mind:

Thank you for contacting Esri and providing additional information to escalate "BUG-000093491: The field name of the in-memory table created using the Excel To Table tool gets truncated if the field name exceeds ten characters when listing it using Python. The field name is not truncated when opening the in-memory table in ArcMap."

R_

IanMurray
Frequent Contributor

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?s...‌ 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