Excel and Python: Friends or Foes?

[Anonymous User]

Original User: DJB

Hello Everyone,

What I am attempting to do is not brain surgery but merely take a single Excel file and import all the worksheets into a GDB.  Pretty easy right???WRONG!

For some reason I cannot do anything with Excel files in Python.  For example???

>>> arcpy.env.workspace = r"C:\GIS_Workspace\Projects\Nutrinet_Management_Mapping\NASM_Update\NASMApprovedReport.xls" >>> Tables = arcpy.ListTables() >>> print Tables [] >>>

or..

>>> arcpy.env.workspace = r"C:\GIS_Workspace\Projects\Nutrinet_Management_Mapping\NASM_Update\NASMApprovedReport.xls" >>> Tables = arcpy.ListTables() >>> for tab in Tables:              print (tab)    >>> 

What I would like to do is...

arcpy.env.workspace = r"C:\GIS_Workspace\Projects\Nutrinet_Management_Mapping\NASM_Update\NASMApprovedReport.xls" Tables = arcpy.ListTables() for tab in Tables:         arcpy.TableToTable_conversion(tab, r"C:\GIS_Workspace\Python_Scripting\ScratchGDB.gdb", tab)

The Excel spreadsheet itself is clean and tight.  I have no spaces or wild/special characters in any of the field headings and the same goes for the worksheet names.  I also created a model to do the very same tasks and it recognizes the excel tables and imports the tables into a GDB successfully.

[ATTACH=CONFIG]20708[/ATTACH]

Any help or advice would be greatly appreciated.

Thanks.

[Anonymous User]

Original User: Wayne_Whitley

Interesting, and you are having problems with all input spreadsheets?
Any way you can attach a copy of the xls? ...a sample is all that is necessary.
Also, what version of ArcGIS are you using?...Excel too, what version of Office?

[DanBihari]

Hey Wayne,

I've attached an empty spreadsheet that contains the exact same field headings and worksheet names.  Unfortunately I couldn't include the data.

I'm currently running ArcGIS 10.1 SP1, Office 2010, Windows 7 (64-bit).  The Excel file in question is XLS (97-2003).

~Dan

[Anonymous User]

Original User: bosewicht

Well, when you do a print (tab) you get:
Contact$
Farm$
FarmLocation$
OperationName$
Submission$

So you will probably need to add:
tab = tab[:-1]

before your table to table to drop the $ from your field names

[ChrisSnyder]

I can access .xls files like this in v10.1:

>>> import arcpy
>>> path = r"\\snarf\am\div_lm\ds\gis\projects\workstations\benchmarks\chris\result_tables\merge_20120904.xls"
>>> arcpy.env.workspace = path
>>> tableList = arcpy.ListTables()
>>> tableList
[u'PivotTable$', u'RawData$']
>>>

Since the .xls file is treated like a workspace container the script would look something like:
import arcpy
xlsPath = r"\\snarf\am\div_lm\ds\gis\projects\workstations\benchmarks\chris\result_tables\merge_20120904.xls"
fgdbPath = r"C:\temp\test.gdb"
arcpy.env.workspace = xlsPath
tableList = arcpy.ListTables()
for table in tableList:
    inTbl = os.path.join(xlsPath,table), 
    outTbl = os.path.join(fgdbPathm, table[0:-1]) #get rid of the traling '$' symbol
    arcpy.CopyRows(inTbl, outTbl)

If you want to get fancier, you could use the win32com module to fetch values from the .xls file (or the .xlsx file!) ... Then you could have it all.

[Anonymous User]

Original User: DJB

Hey Wayne,

I thought that may be a problem so I had similar code to parse out the "$",

i.e. tab = tab.split("$")[0]

Where my frustration comes in is I still do not see anything where as you are seeing the list of tables.

Could it be that I'm missing an excel python library.  As you can see below, no results are returned.  It's still essentially an empty list.

>>> import arcpy
>>> arcpy.env.workspace = r"C:\GIS_Workspace\Projects\Nutrinet_Management_Mapping\NASM_Update\NASMApprovedReport.xls"
>>> Tables = arcpy.ListTables()
>>> for tab in Tables:
 tab = tab[:-1]
 print tab

 
>>> 

~Dan

[T__WayneWhitley]

Yes, I was able to read everything okay .... but your problem seems to be encountered before that.
Just a shot, but do you have the 2007 office system drivers?

[Anonymous User]

Original User: DJB

Hey Chris,

Thank you for your response.  As the previous post, I'm still getting an empty list.

>>> import arcpy
>>> path = r"C:\GIS_Workspace\Projects\Nutrinet_Management_Mapping\NASM_Update\NASMApprovedReport.xls"
>>> arcpy.env.workspace = path
>>> tableList = arcpy.ListTables()
>>> tableList
[]
>>> 

~Dan

[T__WayneWhitley]

Hmmm, I was able to list them just fine, but I'm at ver 10.0 and have Office 2007, meaning by default I have the 2007 system drivers.
Just a shot in the dark, but that seems to have been a problem at the upgrade of Office not to include the drivers ArcGIS 10.0 needed....not sure if those are included with 10.1 but those drivers are also provided separately, see this:

http://www.microsoft.com/en-us/download/details.aspx?id=23734


"If you have Microsoft Excel 2010 or no version of Microsoft Excel installed, you must install the 2007 driver before you can use either .xls or .xlsx files."
http://resources.arcgis.com/en/help/main/10.1/index.html#//005s0000001w000000

I know you said you could access the xls tables in a model, so that leaves doubt this is the problem, but I do not know if the same drivers are used in that environment...

[Anonymous User]

Original User: csny490

Dan,

When I use your .xls file, I get a table list of [u'Contact$', u'Farm$', u'FarmLocation$', u'OperationName$', u'Submission$']

What version of ArcGIS are you running?
What version of MS Office?