topic Re: ExceltoTable_conversion error in Python Questions

ExceltoTable_conversion error

JamesGibson — Fri, 22 Jan 2016 18:02:09 GMT

I am having a problem with a simple line of python code here -- and I don't know why this is happening. There was a point in time where I did not receive this error, so I know it should work.

What I am trying to do:

Every month I get a Tax Data CD from the Tax Office, and I am trying to streamline the process of combining the ownership XLS to the Parcel Shapefile.

I have a program I've written in VB which will allow me to select all the files necessary for the join and run the python script. (this stores the input files to a variable in an .ini file, which the python script reads the variables with ConfigParser()

When I run the script, I get this error message:

WARNING *** OLE2 inconsistency: SSCS size is 0 but SSAT size is non-zero

Traceback (most recent call last):

File "C:\Users\anotgrass\Desktop\test.py", line 8, in <module>

arcpy.ExcelToTable_conversion (XLSInput, tableOutput)

File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\conversion.py", line 44, in ExcelToTable

raise e

ExecuteError:

Traceback (most recent call last):

File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\ExcelToTable.py", line 254, in <module>

arcpy.GetParameterAsText(2))

File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\ExcelToTable.py", line 244, in excel_to_table

row = field.validate_value(row, workbook.datemode)

File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\ExcelToTable.py", line 100, in validate_value

value = xlrd.xldate_as_tuple(value, datemode)

File "C:\Python27\ArcGISx6410.3\lib\site-packages\xlrd\xldate.py", line 80, in xldate_as_tuple

raise XLDateAmbiguous(xldate)

XLDateAmbiguous: 1.0

Failed to execute (ExcelToTable).

I've narrowed the error down to the ExcelToTable conversion, and now have a test script solely for testing this out.

Basically, I have an XLS file that needs to be converted to a temp.dbf file, which later in the script lets me join the table to the Shapefile and create a new shapefile.

When I run this simple script, it creates the temp.DBF file, but it doesn't finish creating it, and then I get the error message.

Does anyone know what is going on?

Thanks!

Re: ExceltoTable_conversion error

DarrenWiens2 — Fri, 22 Jan 2016 18:10:53 GMT

Can you open the xls in Excel? Verify the file is not corrupt.

Re: ExceltoTable_conversion error

XanderBakker — Fri, 22 Jan 2016 18:27:20 GMT

... additionally to the comment by Daren, if you execute the Table to Excel tool with the same Excel file, does that yield the same error of does it work?

Is the Excel in a valid format to be converted to a "database" (especially check fieldnames and different types of values in the same field)?

Re: ExceltoTable_conversion error

JamesGibson — Fri, 22 Jan 2016 19:26:11 GMT

Darren Wiens

Yes, I can open the excel file if I double click the file manually, and also from the full path of "E:\\City of Belton Projects Folders\\Maps and Data\\Tax Appraisal Office\\2016.01.15\\01152016 BellCad_Ownership_Data\\SP_OtherISDs_01152016.xls" minus the additional backslashes.

Xander Bakker

If I change the script from ExceltoTable to TabletoExcel, I get

AttributeError: 'module' object has no attribute 'TabletoExcel_conversion'

But that shouldn't work, since it is trying to convert the excel file, to an excel file, correct?

The excel file is in the correct format. If I import the excel in arcmap, I can join it to the Shapefile with no problems. It just doesn't seem to work the Python way.

Re: ExceltoTable_conversion error

XanderBakker — Fri, 22 Jan 2016 20:05:02 GMT

Oops. my bad. I meant to write Excel to table conversion. The idea is performing this step in ArcMap manually with the Excel you have in order to see if this step produces the same error. If so, it is either a bug in the tool, but more often caused by errors in the input data.

Re: ExceltoTable_conversion error

JamesGibson — Fri, 22 Jan 2016 20:29:41 GMT

Oh ok, I see what you mean.

I tried using the Excel to Table within ArcMap and I still get an error. This time it shows that the maximum record length has been exceeded.

Running script ExcelToTable...

Failed script ExcelToTable...

Traceback (most recent call last):

File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\ExcelToTable.py", line 254, in <module>

arcpy.GetParameterAsText(2))

File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\ExcelToTable.py", line 225, in excel_to_table

template=tmp_table)

File "c:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\management.py", line 15242, in CreateTable

raise e

ExecuteError: ERROR 999999: Error executing function.

The maximum record length has been exceeded.

