One Table To Multiple Excel Sheets

[FlightDeck]

Hey all,

Since the Table to Excel Function has no Batch option, Is there an easy way to export a single table to multiple excel sheets using an attribute as the demarcation? Or do I need to do this manually with select by attributes? Would prefer not making a bunch of separate tables if its not necessary as well. 

Thanks. 

[VenkataKondepati]

Hi @FlightDeck,

If you are familiar with the python, you can write a small script.

Table To Excel (Conversion)—ArcGIS Pro | Documentation

SearchCursor—ArcGIS Pro | Documentation

Please let me know if you need a sample.

[ZenMasterZeke]

I think the Table to Excel script was originally written by a regular user and later incorporated as a tool by ESRI. Might be wrong about that, was a handful or two of years ago. Anyway, this suggests rolling your own script as suggested by @VenkataKondepati .

[MErikReedAugusta]

In theory, you could also solve this problem on the Excel side, depending on how complex the operation is and how many different sheets you plan to end with.

If it's a one-time project with no more than a half-dozen sheets, I'd probably export the entire table as a single Excel file, and then go manually create the additional sheets, and populate them dynamically using the FILTER, XLOOKUP, and similar functions inside Excel.

If it's something I'm going to be doing fairly regularly and/or with more than a half-dozen sheets, I'd automate it like Venkat suggested:

1. Call Select By Attributes against the dividing Attribute
2. Call Table to Excel and save it as a temporary standalone excel file.
3. Loop through and repeat Steps 1 & 2 for all possible attributes in the demarcation field.
4. Use one of the available Excel libraries for Python to stitch all the files from Step 2 into separate sheets in the same file (like openpyxl or xlwt—both of which are included in the ArcPro environment ships with).
5. For sanity & cleanliness, I'd probably automate deletion of all those standalone files from Step 2, at the very end.

[DanPatterson]

Split By Attributes (Analysis)—ArcGIS Pro | Documentation

works for a featureclass or table, assuming your attribute you want to split on are based on unique combinations within the attribute field or fields