Query a csv file with a search cursor to join certain entries to another shapefile

[LisaD]

I am stuck and hoping someone here can help.  I am rather new to Python but came up with something I thought could help me and was attempting to implement it. 

I have a comma delimited file that contains a few columns of data, three of which I want to use.  I have rows of data categorized under one column called "SP" which has numbers from 1 to 10.  I want to be able to write a loop script that parses only the data for SP = 1, joins that data to another shapefile based on a column called Cell_ID, then for SP = 2 and joins that to a shapefile based on the column called Cell_ID, then SP = 3 and joins that to another shapefile based on a column called Cell_ID and so on.

I thought this task could be done using a search cursor with a where clause but I am not getting too far.  My guess is I am making it too hard because that is what I always do :9(

Could I get some hints as to if the search cursor would work (can you join to another shapefile based on the results of a search cursor?) and if so how would the where clause be implemented.  If that won't work can you point me in the right direction?

[MathewCoyle]

I would try making a tableview with a where clause. Make a list of your where clauses or create them on the fly depending on the complexity. You may have to convert your data to a different table type first, I don't believe csv is directly supported for this tool.

[LisaD]

I would try making a tableview with a where clause. Make a list of your where clauses or create them on the fly depending on the complexity. You may have to convert your data to a different table type first, I don't believe csv is directly supported for this tool.

I will try that and let you know.  I did not know about tableview.

[LisaD]

I used the maketableview_management command and the script did run.  Is there something I can print out that will tell me if a temporary table with the desired information was definitely made?  Otherwise I will just have to attempt a join with a shapefile to see if it did what I wanted it to do.

[MarcinGasior]

1. Converting CSV to table/geodatabase table, as Mathew suggested, greatly simplyfies processing.

2. To display Table View content you can create a Search Cursor on it or create "normal" table with 'Copy Rows' tool.

3. I created the following script which joins all fields from CSV data tablein GDB to separate feature classes based on value in SP field:

import arcgisscripting
gp = arcgisscripting.create(9.3)

gp.workspace = r"Z:\Work\AttrFromCSV_to_FC.gdb"
csvDataTable = r"Z:\Work\AttrFromCSV_to_FC.gdb\CSVdata"

#Create list of feature classes in current workspace
#ASSUMPTION: feature class layers share part of name with SP value: eg. layer_1 for SP=1
fcLst = gp.ListFeatureClasses()

#loop through possible classes and for each create TableView
for sp in range(1,11): #range (1 to 10)
    whereClause = '"SP" = ' + str(sp)
    gp.MakeTableView_management(csvDataTable,"csv_" + str(sp), whereClause)

    #for corresponding feature class in workspace create Feature Layer
    fcName = "SamplePoints_" + str(sp) #name of input Feature Class
    if fcName in fcLst:
        inputFLName = "inputFC_" + str(sp) #name of temporary Feature Layer
        gp.MakeFeatureLayer_management(fcName, inputFLName)
        
        #join data from Table View with 'Add Join' tool
        #"KEEP_COMMON" parameter cause preserving only maching records
        gp.AddJoin_management(inputFLName,"ID", "csv_" + str(sp), "Cell_ID","KEEP_COMMON")
        
        #save feature layer with joined data
        gp.CopyFeatures_management(inputFLName, "Joined_"+fcName)
        print "Joined_" + fcName + " created"

If you have AG10 or ArcInfo license with AG9.3:
Instead of using AddJoin and CopyFeatures, you can use JoinField_management tool which is able to join specified list of fields to oryginal feature class (without creating new one).

[LisaD]

I appreciate the code - but being the novice that I am I can't get it to work.  Likely missing some small thing.  First let me show you the code I wrote last night with the addition of two of your lines from the code you gave me.

import arcpy
arcpy.env.overwriteOutput = True

# set table path
intable = "C:/Project/Project.gdb/DeltaWL"
shapefile="C:/Project/Project.gdb/model_nodes"
spField = "SP"
deltaField="Delta"
cellidField="Cell_ID"

rows = arcpy.SearchCursor("C:/Project/Project.gdb/DeltaWL", "SP"=='1', "", "SP; Delta; Cell_ID")

currentSP = ""
fields= arcpy.ListFields(intable)
fieldinfo = arcpy.FieldInfo()

for row in rows:
       if currentSP !=row.SP:
              currentSP = row.SP
              currentSP=row.getValue(spField)
              queryString=currentSP

              for field in fields:
                     if field.name == "SP":
                             fieldinfo.addField(field.name, "SP_"+str(currentSP), "VISIBLE", "")
                     elif field.name == "ROW":
                             fieldinfo.addField(field.name, field.name, "HIDDEN", "")
                     elif field.name == "COL":
                             fieldinfo.addField(field.name, field.name, "HIDDEN", "")
                     elif field.name =="Delta":
                             fieldinfo.addField(field.name, "Delta_"+str(currentSP), "VISIBLE", "")
                     elif field.name =="Cell_ID":
                             fieldinfo.addField(field.name, "Cell_ID", "VISIBLE", "")
                           

                     arcpy.MakeTableView_management (intable, "out_" +str(currentSP), "", "", fieldinfo)
                     arcpy.AddJoin_management(shapefile,"ID", "out_" +str(currentSP), "Cell_ID","KEEP_COMMON")
                    
                     #save feature layer with joined data
                     arcpy.CopyFeatures_management(shapefile, "Joined_"+field)

              print queryString
              print "made table"

-----
this now gives me the following error:
The value cannot be a feature class

ERROR 000840: The value is not a Raster Layer.
ERROR 000840: The value is not a Raster Catalog Layer.
ERROR 000840: The value is not a Mosaic Layer.
WARNING 000970: The join field ID in the join table model_nodes is not indexed. To improve performance, we recommend that an index be created for the join field in the join table.
Failed to execute (AddJoin).

I tried the code you gave me and made a few modifications based on names and such:

import arcpy
arcpy.env.overwriteOutput = True

from arcpy import env
import os

arcpy.env.workspace = r"C:\Project\Project.gdb"
model_nodes = "model_nodes"
spField="SP"
dataTable = "DeltaWL"

#Create list of feature classes in current workspace
#ASSUMPTION: feature class layers share part of name with SP value: eg. layer_1 for SP=1
fcLst = arcpy.ListFeatureClasses()

#loop through possible classes and for each create TableView
for sp in range(1,11): #range (1 to 10)
    whereClause = 'spField = ' + str(sp)
    arcpy.MakeTableView_management(dataTable,"Delta_WL" +str(spField), whereClause)

    #for corresponding feature class in workspace create Feature Layer
    fcName = model_nodes + str(spField) #name of input Feature Class
    if fcName in fcLst:
        inputFLName = model_nodes + str(spField) #name of temporary Feature Layer
        arcpy.MakeFeatureLayer_management(fcName, inputFLName)
       
        #join data from Table View with 'Add Join' tool
        #"KEEP_COMMON" parameter cause preserving only maching records
        arcpy.AddJoin_management(inputFLName,"ID", Delta_WL + str(spField), "Cell_ID","KEEP_COMMON")
       
        #save feature layer with joined data
        arcpy.CopyFeatures_management(inputFLName, "Joined_"+fcName)
print "Joined_" + fcName + " created"

----

with this I don't get an error - but nothing is added to my geodatabase.  What am I missing?

[MarcinGasior]

Aha, you have one input feature class, and want to make 10 new? Or just add data to one input feature class?

In first case, try code (it wasn't tested, but it's quite simple so you should be able to follow what's going on):

import arcpy
arcpy.env.overwriteOutput = True

from arcpy import env
import os

arcpy.env.workspace = r"C:\Project\Project.gdb"
model_nodes = "model_nodes"
spField="SP"
dataTable = "DeltaWL"

#create Feature Layer for input feature class
arcpy.MakeFeatureLayer_management(model_nodes, "model_nodes_FL")

#loop through possible classes and for each create TableView
for sp in range(1,11): #range (1 to 10)
    whereClause = 'spField = ' + str(sp)
    tableViewName = "Delta_WL" +str(sp) #name is based on 'sp' iterator
    arcpy.MakeTableView_management(dataTable, tableViewName, whereClause)
    
    #join data from Table View with 'Add Join' tool
    #"KEEP_COMMON" parameter cause preserving only maching records
    arcpy.AddJoin_management("model_nodes_FL","ID", tableViewName, "Cell_ID","KEEP_COMMON")

    #save feature layer with joined data
    arcpy.CopyFeatures_management("model_nodes_FL", "model_nodes_joined"+str(sp))
    
    #remove existing Join to allow make new join with new subset (the second parameter is join name, which is name of joined data table)
    arcpy.RemoveJoin_management("model_nodes_FL", "DeltaWL")

EDIT:
Code corrected.

[LisaD]

Thank you so much!  It worked but I had to move the makefeaturelayer to below the for statement and use delete_management to delete the feature layer before it goes into the next SP.  I can't wait to know as much Python as you people all know.

[MarcinGasior]

Congrats for script debugging - it's first step to dive into Python;). (I'm also at the beginning of the journey with Python.)

I edited my last post and added 2 lines at the end of the code which make it working as it should.
I think that just removing a join can be more efficient solution than making and deleting feature layer (especially when therere larger datasets).