topic Re: How can I specify the data types for the columns in a CSV I upload? in ArcGIS Online Questions

How can I specify the data types for the columns in a CSV I upload?

MauricioGongora — Fri, 16 Feb 2018 21:38:32 GMT

I'm trying to upload data to a feature layer using a CSV file, but some columns that are strings, ArcGIS recognized it as a number and I can't wire that columns. A dummy row don't work for me because I have to load three values that are choices and they are string type. The disponibles choices are "1", "2", or "3", if I use a dummy row to set those columns as string, for example "abc123", an Object Null Reference Exception is thrown, because "abc123" is an avoide value for these fields.

There is any way to specify that a column is a string type instead a number, when the value is "1", "2", etc?

Re: How can I specify the data types for the columns in a CSV I upload?

RebeccaStrauch__GISP — Mon, 19 Feb 2018 16:47:33 GMT

I would think that if the value is in quote, i.e. "1", it would see it as a string...but I haven't uploaded csv files to Online lately.

Can you attach a copy of the first few rows of your csv so others can test? The data can be dummy, as long as the formatting is the same.

Re: How can I specify the data types for the columns in a CSV I upload?

MauricioGongora — Mon, 19 Feb 2018 17:58:15 GMT

Hi Rebecca, of course, there is an example of the data that I'm trying to upload. The fields with quotes are the fields with problems to recognized as string.

operator_id,line_id,campaign_id,run_id,CreationDate,Creator,EditDate,Editor,Longitude,Latitude
operatorName,lineName,"1","2",2/16/2018 9:44:16 PM,userName,2/16/2018 9:44:16,UserName,-75.244467,-3.168276

I already tried with single and double quotes but it still see it as a number.

Best Regards

Re: How can I specify the data types for the columns in a CSV I upload?

MauricioGongora — Mon, 19 Feb 2018 18:00:16 GMT

the fields campaign_id and run_id are select_one_choice fields and string type too, the options can be option1, option2, but the values are "1", "2" strings, not numbers

Re: How can I specify the data types for the columns in a CSV I upload?

RebeccaStrauch__GISP — Sat, 11 Dec 2021 23:40:03 GMT

So, I tried a few different configurations for the campaign_id and run_id .

<<Note: I'll leave what I started writing below, but I don't think this is an option, since it is no better than the results you were getting above, that is, not wanting to put in a fake value....although you would probably only have to do it for the first line. This may give you or others a better idea.>>

Although this may not be an option, it did stick as a string if I put a ' (single quote) in front, and left off the double quotes completely. A single quote in Excel is seen as a align-left (if I remember correctly) and therefore is invalid for a numeric field. I'm assuming it is now a sctring since if I go into the configure pop-up and then the configure attributes, it shows it as a "single line"

I used my own coordinates so I could show it on my base map, but this is the input of my .csv file. I found that if I opened it in Excel to make it easier to change, then it didn't work, so I was editing in Notepad++

operator_id,line_id,campaign_id,run_id,CreationDate,Creator,EditDate,Editor,Longitude,Latitude
operatorName,lineName,'1,'2,2/16/2018 21:44,userName,2/16/2018 9:44,UserName,-154.244467,64
operatorNam2,lineNam2,'1,'2,2/16/2018 21:44,userName,2/16/2018 9:44,UserName,-154.244467,64‍‍‍

NOTE: the pop-up doesn't show the single quote, but it does seem to show when viewing the attribute tables...so this may not help any.

There is likely a better way to do this, but if in a hurry, that might be a quick fix.

Re: How can I specify the data types for the columns in a CSV I upload?

MauricioGongora — Mon, 19 Feb 2018 19:12:13 GMT

Hi, the single quote does not work, I tried it too. ArcGIS throw an exception and I suppose that is because '1 is not a valid value for the domain of the field, that is "1", "2", for example. I neither can use a first row as dummy to specify the data types because the uploading fails due to the domain of the select_one field.

I probe with a single quote in another string field that is text and the value stored is textually '1, and it is an invalid value for the select_one field.

So, I thing that I'll have to change the values for the domain to alpha-numeric string but it mean that I'll have to update the other apps that uses the feature service too.

Other problem that I've had is that the coordinates doesn't are recognized, the system change the values to a non valid latitude and longitude, I don't know which is the correct format for the coordinates.

I don't know if because the feature layer was created from a survey of Survey123, It works different and has these troubles that if the feature was created with another method.

Best Regards.

Re: How can I specify the data types for the columns in a CSV I upload?

RebeccaStrauch__GISP — Mon, 19 Feb 2018 19:16:55 GMT

I'll tag the https://community.esri.com/groups/survey123?sr=search&searchId=6076e8f8-522e-48f1-af1e-41a6f4a41552&searchIndex=0‌ space since they may have some workarounds or suggestions.

Re: How can I specify the data types for the columns in a CSV I upload?

MauricioGongora — Mon, 19 Feb 2018 19:19:07 GMT

Thank you

Re: How can I specify the data types for the columns in a CSV I upload?

XanderBakker — Mon, 19 Feb 2018 19:53:23 GMT

I just did a simple test to load the csv from my computer and when I do so, it allows me to change the data format that it will apply to the fields. See the interface below (sorry for the Spanish text). As you can see the field campaign_id is detected as integer field:

When I click on the field type, I can set it to Text:

The resulting service seems to be correct if I validate the field type at the REST endpoint:

Re: How can I specify the data types for the columns in a CSV I upload?

RebeccaStrauch__GISP — Mon, 19 Feb 2018 20:29:19 GMT

I never thought about using the "Add Item" from computer. I was draggin and dropping or just adding in the map itself. I think you have the answer xander_bakker . That worked for me too (in English even. )

Re: How can I specify the data types for the columns in a CSV I upload?

MauricioGongora — Mon, 19 Feb 2018 21:09:41 GMT

Hi Xander, thank you, that is a good solution, there is any way to relate the new layer created through that way with the older layer that was created from a survey, since the applications are tied to the current layer?

Uploading the CSV file as a new layer I can create data domain for a select type field?

Best Regards

Re: How can I specify the data types for the columns in a CSV I upload?

XanderBakker — Mon, 19 Feb 2018 22:03:52 GMT

Maybe this document can help: How To: Add coded value domains to a hosted feature service in ArcGIS Online

If you have a web map / web application that is consuming the "old" service, you could use ArcGIS Online Assistant to redirect the service. Be careful, if the schema has changed you may need to edit the json itself (which is kinda risky)