I am trying to use modelbuilder (ArcGIS 10.1) to add XY data to ArcGIS, then export the events as Feature Classes. The data comes in one xlsx document with a large number of sheets, each with an X and Y column. So in the end I would have a set of point feature classes.
So far I haven't been successful in automating this procedure with modelbuilder. My idea was to use the "Iterate Tables" tool and choose the xlsx document as input workspace; then continue with "Make XY Event Layer" and finally "FC to FC". In order to not overwrite previous results, I have added "%i%" to the output name. To be more precise, the model does run through the first sheet; the second run fails because the model tries to overwrite the first result (despite the %i% appendix). Using "%n%" instead, I get an infinite number of results all using the first sheet.
I suspect that either it is not possible to iterate through the sheets of an Excel table, or else maybe something is wrong with my naming system. Any help on this / workarounds much appreciated.
Best Answer
Not sure if iterating Excel sheets is still an issue for you....
...but I was able to use Iterate Tables to successfully make an XY event layer and export to a feature class for all sheets in the original .xlxs document. The tricky part for me was to remove the "$" from the sheet name because it was causing errors in the exporting (invalid character for feature class name). I used the model only tool Calculate Value to edit the %Name% string of the current sheet. Check the model labels for the expression.
I am sure there are ways to improve this model...but it does export a feature class for each excel sheet using the X and Y coordinate of the source table. Enjoy.
View full size model