topic Re: Table join unsuccessful / Multiple symbology - fields missing in ArcGIS Pro Questions

Table join unsuccessful / Multiple symbology - fields missing

TamraSteens — Mon, 08 Nov 2021 21:10:55 GMT

Hi there,

I have a couple of questions - firstly when I try & do a table join, joining .xslx to an attribute table, the fields join but everything is Null.

Secondly, when I try to vary symbology by attribute a limited number of fields are available.

Any hints on either of these subjects would be much appreciated!

Thanks, Tamra

Re: Table join unsuccessful / Multiple symbology - fields missing

DanPatterson — Mon, 08 Nov 2021 22:59:07 GMT

Excel To Table (Conversion)—ArcGIS Pro | Documentation would be my recommendation, then use that for the join.

A lot can go wrong when using excel.

As for symbology, this?

Attribute-driven symbology—ArcGIS Pro | Documentation

there are limitations

Work with Microsoft Excel files in ArcGIS Pro—ArcGIS Pro | Documentation

make sure your fields are formatted explictly and I advise not having any blank cells in any column. If you need a null/nodata value, provide one rather than leaving cells empty. If you have numeric fields explicitly format them as such in excel.

Re: Table join unsuccessful / Multiple symbology - fields missing

curtvprice — Tue, 09 Nov 2021 05:48:28 GMT

Dan has lots of good advice there. Some of things that go wrong that are easily fixed are:

1) Excel file, worksheet, and field names must start with a letter, no spaces, no special chars except "_". ArcGIS can overlook this until you try a join and then the SQL engines break on the bad names.

2) Do format table data as numeric. The "missing" fields are probably coming as text because there are lots of blanks or strings in the excel file.

Re: Table join unsuccessful / Multiple symbology - fields missing

TamraSteens — Tue, 09 Nov 2021 22:18:46 GMT

Thanks @DanPatterson , that's super helpful.

Thanks @curtvprice. Do you mean make sure ALL Excel data is formatted as numeric? I don't think I follow. The problem is none of the info showed up, not just the blank cells. Do you have any idea why this would have happened?

Thanks,

Tamra

Re: Table join unsuccessful / Multiple symbology - fields missing

curtvprice — Tue, 09 Nov 2021 22:23:23 GMT

If you do a join and your tables go empty this is often because the table or field names are not friendly to SQL queries (Field1, Field2 are friendly, "My field1" "fielda/b" "123" are unfriendly). The formatting is to force data type on the fields if they do read in OK so they will come in as numeric instead of text. This is all discussed in the help documents referenced up thread.

Dan and I both mentioned formatting because of your second question of your original post.

Re: Table join unsuccessful / Multiple symbology - fields missing

TamraSteens — Tue, 09 Nov 2021 22:23:02 GMT

Okay, thanks. I can't see where I have gone wrong, but will have another look.

Cheers

Re: Table join unsuccessful / Multiple symbology - fields missing

curtvprice — Tue, 09 Nov 2021 22:24:37 GMT

Dan's suggestion of copying the table from .xlsx into the geodatabase before doing the join is good advice, joins to excel sometimes just don't work as well.

Re: Table join unsuccessful / Multiple symbology - fields missing

DanPatterson — Tue, 09 Nov 2021 22:51:00 GMT

If there are no matches OR the data are floats/doubles, then that isn't unexpected. Joins work with integer to integer or text to text. Float to float has numeric expression issues

A couple of examples of the records that you are trying to join would be useful

Re: Table join unsuccessful / Multiple symbology - fields missing

TamraSteens — Tue, 09 Nov 2021 23:06:35 GMT

Awesome, I vaguely remember coming across this now that you mention floats. Thanks for your help

Re: Table join unsuccessful / Multiple symbology - fields missing

DanPatterson — Wed, 10 Nov 2021 01:29:55 GMT

let us know if it works

Re: Table join unsuccessful / Multiple symbology - fields missing

TamraSteens — Fri, 19 Nov 2021 02:05:01 GMT

Hi Dan,

Sorry to not reply, I got caught up in other things. Both problems have been solved - I used Add Field to have multiple symbology, and the tables weren't joining because the data in the fields I was trying to join didn't match. Seems simple now, but I guess that's all part of learning new things. Thanks heaps for your help.

Kind regards

Tamra