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.

[ChrisSnyder]

Maybe a dumb question: Are you sure that the path to your xls file is correct? Strangely, you can set the workspace to a non-existant .xls file with no error, and it will yield of course, an empty table list

Make sure the .xls file path exists:

import os
os.path.exists(xlsFilePath)

It should retern True, otherwise there's you problem.

[Anonymous User]

Original User: Wayne_Whitley

Good point Chris... I didn't think of that.  That would certainly be a reason to call it time for a beer I'd say.

But see Dan's post #3 I think, that he's "...running ArcGIS 10.1 SP1, Office 2010, Windows 7 (64-bit). The Excel file in question is XLS (97-2003)."

Also, in his very 1st post he says he's doing the exact same thing in a model... so you'd think that the pathnames are reading okay.  Stranger things have happened, I guess, and can't leave anything to chance.

[DanBihari]

Hey Chris,

I'm running ArcGIS 10.1 SP1 and Office 2010.  I tried the Exist code that you provided and it did return true.

Wayne, you suggested downloading the Office 2007 system driver.  I downloaded it and installed it.  I found the instructions a bit confusing.  After the driver is installed is there any additional steps that I need to take?  The reason why I ask is it unfortunately did not correct my problem.

~Dan

[Anonymous User · Accepted solution]

Original User: Wayne_Whitley

I am at a loss for an answer - so you say you are able to open a worksheet from this file in ArcMap, or preview it in Catalog?  Check that if you haven't already, and if you will, use the same path in your script just to be certain you're 'looking at' the same xls.

To help isolate where the error is coming from, I would try saving a copy of the xls in newer format with your Office 2010 software, then see if you can access that by script, listing as you were doing before.

Other than that, it may be time for a support call.


EDIT:  Are you using the 64-bit background geoprocessing?- check this out:

10.1 sp 1 and 32/64 bit Python versions?
http://forums.arcgis.com/threads/70241-10.1-sp-1-and-32-64-bit-Python-versions?p=245700#post245700

Also, Kevin Hibma comments here:
http://blogs.esri.com/esri/arcgis/2012/10/31/announcing-64-bit-geoprocessing/

dpettittva says:
Just to clarify, I assume that doing any processing in a standalone Python script (assuming you???re using a 64-bit version of Python) still runs into the issue where the following data types are unsupported in 64-bit processing (just like they are w/in ArcMap), right?
???Personal geodatabase (.mdb)
???Excel tables (.xls, .xlsx)
???OLEDB connections

November 7, 2012 at 10:35 am
khibma says:
dpettittva, correct. These are limitations within the 64-bit processing framework (not limited to ArcMap)

[ChrisSnyder]

The 64-bit background theory sounds very plausible - I bet that one will be a common stumbling point for many of us going forward.

Dan, you are able to list non-xls tables via arcpy, right? Like tables in a FGDB?

If you don't have the 64-bit background gp thing installed... Hmm. I'm out oif ideas...

Except for plan Z:Complete uninstall/reinstall of ArcGIS and Office. Install Office 1st, then ArcGIS.

[Anonymous User]

Original User: DJB

Hey Wayne,

I think you might be onto something.  I just installed 64-bit background geoprocessing before Christmas.

I just need to figure out how to run my script in python 32.

Thank you for your help Wayne.  That was a great find!

~Dan

[DanBihari]

Hey Chris,

I am able to view and list tables in a FGDB.

>>> import arcpy
>>> arcpy.env.workspace = r"C:\GIS_Workspace\Python_Scripting\ScratchGDB.gdb"
>>> tables = arcpy.ListTables()
>>> tables
[u'Contact', u'Farm', u'FarmLocation', u'FarmRollNumber', u'MaterialType', u'MaterialTypeComponent', u'NAALocation', u'NAARollNumber', u'NASMApplicationArea', u'OperationName', u'Storage', u'StorageNASMLocation', u'StorageNASMRollNumber', u'Submission']
>>> 

~Dan

[Anonymous User]

Original User: csny490

I think Wayne nailed it. To gather further evidence, try listing the contents of a .mdb (which is also unsuported in 64-bit background gp like Wayne says).

The 'simple' fix then is to run your python script:
1. As a forground process via ArcMap (not as a background process)
2. Via a 32-bit Python IDE - not the 64-bit version.

I would recomend also, that if you don't need it, uninstall the 64-bit background gp.

Maybe a bit more complicated, is to run 32-bit python as a subprocess from your 'master' script (which can continute to run however you want).

[DanBihari]

Hey Chris,

You're last sentence, how would you run a sub-process as 32-bit python.  Using ESRI help (http://blogs.esri.com/esri/arcgis/2012/11/12/python-scripting-with-64-bit-processing/) and Kevin Hibma thread, I successfully ran my Python script.  It is definitely a 64-bit Python problem.

C:\GIS_Workspace\Python_Scripting>C:\Python27\ArcGIS10.1\python.exe NASM_Layer_Creator.py

C:\GIS_Workspace\Python_Scripting>

The reason I ask about sub-processes within a master script is it would be nice to have it run outside of Command Prompt. 

My ultimate end goal is to create multiple data layers, from Excel files, on a weekly basis using Scheduled Tasks.

Thanks Wayne and Chris for all your help.

Cheers!

[Anonymous User]

Original User: csny490

The basic command is:

import subprocess
subprocess.Popen([pythonExePath, childScriptPath, str(inputVar1), str(inputVar2)], shell=False, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)

You would then use the path to 32-bit Python for the pythonExePath variable.

An example of using subprocess to create a psudo-parallel process (32-bit) is here: http://forums.arcgis.com/threads/33602-Arcpy-Multiprocessor-issues?p=177418&viewfull=1#post177418

Read this too: http://forums.arcgis.com/threads/33602-Arcpy-Multiprocessor-issues?p=179790&viewfull=1#post179790