One Table To Multiple Excel Sheets

Question

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.

DanPatterson · Answer

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

MErikReedAugusta · Answer

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:

* Call Select By Attributes against the dividing Attribute
* Call Table to Excel and save it as a temporary standalone excel file.
* Loop through and repeat Steps 1 & 2 for all possible attributes in the demarcation field.
* 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).
* For sanity & cleanliness, I'd probably automate deletion of all those standalone files from Step 2, at the very end.

ZenMasterZeke · Answer

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 .