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?

[Anonymous User]

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

[PersefoniKapotas]

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!

[MariaSevier1]

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!

[Anonymous User]

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

[SamSimpson]

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

[VanessaGoold]

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-ar...) 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

[SamSimpson]

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 

[VanessaGoold]

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

[Anonymous User]

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