topic Re: Select by attribute then exporting selection in Python Questions

Select by attribute then exporting selection

NathanBaylot1 — Mon, 30 Jan 2012 13:30:34 GMT

arcpy.TableToTable_conversion("Master_TAHI", "C:\\Users\\D2148\\Documents\\Maps\\Parish Roads\\TAHI_LRS\\Parish_roads\\TAHI_parish\\FIPS", "FIPS001", "\"FIPS PARIS\" = '001'")

I am trying to select by attributes in a .dbf table then export it out to a new .dbf with the selected features only. I am having a problem executing this file. It keeps saying there is a error. The maximum record length has been exceeded. The file only has 87000 records.

Also, once this is figured out. How do I continue to go to the next select by attribute using a while statement. I need it to go to the FIPS PARIS = 127 and each FIPS is odd starting with 001 (eg. 001,003,005...)

If the table to table way doesn't work, any suggestions would be helpful. Thanks.

Re: Select by attribute then exporting selection

markdenil — Mon, 30 Jan 2012 14:06:12 GMT

Well, to start; neither your input nor output is a obviously a dBase file. A dBase file has a .dbf extension.
Arc has to be told you are working with a dbase input and expect a dBase output.

Re: Select by attribute then exporting selection

NathanBaylot1 — Mon, 30 Jan 2012 14:16:21 GMT

This is used inside of arcmap, the master tahi file was selected in the python window from drop down and the output I changed to "FIPS001.dbf", This still did not work. So, what I did was run it through the toolbox command with the window pop up and did the same parameters. This still came up with the exceeded records error.

Re: Select by attribute then exporting selection

BruceNielsen — Sat, 11 Dec 2021 22:55:43 GMT

I'm not sure the changes I made to your TableToTable command will solve the error you're getting, but here is how you can set up a loop for all odd numbers from 1 to 127. It will make the appropriate changes to the output file name, and the where clause:

for i in range(1,129,2):
    fipString=str(i).zfill(3)
    arcpy.TableToTable_conversion("Master_TAHI", "C:\\Users\\D2148\\Documents\\Maps\\Parish Roads\\TAHI_LRS\\Parish_roads\\TAHI_parish\\FIPS", "FIPS%s.dbf" % fipString, "\"FIPS PARIS\" = '%s'" % fipString)

Re: Select by attribute then exporting selection

NathanBaylot1 — Mon, 30 Jan 2012 16:00:55 GMT

FIPS = "C:\\Users\\D2148\\Documents\\Maps\\FIPS"       # I changed the destination for a shorter path name
c = 0
FIPSlist = ['001', '003', '005', '007', '009', '011', '013',
        '015', '017', '019', '021', '023', '025', '027',
        '029', '031', '033', '035', '037', '039', '041',
        '043', '045', '047', '049', '051', '053', '055',
        '057', '059', '061', '063', '065', '067', '069',
        '071', '073', '075', '077', '079', '081', '083',
        '085', '087', '089', '091', '093', '095', '097',
        '099', '101', '103', '105', '107', '109', '111',
        '113', '115', '117', '119', '121', '123', '125',
        '127']
while c < len(FIPSlist):
    arcpy.TableToTable_conversion("Master_TAHI",FIPS,"FIPS" + FIPSlist + ".dbf","FIPS_PARIS" = "'" + FIPSlist + "'") #I'm thinking this is the problem with to many ", but I need it to automate choosing the next number. I also think that it is a string and the program won't take it.
    c = c + 1

This is what I came up with because the number part would not be correct because it needs the 001,003, 005 and so forth.
This code comes up with
Runtime error <type 'exceptions.SyntaxError'>: keyword can't be an expression (<string>, line 14)

Re: Select by attribute then exporting selection

NathanBaylot1 — Mon, 30 Jan 2012 16:48:28 GMT

I also tried your way bruce but it did not work

Re: Select by attribute then exporting selection

curtvprice — Mon, 30 Jan 2012 17:25:42 GMT

. I am having a problem executing this file. It keeps saying there is a error. The maximum record length has been exceeded. The file only has 87000 records.

The error has to do with the record length, not the number of records. Dbase tables have some pretty serious limitations compared to GDB tables -- I suspect that is your issue.

Field names cannot be longer than 10 characters.

The maximum record length for an attribute is 4,000 bytes. The record length is the number of bytes used to define all the fields, not the number of bytes used to store the actual values.

The maximum number of fields is 255. A conversion to shapefile will convert the first 255 fields if this limit is exceeded.

The dBASE file must contain at least one field. When you create a new shapefile or dBASE table, an integer ID field is created as a default.

dBASE files do not support type blob, guid, global ID, coordinate ID, or raster field types.

dBASE files have little SQL support aside from a WHERE clause.

Attribute indexes are deleted when you save edits, and you must re-create them from scratch.

