0

I made some adjustments to existing code, and now there seems to be a glitch down the line in an area where no adjustments were made.

My initial goal was to ensure the correct data types are shown in ArcGIS. Previously, I created an Excel spreadsheet (DatabaseJoin.xlsx) that sourced select information from another Excel spreadsheet. My Excel spreadsheet (DatabaseJoin.xlsx) contained formulas that pulled and displayed numbers, text, and dates taken from the original Excel spreadsheet.

Next, in ArcPy, I converted the Excel spreadsheet (DatabaseJoin.xlsx) to a dbf file (DatabaseJoin.dbf)to get rid of the formulas using ExceltoTable_conversion. I then joined the dbf file (DatabaseJoin.dbf) to a layer within my geodatabase (Projects.shp).

The problem with this original method was that everything was displayed as STRING, so I couldn't summarize the numbers in my mxd file.

My new method was to manually save a csv file (DatabaseJoin.csv) from the Excel spreadsheet (DatabaseJoin.xlsx) and make sure that all fields were saved as number, date, text, etc. In ArcPy, I joined the csv file (DatabaseJoin.csv) to my geodatabase layer (Projects.shp) and the field types carried over so I can now use the Statistics tool.

However... now I am getting issues when I alter the field names. I am receiving the following error message:

ExecuteError: Failed to execute. Parameters are not valid.  
ERROR 000800: The value is not a member of TEXT | FLOAT | DOUBLE | SHORT | LONG | DATE | BLOB | RASTER | GUID.
Failed to execute (AlterField).

This error must be an issue with the csv file, right? I'm not sure if it has anything to do with it, but I notice when I open up the dbf file of the geodatabase layer in Excel, all of the field types show as 'NUMBER', even the text entries.

Here is my original code:

 ##### JOIN TO MAIN DATABASE
#Convert xlsx to table:
import xlrd

in_excel= r'C:\\VisualDatabase\\Planning_Out\\DatabaseJoin.xlsx'
out_table= r'C:\\VisualDatabase\\Planning_Out\\DatabaseJoin.gdb'

# Perform the conversion
join_table= arcpy.ExcelToTable_conversion(in_excel, out_table)

# Permanent Join
inFeatures= r'C:\\VisualDatabase\\Planning_Out\\Projects.shp'
layerName= "projects"
P_join=arcpy.MakeFeatureLayer_management(inFeatures, layerName)

# Set the local parameters
joinField= "Name"
joinField_P = "Contract"
outFeature = r'C:\\VisualDatabase\\Planning_Out\\P_join.shp'
joinTable= r'C:\\VisualDatabase\\Planning_Out\\DatabaseJoin.dbf'

# Join feature classes by joinField
arcpy.AddJoin_management ("projects", joinField, joinTable, joinField_P, "KEEP_COMMON")
arcpy.CopyFeatures_management("projects", outFeature)

#Contract
#Set local variables
Contractfield = "Database_1" 
Contract_new_length = "20"
arcpy.AlterField_management(gdb,Contractfield, "CON", "CON", "TEXT", CON_new_length, "NULLABLE")

#Length
#Set local variables
Lengthfield = "Database_8" 
Length_new_length = "200"
arcpy.AlterField_management(gdb,Lengthfield, "LENGTH", "Length", "TEXT", Length_new_length, "NULLABLE")

Here is my new code:

    # Permanent Join
    inFeatures= r'C:\\VisualDatabase\\Planning_Out\\Projects.shp'
    layerName= "projects"
    P_join=arcpy.MakeFeatureLayer_management(inFeatures, layerName)

    # Set the local parameters
    joinField= "Name"
    joinField_P = "Contract"
    outFeature = r'C:\\VisualDatabase\\Planning_Out\\P_join.shp'
    joinTable= r'C:\\VisualDatabase\\Planning_Out\\DatabaseJoin.csv'

    # Join feature classes by joinField
    arcpy.AddJoin_management ("projects", joinField, joinTable, joinField_P, "KEEP_COMMON")
    arcpy.CopyFeatures_management("projects", outFeature)

    #Contract
    #Set local variables
    Contractfield = "Database_1" 
    Contract_new_length = "20"
    arcpy.AlterField_management(gdb,Contractfield, "CON", "CON", CON_new_length, "NULLABLE")

    #Length
    #Set local variables
    Lengthfield = "Database_8" 
    Length_new_length = "200"
    arcpy.AlterField_management(gdb,Lengthfield, "LENGTH", "Length",  Length_new_length, "NULLABLE")

Maybe field mapping can fix this and make it easier?

1

This is not an arcpy solution, but it would work.
You could also add fields to your attribute table of a shapefile with correct data as string. The fields you add must then have the right format (integer, double, date...)

Next step would be to use field calculator to "move" the attributes into the right fields (from string field "ID_Code" to integer field "ID_Code_1)". With this in mind you could probably edit your field names not exactly as they should be in the end (like e.g. "ID_Code" instead of "ID_Code_1").

  • This makes sense and can actually be done in ArcPy also... it would solve some of my issues with manually creating a csv before I run my code. – Jessica Marie Jul 6 '17 at 15:40
3

In your new code, as indicated by the error, a parameter is not valid. Specifically, the parameter field_type is wrong.

Your new code:

arcpy.AlterField_management(gdb,Contractfield, "CON", "CON", CON_new_length, "NULLABLE")

Compare to your previous code:

arcpy.AlterField_management(gdb,Contractfield, "CON", "CON", "TEXT", CON_new_length, "NULLABLE")

field_type is skipped, unlike in your previous code, which indicates "TEXT". You are putting in the value of field_length in the place as field_type.

  • AlterField_management (in_table, field, {new_field_name}, {new_field_alias}, {field_type}, {field_length}, {field_is_nullable}, {clear_field_alias}) instead of 'gdb' in first place, she needs the 'in_table' that would be a table view, a raster layer or a mosaic layer. So with your needs, it would be a table view – DirkB. Jul 5 '17 at 18:34
  • Emil- you cannot specify a field_type if the table contains attributes. From the Esri website, "Field_type (optional)= The new field type for the field. Only applicable if the input table is empty (does not contain records)." Because my table is based off of a spreadsheet in Excel, the populated values cannot change 'field_type' once they are brought into ArcGIS. I probably should have taken this out of my previous code example. – Jessica Marie Jul 5 '17 at 22:31
  • @JessicaMarie Then update the parameter with None. arcpy.AlterField_management(gdb,Contractfield, "CON", "CON", None, CON_new_length, "NULLABLE") – Emil Brundage Jul 5 '17 at 22:50
  • Thank you @EmilBrundage! Adding the 'none' worked, even though the esri web page said it was an optional parameter. – Jessica Marie Jul 6 '17 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.