nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Merge Address Fields into one...

TheodoreF

Hello.

I have a large (20+ million) address database point FC in FGDB format. Each address record is split into multiple fields like 'organisation', 'building name', 'building number', 'post town', 'post code' etc. Not every field is populated with data, there are many null values (not every address has an 'organisation' name for example).

I've created a new text field called 'Full_Addr' which will merge all the address fields into one. So far I used this VB script in field calculator:

Full_Addr=

[OrganisationName]& ", " & [BuildingName]& ", " & [BuildingNumber]& ", " & [Thoroughfare]& ", " & [PostTown]& ", " & [Postcode]

However this results in entries like:

, , 1, Example Street, Example Town, AB1 2CD

Example Organisation, , , Example Street, Example Town, AB1 2CD

Is there a smarter, more efficient way of creating a merged address field without the double, triple or quadruple ", "?

Find more posts tagged with

Accepted answers

All comments

DanPatterson_Retired

python parser... in Pro... join the contents in 2 fields unless one of them contains None. Since we might be combining test and string, just convert to string first. Lastly, join the 2 with a comma-space separator

<SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>str<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> <SPAN class="punctuation token">[</SPAN>!file_part!<SPAN class="punctuation token">,</SPAN>!X_min!<SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">if</SPAN> i <SPAN class="keyword token">is</SPAN> <SPAN class="operator token">not</SPAN> None<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

DanPatterson_Retired

Yes, the a, b, c, d were just placeholders for the field names (with appropriate pre- and post markers ie ! for python).

To ensure that your environment gets the correct fieldname surround, it is best to set the parser used first, then physically select the fieldname from the available fields rather than type them in full. If there is a chance that there are going to be numeric values involved, you can cast them to string. For example, for a simple 2 field concatenation with a check I used this example to do the change to string since X_min was numeric... I didn't want anything to be done to the record if 'i' was a None value in either field. Since the result was going to a string field, I just made sure that the values were changed to string. I could have written a function for it... but I made the list ( [ !file_part!, !X_min! ] ) within the test rather than pre-constructing the list...

Just do what is faster especially if you don't need to reuse a function

", ".join([str(i) for i in [!file_part!, !X_min!] if i is not None])

TheodoreF

This may sound silly (I'm still learning), but are 'a' , 'b , 'c' and 'd' substitutes for my field names?

Since I posted my question, I'm actually ommitting OrganisationName and PostTown from my final output, so now with less variables it might be more simple.

TheodoreF

thanks for all the help. I'll give your suggestions a whirl today!

Theo

TedKowal

In the field calculator VB script does not have any more efficient way. In VB language ... like .net -- yes there are other ways. Testing for Null's in VB is by using the "is NULL" test. However in my experience with addresses I use an old fashion test and test for one blank space by doing something like this ... if a & "" = "" then do something. In vb if a null is concatenated with a space then the value will be a space.

I am an old fashion VB'r but I would highly recommend doing this in Python there is less and less support for VB. Already ESRI has downgraded from VBA to VBScript in ArcMap.

bharold

Dan's suggestion is valid, to expand on it you could use Calculate Field to do the concatenation, but another simple way is with the Field Map in Feature Class to Feature Class, add a new output field with Join merge rule and space or comma join value then add input fields to the new output field.

BillFox

I have not tried it but this might do what you need: System Toolboxes > Geocoding Tools > Standardize Addresses

DanPatterson_Retired

Actually, you can emulate the following, by simply collecting the field values into a list and assuming you are working in a geodatabase, None should be the equivalent of <Null>

Worth a look... the key is in the ", ".join bit and the test for null

BTW this is python... don't know if vb* has a equivalent, but switch to the python parser to use this idea

a <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'b'</SPAN><SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'d'</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>i <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> a <SPAN class="keyword token">if</SPAN> i <SPAN class="keyword token">is</SPAN> <SPAN class="operator token">not</SPAN> None<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="string token">'a, b, c, d'</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.