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; } } }

Script to contatenate the same field from several records

JoseSanchez

Hi all,

I am looking for a script, preferably a Python script that reads all the records from a file and concatenates them in a result variable.

I am using ArcMap to "Select By Attributes" and I would like to have a way to format a list of IDs I am importing form a text file, or Ms Excell list. To build a Definition Query in the Query Builder window.

Input: (from xyz.txt)

341440

341444

341521

341534

341580

341664

341783

Output: (to zzz.txt)

ID=’341440’ OR ID= ‘341444’ OR ID= ‘341521’ OR ID= ‘341534’ OR ID= ‘341580’ OR ID= ‘341664’ OR ID= ‘341783’

Thanks

Find more posts tagged with

Accepted answers

All comments

curtvprice

Yeah - I've heard this type of join to select rows called a "filter".

There used be an awesome ARCPLOT command that I still miss. (Those were the days.)

reselect <dataset> <dstype> keyfile <table> <field> keyitem <key_field>

I've tried to reproduce this functionality using layers and the Add Join tool but found it slow and unreliable.

Maybe the Make Query tool... or a python solution using sets....

JamesCrandall

Curtis,

In RDBMS-landia, if I have >1000 values I'd probably insert them into a temporary table of the values for my IN operator and setup a JOIN to the main table I am interested in querying. I know that doesn't specifically address your post but is likely the best solution. Or if I were in SQL Server I'd likely opt for the Table variable instead of a Temp Table.

curtvprice

I am not very familiar with "real" RDBMS's so I'm wondering what string length fails if you use the more basic form

FIELD = 1 OR FIELD = 2 or FIELD = 3 ...

For 1000 fields that is at least 14K of text!

I would guess that RDBMS would not support indexes when you use the IN operator - but in theory that wouldn't be a problem with many in the list because IN would parallelize the comparisons (ie all comparisons would be done on each record, in one pass).

JamesCrandall

Keep in mind that the "IN" statement does have limits (depending upon database). I think Oracle limit is 1000? Not exactly sure but I recently had to break up my list of values that built my IN portion of a WHERE clause.

Zeke

Thanks Curtis! I saw the >> icon, but hovering over it said Insert, which is not quite as intuitive as something like Format.

curtvprice

see Posting Code blocks in the new GeoNet‌

Zeke

Even more pythonic! Except I don't know how to format as code on the new forums here...

with open(r'H:\Documents\blah\'output.txt', 'w') as fout:
values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str')
fout writelines("ID='" + str(val) + "' OR " for val in values)

Also, for your output, rather than a bunch of OR clauses, you could make a python list and test using IN:

ID IN ['34012', 35078', '39222'...]

curtvprice

Here's how I do it. I have a function as this something I find myself doing a lot! This function works in 10.0 and 10.1. It reads a table, not a text file. (If you add a field name to the top of your text file, and name the file with a .csv or .txt file extension you can read it directly as a table in ArcGIS.)

def ListUnique(inTable, Field):
    """Create a list of unique values from a table/tableview.

    arguments

    inTable  Table or table view
    Field    Field name
    """
    try:
        try:
            # this will only work for 10.1
            import arcpy.da
            lstValues = [r[0] for r in
                    arcpy.da.SearchCursor(inTable, Field)]
        except ImportError:
            # 10.0 version
            try:
                Rows = arcpy.SearchCursor(inTable, "", "", Field, Field)
                Row = Rows.next()
                lstValues = []
                while Row:
                    lstValues.append(Row.getValue(Field))
                    Row = Rows.next()
            except:
                raise
            finally:
                del Row, Rows
        # unique-ize and sort the list
        lstValues = sorted(set(lstValues))
        return lstValues
    except Exception as msg:
        raise Exception("ListUnique failed\n" + str(msg))

Once you have the list of values, you can make this into a SQL expression from a list like this:

numlist = ",".join(str(L))
strlist = ",".join(["'{}'".format(vv) for vv in L]) # need quotes for string values
where = '"{}" IN ({})'.format(field, numlist) # "FIELD" IN (1,2,3)

James wanted me to use the FIELD = 1 OR FIELD = 2 syntax, so here's a way to do that (this replaces line 03 above):

where = " OR ".join(['"{}" = {}'.format(field, k) for k in strlist])

JamesCrandall

Let's get a bit more pythonic and into just 4 lines:

fout = open(r'H:\Documents\blah\output.txt', 'w')
values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str')
fout.writelines("ID='" + str(val) + "' OR " for val in values)
fout.close()

JamesCrandall

I tried to modify my original post but it won't let me because GeoNet is not a real forum.

Anyway... I noticed that the values will come convert over as decimal, which you may not want. So, simply modify the genfromtxt() method to include the output format as strings.


values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str')

Here's the updated code:


import numpy as np


fout = open(r'H:\Documents\blah\output.txt', 'w')
values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str')
for val in values:
    writevalue = "ID='" + str(val) + "' OR "
    fout.write(writevalue)
    print writevalue

fout.close()

JamesCrandall

Here's a way using NumPy:


import numpy as np


fout = open(r'H:\Documents\blah\output.txt', 'w')
values = np.genfromtxt(r'H:\Documents\blah\input.txt')
for val in values:
    writevalue = "ID='" + str(val) + "' OR "
    fout.write(writevalue)
    
fout.close()

IanGrasshoff

In python, you can open and read/write a file using..

f = open('your file - full path here', 'r')

You can also open it for writing using..

f = open('your file', 'w')

f.write("your text")

For your situation you might want to do this in a script (I am just giving you the overview)

1. Open the File

2. Read each line and concatenate the results to build your select query

using this after you open it..

for line in f:

print line

3. Print the concatenated results or store the result in another file using f.write()

*Note, you might want to write this as a python function so you can pass in the file to read from and the column name to use for your result query. I am not sure exactly how you are trying to use this.

I suggest you read this...

7. Input and Output — Python 2.7.8 documentation

JoseSanchez

how do you build the result string:

Result = "ID=’341440’ OR ID= ‘341444’ OR ID= ‘341521’ OR ID= ‘341534’ OR ID= ‘341580’ OR ID= ‘341664’ OR ID= ‘341783’"

StevenGraf1

So you have 1 field with numbers such as 341423 and you want it to be ID = 341423?

You could field calculate using python. This will calculate every record and add ID = in front of all the numbers. Be sure that your field is a String/Text field. ArcMap may import it in as an integer or double field. I would create a new field and field calculate that one so you don't accidentally type something in wrong.

'ID = ' + !field!

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.