Failed to execute (CreateTable).

Failed to execute (ExcelToTable).

I just did a little bit of research, the excel file is a 97-2003 version. But the total number of rows is 59399 with 31 columns, which is under the 65,536 rows by 256 columns limit.

So it should still work, yes?

Re: ExceltoTable_conversion error

JamesGibson — Fri, 22 Jan 2016 20:39:11 GMT

I also just tried to save the file as a 2010 xlsx and run the table to excel tool, while leaving the default output location to the default.gdb and this is the error I get.

Failed script ExcelToTable...

Traceback (most recent call last):

File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\ExcelToTable.py", line 254, in <module>

arcpy.GetParameterAsText(2))

File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\ExcelToTable.py", line 244, in excel_to_table

row = field.validate_value(row, workbook.datemode)

File "c:\program files (x86)\arcgis\desktop10.3\ArcToolbox\Scripts\ExcelToTable.py", line 100, in validate_value

value = xlrd.xldate_as_tuple(value, datemode)

File "C:\Python27\ArcGISx6410.3\lib\site-packages\xlrd\xldate.py", line 80, in xldate_as_tuple

raise XLDateAmbiguous(xldate)

XLDateAmbiguous: 1.0

Failed to execute (ExcelToTable).

Failed at Fri Jan 22 14:34:55 2016 (Elapsed Time: 19.67 seconds)

Re: ExceltoTable_conversion error

curtvprice — Fri, 22 Jan 2016 20:51:56 GMT

A few thoughts

1. That's a really long and complicated path. Have you tried moving the file to C:\workspace\test.xls and seeing if that works?

2. DBF format is notoriously weak. There are many limitations that are easy to run into, especially if you are reading spreadsheets that humans type things into. The maximum record length exceeded error may be that you're hitting the max number of bytes in a .dbf row, which is really small. If you can copy to a temp file geodatabase and maybe massage your data from there (ie truncate long text, etc to fit in dbf) you may have better luck.

3. If you post a sample xls file that breaks the tool (after you replace all the PII with garbage of course!) that may help solve the problem.

Re: ExceltoTable_conversion error

DarrenWiens2 — Fri, 22 Jan 2016 21:09:21 GMT

Your error message seems to revolve around something to do with dates, specifically something to do with 1900 vs. 1904 date mode. Can you compare the datemode used in an old, working Excel workbook against a new, nonworking Excel workbook? It should be in Excel Options -> Advanced -> checkbox "Use 1904 date system"

More on datemode: The xlrd Module and https://support.microsoft.com/en-us/kb/214330

edit: I can't seem to break the tool using either 1900 or 1904 datemodes, so that may not be the issue.

Re: ExceltoTable_conversion error

JamesGibson — Fri, 22 Jan 2016 21:57:40 GMT

I was not able to find an excel file that worked at one point, but I checked the date mode and the box is unchecked (using 1900 I suppose)

I tried checking the use 1904 date mode and reran the tool, and it worked just fine.

Does this not work by using the 1900 date mode?

edit: That's odd, it only seems to work using 1904, when I change it back to 1900, I continue to receive the same errors.

Re: ExceltoTable_conversion error

JamesGibson — Fri, 22 Jan 2016 22:23:29 GMT

Here is a stripped down version of the excel file with personal info removed, which still gives me the same error messages. https://www.dropbox.com/s/ejcywo7vyqi9mdg/test.xls?dl=1

If anyone wanted to test using the actual file.

Re: ExceltoTable_conversion error

XanderBakker — Fri, 22 Jan 2016 22:49:15 GMT

It is definitely the date 1-1-1900 as mentioned here:

http://stackoverflow.com/questions/8231109/how-to-format-date-in-python-and-avoid-xldateambiguous-and-fix-leap-year

The docs are very clear about when this is raised.
It will happen if the workbook.datemode == 0 and 1.0 <= value < 61.0

If you change the only record that has this date to a value higher than or equal to 61 (first of March 1900 for instance), the tool will run without a problem.

It is a limitation of the xlrd package used for this geoprocessing tool.

If you add the xls to the TOC and manually export the table to DBF, then it will work too, since this doesn't use Python code, but ArcObjects (not that it helps you though).

Re: ExceltoTable_conversion error

JamesGibson — Fri, 22 Jan 2016 23:01:39 GMT

Thanks Xander, I will have to come back on Monday to do some more verifying as I will not have access to my computer this weekend.

I appreciate everyone's help!