AnsweredAssumed Answered

Iterate Tables in Model Builder

Question asked by geonetadmin on Mar 15, 2014
Latest reply on Mar 16, 2014 by Wayne_Whitley

Like • Show 0 Likes0
Comment • 8

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

Wayne_Whitley Mar 16, 2014 5:14 PM

Correct Answer

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

See the reply in context

Attachments

No one else had this question

Outcomes

Wayne_Whitley Mar 15, 2014 6:06 AM

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?

Actions

geonetadmin Mar 16, 2014 2:02 AM

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

Actions

Wayne_Whitley Mar 16, 2014 5:30 AM

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

Actions

Wayne_Whitley Mar 16, 2014 5:14 PM

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')

Actions

geonetadmin Mar 18, 2014 1:41 AM

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

Actions

geonetadmin Apr 7, 2014 7:44 AM

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

Actions

Wayne_Whitley Apr 7, 2014 10:21 AM

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

Actions

geonetadmin Apr 9, 2014 12:07 AM

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

Actions

8 Replies

Wayne_Whitley Mar 15, 2014 6:06 AM
Mark Correct
Correct Answer
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?
Like • Show 0 Likes0
Actions
geonetadmin Mar 16, 2014 2:02 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
Wayne_Whitley Mar 16, 2014 5:30 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions

geonetadmin Mar 18, 2014 1:41 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
geonetadmin Apr 7, 2014 7:44 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
Wayne_Whitley Apr 7, 2014 10:21 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
geonetadmin Apr 9, 2014 12:07 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

Iterate Tables in Model Builder

Attachments

Outcomes

Related Content

Recommended Content