Iterate Tables in Model Builder

[Anonymous User]

Original User: jofuchs

Hi community,

I want to iterate through a folder with several Excel-files. Every Excel-File consits of one sheet. But when I chose the folder directory of all Excel-files as workspace for the iteration, the iterator doesn´t work because only the Excel-File is selected, but not the sheet of the Excel-file and the model stops immediately. What I can do is to join all the single Excel-files into one file with several sheets. Then I can choose this generated Excel-File as workspace and the following processing steps are applied correctly.

Is there another way to process every seperated Excel-file standalone so I can avoid the manuel data preparation?

Thanks in advance!

Jo

[T__WayneWhitley]

Yes.  The problem is ArcGIS 'sees' the Excel file itself as a workspace.  So what you can do is use ListFiles on the directory, filtering for Excel files - this produces a list which you can feed into Iterate Multivalues.  In turn, you can use ListTables to get at and process each table.  Although you said you only have 1 table in each workbook, this creates another list -- so since using modelbuilder, you'd use Iterate Multivalues again.

I'd rather do something like this in Python, but you could still manage this in ModelBuilder - I think you're limited to 1 iterator per model, so you'd need a 'submodel' to enter the 2nd Iterate Multivalues, accepting the intermediate ListFiles output list to iterate over the ListTables output list.

Make sense?

[Anonymous User]

Original User: jofuchs

Thank you very much Wayne!

That sounds good to me. I will test it on monday (then I will have access to ArcGIS) and give you feedback.

I'm a really beginner in Python. I know how to iterate over a directory and select files with a specified suffix. But how can I manage this thing with the ´file in the excel-file´in Python?

Have a nice weekend,
Jo

[T__WayneWhitley]

If you decide to do this via Python, once you list the Excel files you can then loop over them like workspaces and use ListTables.
Later today (if enough time) I'll test and attach both means, ModelBuilder and Python....something simple like copying the tables to gdb.

Wayne

[T__WayneWhitley · Accepted solution]

I was looking at this problem again and I don't know if you're using 10.2 but there are new Excel gp tools...and I found an interesting Python function here in the webhelp that uses the ExcelToTable tool:

Excel To Table (Conversion)
Desktop » Geoprocessing » Tool reference » Conversion toolbox
http://resources.arcgis.com/en/help/main/10.2/index.html#//001200000055000000

So what I did was modify it to work on an input directory - this would probably suit you best (rather than a model):

import os import xlrd import arcpy  """     Modified function ExcelToTable example 2 (stand-alone script) from:     http://resources.arcgis.com/en/help/main/10.2/index.html#//001200000055000000 """  def importallsheets(in_dir, out_gdb):     arcpy.env.workspace = in_dir     xlsxfiles = arcpy.ListFiles('*.xls*')     for xlsx in xlsxfiles:         workbook = xlrd.open_workbook(xlsx)         sheets = [sheet.name for sheet in workbook.sheets()]          print('{} sheets found: {}'.format(len(sheets), ','.join(sheets)))                  for sheet in sheets:             out_table = os.path.join(out_gdb,                                      arcpy.CreateUniqueName(                                          arcpy.ValidateTableName(                                              "{0}_{1}".format(os.path.basename(xlsx), sheet),                                              out_gdb), out_gdb))              print('Converting {} to {}'.format(sheet, out_table))              # Perform the conversion             arcpy.ExcelToTable_conversion(xlsx, out_table, sheet)  if __name__ == '__main__':     # usage:  importallsheets( input_directory, output_geodatabase )     importallsheets(r'C:\Users\whitley-wayne\Desktop',                     r'C:\Users\whitley-wayne\Desktop\New File Geodatabase.gdb')

As-is, you'll need to modify 2 parameters at the bottom of the script above to reflect your input directory and output geodatabase. (See where I tested it on my input directory: C:\Users\whitley-wayne\Desktop ...and my output gdb: C:\Users\whitley-wayne\Desktop\New File Geodatabase.gdb)

This can be easily adapted for use as a script tool...just thought this would get you started.



Wayne

[Anonymous User]

Original User: jofuchs

Hi Wayne,

thank you very much once again, especially for your script! So far it seems that both approaches work. I will need some more time for testing, but I think I can close this thread. In case of occuring unexpected problems, I'll post my problems.

Kind regards,
Jo

[Anonymous User]

Original User: jofuchs

Hi wayne,

to do this task in 10.2 is very convinient. But how can I do this in 10.1 without the Excel Conversion gp tools. What I did so far is to List all .xlsx files (ListFiles), but I am not able to do the next step entering the Excel sheet and work with the workbook inside... Then I tried ListTables but that doesn#t work (result was an empty list)

I did something like this:

xlsxfiles = arcpy.ListFiles('*.xls*')
for xlsx in xlsxfiles:
    result = arcpy.ListTables()
    print xlsx, result

Kind regards,
Johnny

[T__WayneWhitley]

I thought this was interesting (see link below), since the orig post concerned how to do this with ModelBuilder -

http://gis.stackexchange.com/questions/75365/arcgis-modelbuilder-iterate-through-excel-sheets


As for your code, looks like you need to remember to set the workspace to the Excel file since, remember, ArcGIS will 'see' that as what is housing the tables, something like this:

from arcpy import env
env.workspace = r'whatever root directory file path you're listing files from'

xlsxfiles = arcpy.ListFiles('*.xls*')
for xlsx in xlsxfiles:
    env.workspace = xlsx
    result = arcpy.ListTables()
    print xlsx, result

Something like that... good luck!

Wayne


PS- And before you go re-inventing the wheel, I forgot about this toolbox you can download (link below), written by TeamPython for ArcGIS 10.0 and up (Basic [Arcview] and beyond) ...sorry didn't mention this before but I didn't know what version you had.

Excel and CSV Conversion Tools
http://www.arcgis.com/home/item.html?id=f3d91b8f852042e289e09a7ec8342431

[Anonymous User]

Original User: jofuchs

Hi Wayne,

thats it! I can use this toolbox to implement my processing with ArcGIS 10.1! Unfortunately the Python code doesn't work. But with your help I could manage my problems by using the Excel conversion tools.

Thank you very much!
Johnny

[Kevin_MacLeod]

11 years later, still an issue. I just expected Iterate Files to work. It doesn't. There's all kinds of workarounds but Esri should just make this work out of the box. I'm only doing this large model today anyway because Transfer Domain Descriptions has been a bug for 15 (20?) years.  Yes, that setting in Environments, which doesn't work in ArcMap or Pro 3.5.  Only GP tool without the bug is FC to Excel.  So I'm iterating to join Excel to a blank SHP by GUID.  (python doesn't work either. the sample straight from the docs doesn't work, on a simple file gdb)

The Excel to Table works. Iterate Files to do the same thing, doesn't. I get the worksheet thing. But make some GUI to deal with worksheets. Excel is a common enough thing this warrants fixing at some point.