How to change field type in Excel before importing?

[EllieHakari]

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.

[JoeBorgione]

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.

[DanPatterson_Retired]

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]

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.

[DanPatterson_Retired]

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

[DarrenWiens2]

"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]

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

[DanPatterson_Retired]

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

[EllieHakari]

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]

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!