How to change field type in Excel before importing?

10810
13
10-27-2016 12:21 PM
EllieHakari
New Contributor II

Hello everyone!

I'm currently trying to calibrate stationing along a highway, but I need the stationing to be straight numbers ("54000" instead of the typical "540+00"). I have an Excel document that the correct formatting is in, and I thought I'd join it with our current stationing shapefile, but when I import it into ArcMAP, the field is automatically changed to a string. I need it to be a number field. That's the only way the calibrate tool will work. Any suggestions would be greatly appreciated!

Things I've tried:

Changing field type from ArcCatalog (which I found you can't change from text to number)

Setting the type in Excel to number

EDIT: I fixed it myself. Apparently if you save your Excel document as a .csv first, then import it, ArcMAP will automatically make the field numeric.

13 Replies
JoeBorgione
MVP Emeritus

You say the spreadsheet has the 'correct formatting'.  Which is correct: 5400 or 54+00?

I'm getting lost with your terminology and methodology:

I thought I'd join it with our current stationing shapefile....   What is the keyfield with which you are joining the excel spreadsheet and the shapefile ?(I hope you mean feature class, shapefiles are so 90's)

but when I import it into ArcMAP...   What do you mean by import?  Import is a specific method with which to transfer data from one database to another. For example you import Table X  into A.gdb from B.gdb.  Do you really mean when I add my spreadsheet as a table to an ArcMap session?

If you can clarify what you have and what you are doing, we might be able to help you.

That should just about do it....
0 Kudos
DanPatterson_Retired
MVP Emeritus

Well you can still fix it using the field calculator in Arcmap

>>> a
'540+00'
>>> "{}{}".format(*(a.split("+")))
'54000'
>>> b = int("{}{}".format(*(a.split("+"))))
>>> b
54000

So I am guessing the field calculator expression would be... using the python parser of course

int("{}{}".format(*(!a!.split("+"))))
# where !a! is the field name containing the text enclosed in ! marks

worth a shot... you can probably emulate it in excel if you must

EllieHakari
New Contributor II

I did use the field Calculator at first, but the field is already Text. That's why I needed to move it over to Excel first.

0 Kudos
DanPatterson_Retired
MVP Emeritus

add a new integer (long field)... and do the calculation in it... the !a! field is the one containing the text

0 Kudos
DarrenWiens2
MVP Honored Contributor

"540+00" is interpreted by ArcGIS as a string no matter what you tell Excel the format is, because it contains a non-numerical character. ArcGIS will interpret "54000" as a number, so you can either change it in Excel or import it as a string into ArcGIS and change it as Dan suggests. 

JoeBorgione
MVP Emeritus

Then add a field called StationID_Int and calculate it's value with Dan's method...

That should just about do it....
0 Kudos
DanPatterson_Retired
MVP Emeritus

Joe.... I suspect interest is lost since it was marked "assumed answered"

0 Kudos
EllieHakari
New Contributor II

I was able to get it fixed myself, but I want to keep all these answers up here in case they help someone else. This was the final result from my project:

I had to keep the stationing with the '+' in one field so I could label with it, but the actual calibrated route refers to the stationing without the '+'. And then, of course, so do the orange lines. They were created by inserting a table with start and end stationing, and then displaying route events. I'm really glad I got this figured out because I feel it's something our geologists want fairly frequently but are unsure of the process. I will probably skip using Excel next time now that I know you can cross reference fields in the Field Calculator. 

TedKowal
Occasional Contributor III

In case people are not aware:  Excel determines numeric/date/text columns based upon some first number of rows -- There is nothing inside excel you can do with this behavior.  For this reason, I always export the data as text to Access then format as necessary.  If it is data I have to deal with alot then I make template access tables with correct formatting.   

A word to the wise -- Never trust an excel export or link!