Arc 10 Help: Geoprocessing considerations for shapefile output

Re: Select by attribute then exporting selection

ChrisSnyder — Sat, 11 Dec 2021 22:55:45 GMT

How about:

inputTable = r"C:\temp\test\mytable.dbf"
outputWorkspace = r"C:\temp\test"
fipsLlist = ['001', '003', '005', '007', '009', '011', '013', 
'015', '017', '019', '021', '023', '025', '027', 
'029', '031', '033', '035', '037', '039', '041', 
'043', '045', '047', '049', '051', '053', '055', 
'057', '059', '061', '063', '065', '067', '069', 
'071', '073', '075', '077', '079', '081', '083', 
'085', '087', '089', '091', '093', '095', '097', 
'099', '101', '103', '105', '107', '109', '111', 
'113', '115', '117', '119', '121', '123', '125', 
'127']
for fip in fipsList:
   arcpy.TableSelect_analysis(inputTable, outputWorkspace + "\\fips_" + str(fip) + ".dbf", ","FIPS_PARIS = " +  "'" + fip + "'")

Re: Select by attribute then exporting selection

NathanBaylot1 — Mon, 30 Jan 2012 17:29:30 GMT

I ran my most recent code and got the correct outcome except the fact it did not select the attributes needed for each file.
i just tried the code without the "FIPS_PARISH" = '', attribute selection. It created the files correctly.

Re: Select by attribute then exporting selection

NathanBaylot1 — Sat, 11 Dec 2021 22:55:48 GMT

How about:

inputTable = r"C:\temp\test\mytable.dbf"
outputWorkspace = r"C:\temp\test"
fipsLlist = ['001', '003', '005', '007', '009', '011', '013', 
'015', '017', '019', '021', '023', '025', '027', 
'029', '031', '033', '035', '037', '039', '041', 
'043', '045', '047', '049', '051', '053', '055', 
'057', '059', '061', '063', '065', '067', '069', 
'071', '073', '075', '077', '079', '081', '083', 
'085', '087', '089', '091', '093', '095', '097', 
'099', '101', '103', '105', '107', '109', '111', 
'113', '115', '117', '119', '121', '123', '125', 
'127']
for fip in fipsList:
   arcpy.TableSelect_analysis(inputTable, outputWorkspace + "\\fips_" + str(fip) + ".dbf", ","FIPS_PARIS = " +  "'" + fip + "'")

arc doesn't recognize the last part as an expression, just as a string.

Re: Select by attribute then exporting selection

NathanBaylot1 — Wed, 01 Feb 2012 14:16:29 GMT

Still having problems with the where clause. The select features will not go through the list loop. It only wants to find the list name since it is in quotes.
I just can't figure this out.

Re: Select by attribute then exporting selection

MathewCoyle — Wed, 01 Feb 2012 14:41:35 GMT

Your code gives me a syntax error so it definitely won't run if that is what you are using.
This should be your where-clause I believe.

"FIPS_PARIS = '" + fip + "'"

Re: Select by attribute then exporting selection

curtvprice — Sat, 11 Dec 2021 22:55:50 GMT

Still having problems with the where clause. The select features will not go through the list loop. It only wants to find the list name since it is in quotes.
I just can't figure this out.

You've got a syntax error. I highly recommend using a syntax checker. Corrected (using escapes for the single quotes -- just for clarity)

arcpy.TableSelect_analysis(inputTable, outputWorkspace +\
    "\\fips_" + str(fip) + ".dbf", \
    "FIPS_PARIS = " +  "\'" + fip + "\'")

Using string formatting and the os.path module to construct paths is a lot easier to code and debug:

query = "FIPS_PARIS = \'%s\'" % fip
outTable = os.path.join(outputWorkspace,"fips_%s.dbf" % fip)
arcpy.TableSelect_analysis(inputTable,outTable,query)

And there is a new string format() method (introduced in Python 2.4) that will eventually replace it:

query = "FIPS_PARIS = \'{0}\'".format(fip)
outTable = os.path.join(outputWorkspace,"fips_{0}.dbf".format(fip)
arcpy.TableSelect_analysis(inputTable,outTable, query)

Re: Select by attribute then exporting selection

NathanBaylot1 — Sat, 11 Dec 2021 22:55:53 GMT

import arcpy
FIPS = "C:\\Users\\D2148\\Documents\\Maps\\FIPS"
c = 0
# FIPSlist = ('001', '003', '005', '007', '009', '011', '013', #Try smaller list first
        #'015', '017', '019', '021', '023', '025', '027',
        #'029', '031', '033', '035', '037', '039', '041',
        #'043', '045', '047', '049', '051', '053', '055',
        #'057', '059', '061', '063', '065', '067', '069',
        #'071', '073', '075', '077', '079', '081', '083',
        #'085', '087', '089', '091', '093', '095', '097',
        #'099', '101', '103', '105', '107', '109', '111',
        #'113', '115', '117', '119', '121', '123', '125',
        #'127')
#FIPSnum = (001, 003, 005, 007, 009, 011, 013, #Try Smaller list first
       # 015, 017, 019, 021, 023, 025, 027,
       # 029, 031, 033, 035, 037, 039, 041,
       # 043, 045, 047, 049, 051, 053, 055,
       # 057, 059, 061, 063, 065, 067, 069,
       # 071, 073, 075, 077, 079, 081, 083,
       # 085, 087, 089, 091, 093, 095, 097,
       # 099, 101, 103, 105, 107, 109, 111,
       # 113, 115, 117, 119, 121, 123, 125,
       # 127)
FIPSlist = ('001', '003', '005')
FIPSnum = (001, 003, 005)
while c < len(FIPSlist):
        for fip in FIPSlist:
                query = "FIPS_PARIS ='" + fip + "'"
               # arcpy.TableToTable_conversion("TRIAL1",FIPS,"FIPS" + FIPSlist + ".dbf",query)
                arcpy.TableSelect_analysis("Master_TAHI", FIPS + "\\FIPS" + FIPSlist + ".dbf",query)
        c = c + 1

I think this will work as it was trying to work and made the first file correct. However, it will not loop anymore

Re: Select by attribute then exporting selection

curtvprice — Wed, 01 Feb 2012 16:20:31 GMT

However, it will not loop anymore

Your loop is incorrect. You don't need a counter or while loop.

FIPSlist = ('001', '003', '005') for fip in FIPSlist:     query = "FIPS_PARIS ='" + fip + "'"     arcpy.TableSelect_analysis("Master_TAHI", FIPS + r"\FIPS" + fip + ".dbf",query)

Re: Select by attribute then exporting selection

NathanBaylot1 — Sat, 11 Dec 2021 22:55:56 GMT

It finally worked! Thank you so much!

import arcpy
FIPS = "C:\\Users\\D2148\\Documents\\Maps\\FIPS"
FIPSlist = ('001', '003', '005', '007', '009', '011', '013', 
        '015', '017', '019', '021', '023', '025', '027',
        '029', '031', '033', '035', '037', '039', '041',
        '043', '045', '047', '049', '051', '053', '055',
        '057', '059', '061', '063', '065', '067', '069',
        '071', '073', '075', '077', '079', '081', '083',
        '085', '087', '089', '091', '093', '095', '097',
        '099', '101', '103', '105', '107', '109', '111',
        '113', '115', '117', '119', '121', '123', '125',
        '127')
for fip in FIPSlist:
  query = "FIPS_PARIS ='" + fip + "'"
  arcpy.TableSelect_analysis("Master_TAHI", FIPS + "\\FIPS" + fip + ".dbf",query)

This worked great. The thing is... I tried this combo many times. I think it was the placement of the " or '.

Re: Select by attribute then exporting selection

AmySwan — Tue, 21 Feb 2012 22:10:41 GMT

I'm new to Python and what I want to do is very similar, only my list is of integer values (created by using range()). Using the above example, how would the query expression be changed if FIPSlist was integer instead of string (query field is also integer)?

Re: Select by attribute then exporting selection

curtvprice — Sat, 11 Dec 2021 22:55:58 GMT

I'm new to Python and what I want to do is very similar, only my list is of integer values (created by using range()). Using the above example, how would the query expression be changed if FIPSlist was integer instead of string (query field is also integer)?

Just do it without the quotes. Also, to use the integer as part of the expression you need to convert the number to a string, using str() too:

FIPSlist = range(5) # (0,1,2,3,4)
for fip in FIPSlist:
    query = "FIPS_PARIS = " + str(fip)
    arcpy.TableSelect_analysis("Master_TAHI", FIPSpath + r"\FIPS" + str(fip) + ".dbf",query)

Re: Select by attribute then exporting selection

ChrisSnyder — Sat, 11 Dec 2021 22:56:01 GMT

How about:

import arcpy
myOutputTable = r"C:\temp\test.gdb\input"
for i in range(1,1001):
   myOutputTable = r"C:\temp\test.gdb\table_id_" + str(i)
   arcpy.TableSelect_analysis(myInputTable, myOutputTable, "MY_ID = " + str(i))

Re: Select by attribute then exporting selection

sofiatavantzi — Fri, 21 Sep 2012 15:14:14 GMT

Hello! i think my question is simple but i can not find a way on answering it.
I want to make a query in attribute table(arcmap) in which i will get the results for the maximum value of a column.
I have a table with 4 columns [District] [total number of schools] [students] [name of schools], i am interested to get the result from the column students, since the question is 'which district has the most students'.
How can i represend in arcmap the value max?