topic Re: How do I specify field aliases for arcpy.TableToExcel? in Python Questions

How do I specify field aliases for arcpy.TableToExcel?

BrandonKeinath1 — Sat, 11 Dec 2021 10:47:00 GMT

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

Re: How do I specify field aliases for arcpy.TableToExcel?

WesMiller — Tue, 22 Sep 2015 20:57:01 GMT

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

Re: How do I specify field aliases for arcpy.TableToExcel?

RandyBurton — Tue, 22 Sep 2015 22:45:02 GMT

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']]

Re: How do I specify field aliases for arcpy.TableToExcel?

BrandonKeinath1 — Sat, 11 Dec 2021 10:47:03 GMT

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

Re: How do I specify field aliases for arcpy.TableToExcel?

BrandonKeinath1 — Wed, 23 Sep 2015 15:29:23 GMT

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

Re: How do I specify field aliases for arcpy.TableToExcel?

RandyBurton — Sat, 11 Dec 2021 10:47:06 GMT

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

Re: How do I specify field aliases for arcpy.TableToExcel?

BrandonKeinath1 — Wed, 23 Sep 2015 18:17:47 GMT

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

Re: How do I specify field aliases for arcpy.TableToExcel?

RandyBurton — Sat, 11 Dec 2021 10:47:09 GMT

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

Re: How do I specify field aliases for arcpy.TableToExcel?

BrandonKeinath1 — Sat, 11 Dec 2021 10:47:12 GMT

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

Re: How do I specify field aliases for arcpy.TableToExcel?

RandyBurton — Wed, 23 Sep 2015 20:09:17 GMT

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.

Re: How do I specify field aliases for arcpy.TableToExcel?

BrandonKeinath1 — Wed, 23 Sep 2015 20:26:58 GMT

Hi Randy,

When I ran the TableToExcel tool on the feature class it said it couldn't open the feature class. I then took your suggestion about the file geotabase and after exporting it to a new feature class in a .gdb the excel file contains the aliases. Woohoo! Thank you for your help.

We use an Oracle SDE. I don't see anything in the esri documentation about this being a known limitation so I'll submit a ticket to see if there is something special I'm missing. I'll try to post back here for others using SDE. For now my workflow will be to create a new file geodatabase, import the data, and execute my code on this new feature class.

Best,

Brandon