7

I am trying to use ModelBuilder(ArcMap 10.1) to add XY data to ArcMap, 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.

Improve this question
5
  • 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
    Commented Oct 24, 2013 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
    Commented Oct 24, 2013 at 16:00
  • Python might be better for this case, as it will handle it better.
    – Cindy Jayakumar
    Commented Oct 25, 2013 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 ♦
    Commented Oct 28, 2013 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
    Commented Oct 28, 2013 at 13:53

2 Answers 2

Reset to default
2

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

Improve this answer
1
  • I added the "Calculate Value" tool to my model and it works fine. Thank you for your suggestion!
    – maffa
    Commented Apr 7, 2014 at 9:20
1

enter image description hereHi Guys, I faced the same issue and I solved pretty much in the same way (hours lost finding out how to get rid of the $ as well!!). I used a wild card (Iterate Table > Make Var > from Par > Wildcard) to selct correct Excel sheets within the table. Sometimes it can be useful. Cheers, Marco

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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