Excel and Python:  Friends or Foes?

4121
19
Jump to solution
01-14-2013 09:32 AM
by Anonymous User
Not applicable
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.
0 Kudos
1 Solution

Accepted Solutions
by Anonymous User
Not applicable
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)

View solution in original post

0 Kudos
19 Replies
by Anonymous User
Not applicable
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?
0 Kudos
DanBihari
New Contributor III
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
0 Kudos
by Anonymous User
Not applicable
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
0 Kudos
ChrisSnyder
Regular Contributor III
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.
0 Kudos
by Anonymous User
Not applicable
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
0 Kudos
T__WayneWhitley
Frequent Contributor
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?
0 Kudos
by Anonymous User
Not applicable
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
0 Kudos
T__WayneWhitley
Frequent Contributor
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...
0 Kudos
by Anonymous User
Not applicable
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?
0 Kudos