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

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

PersefoniKapotas — Fri, 25 Oct 2019 06:46:18 GMT

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?

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

Anonymous User — Mon, 28 Oct 2019 17:28:14 GMT

Hi Persefoni,

To clarify one thing here - "arcgis pro recognized it as a string field so I didn't go any further with that idea" - you mean recognized it as an Integer field, correct?

My understanding is that ArcGIS automatically determines field type for Excel (.xls) fields based on entries in the column. The workaround here is to save the Excel workbook as a .csv file, the add as an item to ArcGIS Online. When you add the .csv you will be able to specify the field type as string when uploading.

Hope this helps,

-Peter

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

PersefoniKapotas — Tue, 29 Oct 2019 06:19:21 GMT

Actually, when I added to my ArcGis Pro map, it was listed as string. My problem occurred when I added it to ArcGIS online. That's what I thought was strange. But, I will keep your workaround in mind for the next time. (It would be great if this could be fixed so that one could specify field type when bringing in from excel as well, or at least respecting the field types already designated in excel) Thanks!

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

MariaSevier1 — Tue, 28 Jul 2020 16:31:53 GMT

I'm having the exact same issue. I have an Excel file that has 2 columns which in Excel read as integer but I need them to be text. I've converted them to a text column and chosen to "ignore error" in Excel so that when I export them to csv or another file type (even Excel) I can use them in a different system and the columns are read as text data type. BUT when I upload them to AGO they are converted back to integer. This has made it impossible to use the join and hosted feature class view option for updating my data through AGO. I had to go back to using desktop tools for doing the join and exporting the data. I'm working at an agency that hasn't yet moved to ArcGIS Pro so it's ArcMap for me. Also when I bring the Excel file into ArcMap it's no problem. The converted columns read as text. This is a big problem!

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

Anonymous User — Wed, 29 Jul 2020 00:40:55 GMT

Hi Maria,

Saving the Excel file as a CSV before adding to ArcGIS Online will enable you to choose the field type when you upload:

Hope this helps and let me know if it works for you/if you have any other questions.

Thanks,

-Peter

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

SamSimpson — Tue, 08 Dec 2020 18:33:11 GMT

Hi Peter,

I'm wondering if there is a workaround like this when importing Google Sheets? When I go to import a Sheet from my Google Drive I am not presented with the option to change the field types. Currently I have a ZIP code field I need to bring into AGOL as a string but every time I import it converts it to an integer. I tried specifying in the sheet beforehand that it is a string but no luck.

Thanks,

Sam

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

VanessaGoold — Tue, 08 Dec 2020 20:49:57 GMT

Hi Sam, I am having exactly the same problem. I just tried going into my GSheet and publishing it to the web (File > Publish to Web...) as a .csv and then adding to my AGOL map directly from the URL, hoping that might solve the problem. (See this article: https://www.esri.com/arcgis-blog/products/arcgis-online/data-management/using-google-docs-in-your-arcgis-online-maps/?rmedium=redirect&rsource=blogs.esri.com/esri/arcgis/2012/02/21/using-google-docs-in-your-arcgis-online-maps) Nope - still brought the field in as an integer rather than string, and you can't change the field type that AGOL automatically assigns.

Please let me know if you find a solution to this. I have a case in with ESRI Support. Will keep you posted if I find a solution. Seems like this would be a very common issue that AGOL should be able to handle!

Thanks!

Vanessa

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

SamSimpson — Tue, 08 Dec 2020 21:51:59 GMT

Hi Vanessa,

I would really appreciate you forwarding any answers you receive!

I'm going to look into some workarounds in the meantime.

Thank you,

Sam

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

VanessaGoold — Wed, 09 Dec 2020 16:08:53 GMT

Hi Sam,

I was able to partially solve my problem using the Publish Google Sheet to the web as a .csv and add layer from the web in AGOL (following instructions in the link I gave above): In the Google sheet, I inserted a few dummy rows at the top of the sheet and entered text in the relevant field. Because AGOL automatically chooses the field data type based on the first 5 or 10 rows of data, it sees this as a text/string field. This worked for me but of course then you have one or more rows of irrelevant dummy data.

I am working with U.S. County data and the field in question is the FIPS code. I have tried to add my .csv as a non-location-based table and also as a location-based dataset using FIPS as a County Location field. In both cases, the dialog box includes a message saying "Note: Features will not update if referenced CSV changes." So there is also that problem if you are hoping to use live data in your .csv that will be updated in your AGOL map.

Any progress on your end?

Thanks!

Vanessa

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

Anonymous User — Wed, 09 Dec 2020 21:49:27 GMT

Hi @VanessaGoold & @SamSimpson - if you share your Google Sheets with the Google user pklingman@esri.com I'm happy to take a look. Since there is an active support case that the analyst is working on I'll post the result of that here as well.

It sounds like there are two central issues here 1) we need to be able to specify the field data type when adding data from Google Sheets without inserting dummy data near the top of the columns (probably an enhancement request) and 2) we need to figure out why the specific CSV mentioned above is preventing data updates when the source CSV changes. Does this sound like a good characterization of where we're at with these issues at the present moment?

Thanks,

-Peter

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

VanessaGoold — Thu, 10 Dec 2020 14:51:17 GMT

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%20you%20want%20to%20keep,programming%20that%20trims%20leading%20zeros - 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.

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

SamSimpson — Thu, 10 Dec 2020 16:48:59 GMT

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

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

SamSimpson — Thu, 10 Dec 2020 16:51:39 GMT

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

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

SamSimpson — Thu, 10 Dec 2020 17:04:51 GMT

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.

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

Anonymous User — Thu, 10 Dec 2020 20:55:58 GMT

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

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

souzadias — Wed, 03 Feb 2021 04:57:08 GMT

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.

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

JohannaS — Thu, 09 Mar 2023 17:34:53 GMT

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?

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

JohannaS — Thu, 09 Mar 2023 17:52:35 GMT

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.