Specify a field type with Excel to Table

588
9
09-04-2019 03:59 PM
JoeBorgione
MVP Esteemed Contributor

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

That should just about do it....
0 Kudos
9 Replies
DanPatterson_Retired
MVP Esteemed Contributor

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.

TedKowal
Regular Contributor II

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 

RandyBurton
MVP Regular Contributor

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.

JoeBorgione
MVP Esteemed Contributor

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

That should just about do it....
DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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?

That should just about do it....
0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Joe... forgot to post the updates from May … thanks for the catch... see the attached, hopefully it is the correct one

Unzip.  *.tbx and scripts folder are inside

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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?

That should just about do it....
0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos