topic How to change field type in Excel before importing? in Roads and Highways Questions

How to change field type in Excel before importing?

EllieHakari — Thu, 27 Oct 2016 19:21:44 GMT

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.

Re: How to change field type in Excel before importing?

JoeBorgione — Thu, 27 Oct 2016 19:46:58 GMT

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.

Re: How to change field type in Excel before importing?

DanPatterson_Retired — Sun, 12 Dec 2021 10:26:39 GMT

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

Re: How to change field type in Excel before importing?

DarrenWiens2 — Thu, 27 Oct 2016 19:59:34 GMT

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

Re: How to change field type in Excel before importing?

EllieHakari — Thu, 27 Oct 2016 20:00:24 GMT

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.

Re: How to change field type in Excel before importing?

DanPatterson_Retired — Thu, 27 Oct 2016 20:07:39 GMT

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

Re: How to change field type in Excel before importing?

JoeBorgione — Thu, 27 Oct 2016 20:09:53 GMT

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

Re: How to change field type in Excel before importing?

DanPatterson_Retired — Thu, 27 Oct 2016 20:13:11 GMT

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

Re: How to change field type in Excel before importing?

EllieHakari — Fri, 28 Oct 2016 13:57:38 GMT

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.

Re: How to change field type in Excel before importing?

TedKowal — Fri, 28 Oct 2016 16:20:51 GMT

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!

Re: How to change field type in Excel before importing?

DanPatterson_Retired — Fri, 28 Oct 2016 17:23:31 GMT

Excel used to have the ability to click on the field name and set its properties to prevent wrong data types... or am I thinking of Quattro Pro?

Re: How to change field type in Excel before importing?

EllieHakari — Fri, 28 Oct 2016 17:53:57 GMT

You can set the type (if I understand what you're referencing -- see below) but it doesn't actually change the field type in ArcMAP.

Re: How to change field type in Excel before importing?

DarrenWiens2 — Fri, 28 Oct 2016 18:23:46 GMT

You can set the type but ArcGIS also checks to make sure it makes sense. For example, you can set a column of numbers in Excel to text and it will import as text, but if you set a column of text to number in Excel, it will import as text into ArcGIS because you can't have non-numerical characters in a numerical field within ArcGIS.

Re: How to change field type in Excel before importing?

curtvprice — Sat, 29 Oct 2016 02:50:58 GMT

You can modify a schema.ini file to tweak file types (but of course in this case the + would not support importing as a number as it was rightly pointed out above).

Adding an ASCII or text file table—Help | ArcGIS for Desktop