topic Excel and Python: Friends or Foes? in Transportation Questions

Excel and Python: Friends or Foes?

Anonymous User — Mon, 14 Jan 2013 17:32:03 GMT

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.

Re: Excel and Python: Friends or Foes?

Anonymous User — Mon, 14 Jan 2013 17:46:35 GMT

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?

Re: Excel and Python: Friends or Foes?

DanBihari — Mon, 14 Jan 2013 18:13:58 GMT

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

Re: Excel and Python: Friends or Foes?

Anonymous User — Mon, 14 Jan 2013 18:52:03 GMT

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

Re: Excel and Python: Friends or Foes?

ChrisSnyder — Sat, 11 Dec 2021 20:15:52 GMT

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.

Re: Excel and Python: Friends or Foes?

Anonymous User — Sat, 11 Dec 2021 20:15:55 GMT

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

Re: Excel and Python: Friends or Foes?

T__WayneWhitley — Mon, 14 Jan 2013 19:11:45 GMT

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?

Re: Excel and Python: Friends or Foes?

Anonymous User — Sat, 11 Dec 2021 20:15:58 GMT

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

Re: Excel and Python: Friends or Foes?

T__WayneWhitley — Mon, 14 Jan 2013 19:13:50 GMT

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...

Re: Excel and Python: Friends or Foes?

Anonymous User — Mon, 14 Jan 2013 20:18:03 GMT

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?

Re: Excel and Python: Friends or Foes?

ChrisSnyder — Sat, 11 Dec 2021 20:16:01 GMT

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.

Re: Excel and Python: Friends or Foes?

Anonymous User — Mon, 14 Jan 2013 20:56:32 GMT

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.

Re: Excel and Python: Friends or Foes?

DanBihari — Tue, 15 Jan 2013 11:57:03 GMT

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

Re: Excel and Python: Friends or Foes?

Anonymous User — Tue, 15 Jan 2013 12:28:56 GMT

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)

Re: Excel and Python: Friends or Foes?

ChrisSnyder — Tue, 15 Jan 2013 15:02:50 GMT

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.

Re: Excel and Python: Friends or Foes?

Anonymous User — Tue, 15 Jan 2013 15:03:24 GMT

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

Re: Excel and Python: Friends or Foes?

DanBihari — Sat, 11 Dec 2021 20:16:04 GMT

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

Re: Excel and Python: Friends or Foes?

Anonymous User — Tue, 15 Jan 2013 15:15:31 GMT

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

Re: Excel and Python: Friends or Foes?

DanBihari — Sat, 11 Dec 2021 20:16:07 GMT

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!

Re: Excel and Python: Friends or Foes?

Anonymous User — Sat, 11 Dec 2021 20:16:09 GMT

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