Script to contatenate the same field from several records

1274
14
09-26-2014 05:37 AM
JoseSanchez
Occasional Contributor III

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 xxx.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

0 Kudos
14 Replies
StevenGraf1
Occasional Contributor III

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!

0 Kudos
JoseSanchez
Occasional Contributor III

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’"

0 Kudos
IanGrasshoff
Occasional Contributor

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

0 Kudos
JamesCrandall
MVP Frequent Contributor

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() 



JamesCrandall
MVP Frequent Contributor

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()

0 Kudos
JamesCrandall
MVP Frequent Contributor

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()

Zeke
by
Regular Contributor III

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

  1. with open(r'H:\Documents\blah\'output.txt', 'w') as fout:

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

  3.     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
MVP Esteemed Contributor
Zeke
by
Regular Contributor III

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

0 Kudos