Table join unsuccessful / Multiple symbology - fields missing

432
10
Jump to solution
11-08-2021 01:10 PM
TamraSteens
New Contributor II

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

0 Kudos
2 Solutions

Accepted Solutions
DanPatterson
MVP Esteemed Contributor

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.


... sort of retired...

View solution in original post

0 Kudos
TamraSteens
New Contributor II

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.

TamraSteens_0-1637287341664.png

Kind regards

Tamra

View solution in original post

0 Kudos
10 Replies
DanPatterson
MVP Esteemed Contributor

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.


... sort of retired...
0 Kudos
curtvprice
MVP Esteemed Contributor

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.

0 Kudos
TamraSteens
New Contributor II

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

0 Kudos
curtvprice
MVP Esteemed Contributor

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.

0 Kudos
TamraSteens
New Contributor II

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

Cheers

0 Kudos
curtvprice
MVP Esteemed Contributor

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.

DanPatterson
MVP Esteemed Contributor

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


... sort of retired...
TamraSteens
New Contributor II

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

0 Kudos
DanPatterson
MVP Esteemed Contributor

let us know if it works


... sort of retired...
0 Kudos