ArcGIS Online is converting my table's text field to 'integer' data type.

[PersefoniKapotas]

I am trying to add a table to my content as a hosted layer.  It is an excel table that has a column with numeric values stored as text.  I did check to make sure that it was a text/string field.  AGOL automatically converts the field to 'integer' and I can't find a way to convert it back to string, which I have to do because I want to join the table to a layer using this field.  I tried bringing it into arcgis pro first, and arcgis pro recognized it as a string field so I didn't go any further with that idea.  I ended up adding a new string field in agol, and copying over the data (i had to do some calculations to add a 0 in front, etc.). But i'm wondering if there is an easier/more straightforward way. 

Why doesn't AGOL respect the data types from the original table?

[VanessaGoold]

Hi Peter and Sam,

I've received further assistance from ESRI Support and here is the upshot:

1. To preserve plain text formatting from the Google Sheet, you need to insert an empty column, pre-format that as plain text (Format > Number > Plain Text). Then copy your data and use Paste Special...Paste Values Only into your pre-formatted column. Here's a link to explain:  https://www.howtogeek.com/470254/how-to-enter-zero-before-a-number-in-google-sheets/#:~:text=If%20yo... follow the workflow under the "Apply Plain Text Formatting" section.

2. In ArcGIS Online, a .csv published from the web will only refresh if it has location data (e.g., latitude and longitude coordinates). If it's not a spatial table, it won't refresh. So you have to add latitude and longitude to your Google Sheet. In my case, I am using USA County data. Sam, you might be able to find a ready-made table with ZIP Code coordinates out there if you need your data to refresh; otherwise ignore the rest of this. This might be the long way around, but here's how I did it:  I was able to extract centroid points from US Tiger Line County polygon data in ArcGIS Pro (desktop version) following the instructions here: https://support.esri.com/en/technical-article/000009381. This table had latitude, longitude, and FIPS field data in it. I then exported the table to Excel format using the ArcGIS Desktop Table to Excel tool. I imported the Excel data into a new tab on my Google Sheet. Then in my County data tab, I added two new columns, Latitude and Longitude. I used the function:

=Index([insert Latitude column from Excel tab], MATCH([insert first FIPS code cell to look up],[insert FIPS array from the Excel data tab],0))

This returns the Latitude value for the county sharing the same FIPS code in the second tab. Copy down to fill in all values. Then do a whole column Copy > Paste Special > Values Only to get the number value in there rather than the formula. 

Repeat for the Longitude Column.

Next I refreshed the Google Sheet's File > Publish to Web > CSV link and re-imported into my AGOL map using the latitude and longitude coordinates. I'm still working on the join, but at least now I have live, refreshed data imported with my FIPS field coming in correctly as text/string.

[SamSimpson]

Ah, I hadn't seen this additional reply. 

Is this specifically steps for adding a 'published .csv' to AGOL? I attempted the first step with an Google Sheet and was still presented with all integers.

[SamSimpson]

Hi Peter,

I believe those are the issues though I look to @VanessaGoold on the second one, I haven't attempted that route. It seems like having the ability to specify a fields data type with Google Sheets would solve both of the problems?

I shared some similar dummy data with you via Google Sheets. Thank you for your help!

Sam

[SamSimpson]

Hi Vanessa,

I haven't attempted any more tries with this for the moment but I did look at the link you sent - as the blog post was created in 2012 I wonder if this was pre- the ability to add Sheets via a cloud connection? It seems Esri adding a mapping ability to field data types would solve our problems!

I will let you know if anything changes on my end, thank you for suggestions and help!

Samantha

[Anonymous User]

Hi Samantha and Vanessa, 

Thanks for the update on this. I agree that it would be a valid enhancement request for field mapping/specifying data type when adding a Google Sheet to ArcGIS Online. @SamSimpson let me know your customer # and I can get a case going for you to log this.

As for the update without Lat/Lon values, this one is a bit trickier; without Lat/Lon values a geocode request would need to occur to plot the points which could incur unknown amounts of credits depending on the number of records added. @VanessaGoold I looked at your case and it seems the credit usage was from the join in ArcGIS Online; adding points with Lat/Lon does not use credits. 

Thanks,

-Peter 

[JohannaS]

Hi!

I always upload as CSV and have never seen the option to change field type on upload to AGOL. I am having the same issue with text being changed to number in a CSV file that I'm uploading and I can't do the join that I need to do. I can look up how to change or create a new field, but is there any easier way to have it stay the way it is in my CSV file when I upload it?

[JohannaS]

Oh, I see what I was doing! In case anyone else is doing the same thing as me, you have to upload it from my content as a hosted layer (not from a map) https://support.esri.com/en/technical-article/000026756. 

[souzadias]

Hi folks,

I'm having the same issue of AGOL reading text as numeric but also numeric as text. Particularly, if a column stores numeric values but has empty cells (null or empty), AGOL will read it as a string. Of course, I can upload as CSV and edit the field type, one by one, by I have a file with over 100 columns. It's just not practical to edit the field type every time I need to re-upload my file. There needs to be a more predictable way to structure an incoming data file so that they are correctly read by AGOL. Alternatively, a way to assign field type from the original file and avoid fiddling during the upload.