Iterating, spatial joining and exporting to Excel- Need help

608
2
11-21-2017 09:27 AM
TheodoreF
Occasional Contributor II

Hello,

My task is:

1. Spatial Join multiple features to a Site_Boundary polygon feature

2. Export the output file to a single Excel document containing the target layer and all joining layers.

So far here is my model:

BGS.gdb is where the joining features are located (polygons and lines).

I spatially join each feature to my target feature (Site_Boundary_Theo) using Iterate Feature Classes.

The output is given the name Spatial_join_%name%.

This output is exported to an Excel file.

The spatial join creates multiple Site_Boundary feature classes (with the name of the joining feature appended to the file name):

The output Excel file only contains data from one of these feature classes:

In this case, the Excel file is the Site Boundary attribute table with the BGS_Linear_50K attribtues joined.

My question is, how can I create a single Excel file showing the target layer with ALL the joining layers?

Thanks,

Theo

Tags (2)
0 Kudos
2 Replies
JamesCrandall
MVP Frequent Contributor

Looks like pandas allows to export individual "Sheets" into a single .xlsx but you will have to first convert your feature class to a NumPy Array http://desktop.arcgis.com/en/arcmap/10.3/analyze/arcpy-data-access/featureclasstonumpyarray.htm

import pandas
 
# Create a pandas dataframe
df = pandas.DataFrame(nparr,columns=['col1','col2'])
 
# Create pandas ExcelWriter and point to a .xlsx file
writer = pandas.ExcelWriter(r'H:\pandas_simple.xlsx')
 
# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='MySheetName')
 
# Close the Pandas Excel writer and output the Excel file.
writer.save()
DarrenWiens2
MVP Honored Contributor

You only get one Excel file with the results from the last iteration because the whole model runs on each iteration, overwriting the final Excel file each time. You could get multiple Excel files if you varied the file name like you did with the Spatial Join output. Or, you could get a single output in the end by using Collect Values and merging the results before exporting to Excel. It probably won't be the exact result you're looking for, but that's the trade-off using model builder rather than Python...