How do I specify field aliases for arcpy.TableToExcel?

[BrandonKeinath1]

I'm having trouble specifying field aliases in an excel file I created using arcpy.TableToExcel.  I've specified "ALIAS" but it seems to ignore it.  The field aliases are set within the database.  Can anyone help me with what I'm doing wrong?  Or give me another method?

Thanks,

Brandon

import arcpy
defeatureclasspath = r"C:\Users\bkeinath\AppData\Roaming\ESRI\Desktop10.2\ArcCatalog\PGISA2_gisadm.sde\GISADM.DistributedEnergy"
arcpy.MakeQueryTable_management(defeatureclasspath,\
                                "CompletedProjectView",\
                                "USE_KEY_FIELDS",\
                                "OBJECTID",\
                                "CUSTOMERLASTNAME; ACCOUNTNUMBER; PROJECTIDENTIFIER; SYSTEMSIZE; CUSTOMERMAILINGADDRESS; PUCPREID; PUCFINALID; APPLICATIONDATE; ACTUALONLINEDATE")


#Create excel file from CompletedProjectView
arcpy.TableToExcel_conversion("CompletedProjectView", r"E:\temp\CompletedProjects.xls", "ALIAS")

[WesMiller]

Table To Excel—Help | ArcGIS for Desktop  I haven't tested but it says it's a boolean so i would try using TRUE

[BrandonKeinath1]

Hi Wes,

Thanks for the reply.  When I use "TRUE" in place of "ALIAS" it acts as if it is ignoring the parameter and using the default.  The field names still show up in the .xls file as the field name rather than the alias defined in the database.

Thanks,

Brandon

[RandyBurton]

Brandon,

Your line for TableToExcel looks correct  using "ALIAS".

I believe the MakeQueryTable_management needs the aliases to be defined in the in_field parameter, such as:

[[Field, {Alias}],...,...]

or in your case, such as:

[["CUSTOMERLASTNAME", 'Last Name of Customer'],["ACCOUNTNUMBER", 'Customer Account'], .... ,["ACTUALONLINEDATE", 'Online Date']]

[BrandonKeinath1]

Hi Randy,

Thanks for the help.  Unfortunately when I make the change you suggest to the in_field parameter I get this error

RuntimeError: Underlying DBMS error [ORA-00923: FROM keyword not found where exp
ected
] [GISADM.DistributedEnergy][STATE_ID = 0]
Failed to execute (TableToExcel).

Here is my updated code

arcpy.MakeQueryTable_management(defeatureclasspath,\
                                "CompletedProjectView",\
                                "USE_KEY_FIELDS",\
                                "OBJECTID",\
                                [["CUSTOMERLASTNAME", 'Last Name of Customer'],["ACCOUNTNUMBER", 'Customer Account']])

Any other thoughts?

Thanks,

Brandon

[RandyBurton]

I did some further experiments with your original code. My version of Python didn't like the slashes.  I also think that if you are using the OBJECTID field, it should be included in your field list. It hung on me without it. In my experiment, it did pull the alias from the feature class table.

Either:

arcpy.MakeQueryTable_management(defeatureclasspath, 
                                "CompletedProjectView",
                                "USE_KEY_FIELDS",
                                "OBJECTID",
                                "OBJECTID; CUSTOMERLASTNAME; PROJECTIDENTIFIER")

Or:

arcpy.MakeQueryTable_management(defeatureclasspath, 
                                "CompletedProjectView",
                                "NO_KEY_FIELD",
                                "",
                                "CUSTOMERLASTNAME;PROJECTIDENTIFIER")

arcpy.TableToExcel_conversion("CompletedProjectView", r"path\to\excel.xls", "ALIAS")

[BrandonKeinath1]

Hi Randy,

Thanks for doing some more testing.  Unfortunately neither of the options changed the result; I'm still not getting the field aliases.  Which python version are you running?  ArcMap?  I'm using Python 2.7 and ArcMap 10.2.1

Thanks,

Brandon

[RandyBurton]

Comment out the TableToExcel portion so that the MakeQueryTable section can be checked.  Add the following code after MakeQueryTable to see if it is selecting the proper number of rows and the correct fields.

# Print the total rows
print arcpy.GetCount_management("CompletedProjectView")

# Print the fields
Fields = arcpy.ListFields("CompletedProjectView")
for field in Fields:
    print field.name

[BrandonKeinath1]

Hi Randy,

Looks like that part is working.  It found 1 as I would expect.

#Create query table to select only those projects that are "Project Completed"
arcpy.MakeQueryTable_management(defeatureclasspath,
                                "CompletedProjectView",
                                "NO_KEY_FIELD",
                                "",
                                #[["CUSTOMERLASTNAME", 'Last Name of Customer'],["ACCOUNTNUMBER", 'Customer Account']],
                                "CUSTOMERLASTNAME; ACCOUNTNUMBER; PROJECTIDENTIFIER; SYSTEMSIZE; CUSTOMERMAILINGADDRESS; PUCPREID; PUCFINALID; APPLICATIONDATE; ACTUALONLINEDATE",
                                "APPLICATIONSTAGE = 'Project Completed'")


# Print the total rows
print arcpy.GetCount_management("CompletedProjectView")




# Print the fields
Fields = arcpy.ListFields("CompletedProjectView")
for field in Fields:
    print field.name

1
CUSTOMERLASTNAME
ACCOUNTNUMBER
PROJECTIDENTIFIER
SYSTEMSIZE
CUSTOMERMAILINGADDRESS
PUCPREID
PUCFINALID
APPLICATIONDATE
ACTUALONLINEDATE

[RandyBurton · Accepted solution]

Next, check the TableToExcel portion by using the table directly.  This would pull all rows/fields into the spreadsheet, but hopefully it would not exceed Excel's limits..  If it is not using aliases, then I would suggest using ArcCatalog to look at your table's properties, just to confirm the fields have aliases properly defined.

arcpy.TableToExcel_conversion(defeatureclasspath, r"E:\temp\CompletedProjects.xls", "ALIAS")

Also, as a note, when I tried using the [[Field, {Alias}],...,...] option in MakeQueryTable, the TableToExcel function gave an error that the aliased field didn't exist.  Perhaps a bug?  My versions are the same as yours; but I am using a file geodatabase; you might be using a server version.