Unable to change text field to numeric field to show xy data

[SBreckan]

Hi,

I have a table, originally imported from excel, with two fields with coordinates that I cannot manage to convert to numeric fields. I want to display the content of the table's XY data, and that cannot be done when the fields are defined as text. I tried adding two new empty fields defined as double and using Field calculator to "copy" the content from the coordinate fields defined as text. I got the error message "The calculated value is invalid for the row with ObjectID = 1. For example, the calculated value may be too large for the field or you may be trying to add a string to a number field. This row will not be updated. Do you want to process the rest of the rows?" I said yes and there was the same problem with the rest of the rows. There are no spaces, commas or Null values in the coordinate fields defined as text. An example of the coordinates in my table: X "274676.9" and Y "6633201.0"

I also tried Table to table, but then I got error 001156 with description "text values cannot be added to numeric fields".

Is there no other way to convert fields in a table from text to numeric?

I tried changing the definition to Number (instead of Standard) in excel and importing it again, but it is still defined as text. The table is a result of many joins, but exported to a new table and the joins are gone now (right?)

I use arcgis 10.2.

Thanks in advance.

[BillChappell]

Ah the advantages of using a spreadsheet for a database. You ruled out the obvious like spaces, characters, quotes, and nulls. Have you rules out the field names like longitude being shortened to long a reserved word? Field names with spaces also cause problems, if you ever plan to export as a shapefile the names have to be 10 characters or less without spaces or risk truncation. Check to make sure the cells also have values not formulas.

Next step if you have checked everything ans it still doesn't work, try to export excel as a CSV and bring that in. If the X,Y fields still don't come in as numbers, you have a character in there somewhere, maybe a comma? Or if you have an older version of excel export as a dbf. Another option use Access, as an mdb files, import the excel table, ArcMap should now be able to read it.

One thing you can count on is excel always being a problem, and people using excel as a database.

[AdrianWelsh]

Bill,

I have this problem all the time. I fully agree about converting it to a CSV. This is likely the best bet to rule out any errors. Though, there isn't much we (as GIS people) can do about people using excel in place of a database. I would say that most 'regular' people in any field would throw data into Excel versus going through the trouble of trying to populate a database. It is s problem but it's something that will always happen.

[BillChappell]

I ran into this when I taught GIS at a college. For one class, students would use excel and their smartphones or GPS units and have trouble bringing data into ArcMap. First it was because they captured points as DMS and then you would explain why they needed Decimal Degrees, and show them how to convert DMS to DD. Next they would struggle because their new converted fields were really just formulas. Even if they had it as DD it usually came in as a string field, or they had 0,0 points if they used number fields.

It was always a frustration lesson that finally had them using CSV as a simple fix.

[SBreckan]

Thank you Bill. Converting to csv also worked, although Jake's solution was faster. I will make sure to convert to csv in the future, it also reduced the size of the table and made my slow computer easier to work with. Much appreciated!

[JakeSkinner · Accepted solution]

Hi S,

Add two new fields of type 'Double' and then you can use the field calculator to convert the text fields with the following expression:

float(!Lat!)‍

You will want to replace 'Lat' with the field name, but keep the exclamation points.  Also, you will want to make sure 'Python' is checked at the top of the field calculator.  Ex:

[SBreckan]

Thank you! I tried using Field calculator a number of times, but I used VB Script and just double clicked my old X field defined as text. But this worked! I am very grateful.

[TedKowal]

 ...... gotta love VB!   I just works!         BTW:  if you are using vbscript ....  Val([yourField]) will work stripping the spaces and non numeric text....

[AlisonWilliams1]

Is there a solution for if the table is imported, but for some reason, is 'read-only'? I am signed into my organization page and there are no pending edits. I cannot find any specific guidance on this online, or how to make the table editable.