ExceltoTable_conversion error

JamesGibson · ‎01-22-2016

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!

DarrenWiens2 · ‎01-22-2016

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.

View solution in original post

DarrenWiens2 · ‎01-22-2016

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

XanderBakker · ‎01-22-2016

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

JamesGibson · ‎01-22-2016

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.

XanderBakker · ‎01-22-2016

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.

JamesGibson · ‎01-22-2016

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?

JamesGibson · ‎01-22-2016

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)

curtvprice · ‎01-22-2016

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.

DarrenWiens2 · ‎01-22-2016

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.

JamesGibson · ‎01-22-2016

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.