topic Re: Why TEXT format in Excel still give , for ZIP in ArcGIS Online Questions

Why TEXT format in Excel still give , for ZIP

ChristofferLindhgren — Thu, 25 Nov 2021 09:56:46 GMT

Im trying to upload order data per Zip to ArcGis. I have formatted the Zip as TEXT but I still get the wrong format when its uploaded. As you can see below is there a , in the ZIP which means that it will not match with the Zips in ArcGis that don’t include a , . Attached can you see the file I have uploaded for the Printscreen but I have tried different countries, zips, formats but nothing seem to be working and I cant understand why.

Is there anyone that know whats wrong with the file Im trying to upload so that I get these , in the ZIP?

Re: Why TEXT format in Excel still give , for ZIP

JayantaPoddar — Thu, 25 Nov 2021 12:25:54 GMT

I believe the best thing to do is to Configure Pop-up, > Configure Attibutes and uncheck Use 1000 Separator for the ZIP field.

JayantaPoddar_0-1637842826711.png

JayantaPoddar_1-1637843004497.png

Re: Why TEXT format in Excel still give , for ZIP

jcarlson — Thu, 25 Nov 2021 16:51:58 GMT

When you upload a format like an Excel sheet, AGOL is reading it as text and making its best guesses about each column's data type. You can configure it any way you want in the Excel sheet, but basically none of that translates to a meaningful setting during the import process. If it sees a column with nothing but numbers, it assumes it's a numeric field and formats it accordingly, even dropping leading zeroes (a common post on here).

To be clear, it's got nothing to do with your file, and everything to do with AGOL's import process. When you add an Excel sheet as a content item in AGOL, you ought to be prompted for the column datatypes, and from there, you can explicitly state that the ZIP field is text, not numeric.

Another more reliable way of handling this is to create an empty layer first, with the schema all well-defined. Then use the append tool to load your spreadsheet contents to the layer.

Re: Why TEXT format in Excel still give , for ZIP

ChristofferLindhgren — Fri, 26 Nov 2021 08:20:43 GMT

Thanks! With this did I manage to get the Zip into the right format. But then the next step I want to do when I want to Join Features with another layer still dont work. I have Zip on both layers and want to Join them. This did not work before and I thought it was because of the format of the Zip in my upload. But it still don't work to join them. (see attached file). What can be the reason for that?

Re: Why TEXT format in Excel still give , for ZIP

ChristofferLindhgren — Fri, 26 Nov 2021 08:21:48 GMT

Thanks! With the suggestion Jayanta did I manage to get the Zip into the right format. But then the next step I want to do when I want to Join Features with another layer still dont work. I have Zip on both layers and want to Join them. This did not work before and I thought it was because of the format of the Zip in my upload. But it still don't work to join them. (see attached file). What can be the reason for that?

Re: Why TEXT format in Excel still give , for ZIP

JayantaPoddar — Fri, 26 Nov 2021 10:59:43 GMT

I couldn't see the error message.

Is there a FieldType mismatch between ZIP and ZipcodeN fields?

Then you could create a new Text Field in the attribute table of "Data SE...", and assign the values of ZIP to the new field using Calculate Field.

Re: Why TEXT format in Excel still give , for ZIP

AmandaRing — Fri, 26 Nov 2021 13:01:30 GMT

As long as the values are the same, Join Features should still work even with a field type mismatch. That blank error message looks more like a publishing failure of the output result than a tool failure. @ChristofferLindhgren have you tried running the tool again since that unsuccessful job?

- Amanda

Product Engineer, Web Analysis