Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
Not sure if it is an issue any longer, but previous versions of ArcGIS could make funny things out of .xlsx files. Might help saving it as a .xls instead. –  Martin Oct 24 '13 at 13:37
    
Further to Martins idea you could import your worksheets into an Access database and that becomes the workspace for your iterate tables? –  Hornbydd Oct 24 '13 at 16:00
    
Python might be better for this case, as it will handle it better. –  Arabella Oct 25 '13 at 5:46
    
I would rather be trying to do this at 10.2 with Excel Toolset than at 10.1 - is that an option for you? –  PolyGeo Oct 28 '13 at 11:14
    
Thanks for sharing your ideas. Saving the file as .xls did not change anything; I have not tried the Access approach yet. We will probably move to 10.2 soon, so maybe I'll have better luck there. Python will probably do this easily, however I was somehow hoping this might be a good example to demonstrate how iterations work in modelbuilder... –  maffa Oct 28 '13 at 13:53
add comment

1 Answer 1

up vote 1 down vote accepted

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.

Iterate Excel Sheet model

View full size model

share|improve this answer
    
I added the "Calculate Value" tool to my model and it works fine. Thank you for your suggestion! –  maffa Apr 7 at 9:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.