I have a python script that uses the ExcelToTable_management() method. One of the fields in excel worksheet is zipcode, and in the past, at least one of the values is in the form of zip + 4 (84124-1234) so the new table zipcode field gets cast as text.
However, I just got a new excel file for the month of August, and none of the data in the original zipcode field is zip + 4, so ExcelToTable_management() converts them to a long int. That's okay but my entire script is dependent on the field being text. When I look at the spreadsheet itself, the zipcode column is in 'General' format.
I kind of doubt there is a way to convince ExcelToTable_management() that when it reads that zipcode column to treat it as text regardless of what it reads, but I thought I'd ask none-the-less....
Joe...
try setting the column in excel to text explicitly or try the tool in Table tools
/blogs/dan_patterson/2018/02/12/table-tools-a-set-of-tools-for-working-with-tabular-data
http://www.arcgis.com/home/item.html?id=90d9ca933e8c4b96bf341a20ae1f2514
you can check the toolbox script (excel2tbl.py) against yours.
Data type in excel is based on the first 8 rows of the Excel file. There are a few tricks you can use to slightly modify this behaviour. A source describing some of them: Excel in SSIS: fixing the wrong data types - Kohera
A trick that I use is to put an apostrophe in Excel's first row cell containing the zip code that you want to be interpreted as text, ie. '12345.
That's basically what I did: in the first row of the excel worksheet I changed the value fro 84006 to 84006-1234 and put it to bed. The problem with this whole mess is someday I'd like the script to run as scheduled task, so any manual intervention is not an option; the spreadsheet is an output of a report from a database, so I've asked the responsible party on that end to keep things as the were. (If I could get access straight to the database, this would be a non-issue, but it's got HIPAA protected data in it, and I'm considered a security breach....)
My script checks the data type for all records, column by column... any crud in a column can rapidly turn a seemingly numeric column into text no guessing from a sample of rows
Dan- after downloading your table package, I couldn't find excel2tbl.py. I didn't see it at the git site either. Did I mess something?
Got the script now. taking a look at it : Read the excel into a np array... I'd need to specify the dt to text for the zipcode field, correct?
just try the toolbox instead of running the script until you get a feel for inputs. it throws in the text incarnation of 'None'. Integer fields, you have an option