topic Re: Specify a field type with Excel to Table in Python Questions

Specify a field type with Excel to Table

JoeBorgione — Wed, 04 Sep 2019 22:59:56 GMT

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

Re: Specify a field type with Excel to Table

DanPatterson_Retired — Wed, 04 Sep 2019 23:18:52 GMT

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.

Re: Specify a field type with Excel to Table

TedKowal — Thu, 05 Sep 2019 14:28:41 GMT

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

Re: Specify a field type with Excel to Table

RandyBurton — Thu, 05 Sep 2019 16:46:22 GMT

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.

Re: Specify a field type with Excel to Table

JoeBorgione — Thu, 05 Sep 2019 16:54:53 GMT

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

Re: Specify a field type with Excel to Table

DanPatterson_Retired — Thu, 05 Sep 2019 17:47:35 GMT

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

Re: Specify a field type with Excel to Table

JoeBorgione — Thu, 05 Sep 2019 18:00:31 GMT

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?

Re: Specify a field type with Excel to Table

DanPatterson_Retired — Thu, 05 Sep 2019 18:30:54 GMT

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

Re: Specify a field type with Excel to Table

JoeBorgione — Thu, 05 Sep 2019 18:48:07 GMT

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?

Re: Specify a field type with Excel to Table

DanPatterson_Retired — Thu, 05 Sep 2019 22:24:18 GMT

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