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....
try setting the column in excel to text explicitly or try the tool in Table tools
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
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