topic Re: Delete column before exporting to excel in Python Questions

Delete column before exporting to excel

CCWeedcontrol — Sun, 12 Dec 2021 03:45:41 GMT

I have a script that creates a excel file but i need it to delete the "OID" field prior to exporting to excel, is this possible or does it have to be done after the export?

I have tried the following, it runs fine and i don't get error.

# Process: Make Feature Layer (2)
arcpy.MakeFeatureLayer_management("In_memory\Blah", "In_memory\Blah3")

#fields = arcpy.ListFields("In_memory\Blah3", "OID")
#if len(fields) != 1:
#    arcpy.DeleteField_management("OID")
    
fields = [f.name for f in arcpy.ListFields("In_memory\Blah3")]

for i,f in enumerate(fields):
    if f == 'OID':
        del fields

# Process: Table Select
arcpy.TableToExcel_conversion("In_memory\Blah3",Listing_xls)

Re: Delete column before exporting to excel

DanPatterson_Retired — Fri, 29 Apr 2016 16:16:05 GMT

If it works and doesn't export the field, I am not sure what the question is

Re: Delete column before exporting to excel

CCWeedcontrol — Fri, 29 Apr 2016 16:43:41 GMT

I am trying to delete the "OID" column prior to exporting the layers attribute to excel, but i am not sure if it's possible during the export or after. The code i posted runs but the "OID" does not get deleted.

Thanks.

Re: Delete column before exporting to excel

DanPatterson_Retired — Fri, 29 Apr 2016 17:05:00 GMT

given it is OID, that suggests a shapefile which requires a minimum of 3 fields to exist, perhaps that is limiting the deletion. A sad workaround would be to export the table to another standalone table, delete the field then export to excel.. I doubt much would be gained, but it might work. deleting in excel would be a quicker option...leaving it alone would cause no harm.

Re: Delete column before exporting to excel

CCWeedcontrol — Fri, 29 Apr 2016 18:18:59 GMT

Ya i could leave it, doesn't hurt but just wondered if it was possible and if it was how to do it.

Re: Delete column before exporting to excel

DanPatterson_Retired — Fri, 29 Apr 2016 19:19:54 GMT

You could use

XlsxWriter 0.8.5 : Python Package Index

openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 2.3.5 documentation

if you want to do imports of packages or have newer versions of python

Darren gave an example in this thread https://community.esri.com/message/604114#comment-604114

Re: Delete column before exporting to excel

JoshuaBixby — Sun, 01 May 2016 16:28:40 GMT

On a side note, if you haven't run into problems already, you likely will with how paths are being defined. In the posted script, in-memory paths are being defined using single backslashes. In Python, backslashes are escape characters:

2.4.1. String literals ¶

....

In plain English: String literals can be enclosed in matching single quotes (') or double quotes ("). They can also be enclosed in matching groups of three single or double quotes (these are generally referred to as triple-quoted strings). The backslash (\) character is used to escape characters that otherwise have a special meaning, such as newline, backslash itself, or the quote character. String literals may optionally be prefixed with a letter 'r' or 'R'; such strings are called raw strings and use different rules for interpreting backslash escape sequences. A prefix of 'u' or 'U' makes the string a Unicode string. Unicode strings use the Unicode character set as defined by the Unicode Consortium and ISO 10646. Some additional escape sequences, described below, are available in Unicode strings. A prefix of 'b' or 'B' is ignored in Python 2; it indicates that the literal should become a bytes literal in Python 3 (e.g. when code is automatically converted with 2to3). A'u' or 'b' prefix may be followed by an 'r' prefix.

In triple-quoted strings, unescaped newlines and quotes are allowed (and are retained), except that three unescaped quotes in a row terminate the string. (A “quote” is the character used to open the string, i.e. either ' or ".)

Unless an 'r' or 'R' prefix is present, escape sequences in strings are interpreted according to rules similar to those used by Standard C. The recognized escape sequences are:

Escape Sequence	Meaning	Notes
`\newline`	Ignored
`\\`	Backslash (`\`)
`\'`	Single quote (`'`)
`\"`	Double quote (`"`)
`....`	....

Also, I don't think the script is doing what you think it is doing with regard to the Make Feature Layer tool. The second argument of the Make Feature Layer tool is the "name of the feature layer to be created," just a name, not a path. On Line 02, you aren't actually storing the feature layer in-memory, you are simply creating a feature layer with "In_memory" as part of its name. Although taking such an approach works, it is bound to cause confusion using names that resemble paths when in fact they aren't paths.

Re: Delete column before exporting to excel

JoshuaBixby — Sun, 12 Dec 2021 03:45:44 GMT

On the topic of exporting to Excel, or really exporting to anything, ArcGIS is very stubborn about including ObjectID fields. I have tried numerous ways to delete or hide the ObjectID field before exporting, but the tools either ignore the changes or throw an error. One approach I have found uses Make Table Query, but it only works with enterprise and file geodatabases (and personal geodatabases with ArcMap/ArcCatalog) since the tool only works with those types of geodatabases.

>>> fc = # feature class or table in enterprise or file geodatabase
>>> desc = arcpy.Describe(fc)
>>> flds = [fld.name for fld in desc.fields]
>>> if desc.hasOID:
>>>    flds.remove(desc.OIDFieldName)
>>> arcpy.MakeQueryTable_management(fc, "tmp_view", "NO_KEY_FIELD", "", flds)
<Result 'tmp_view'>
>>> arcpy.TableToExcel_conversion("tmp_view", r"C:\tmp\fc_dump.xls")
<Result 'C:\\tmp\\fc_dump.xls'>
>>> arcpy.Delete_management("tmp_view")
<Result 'true'>

EDIT: Added a comment regarding personal geodatabase support when using ArcMap/ArcCatalog.

Re: Delete column before exporting to excel

DanPatterson_Retired — Sun, 01 May 2016 21:13:53 GMT

Of course, none of us have asked why the H you would want to export it to excel in the first place

Re: Delete column before exporting to excel

CCWeedcontrol — Sun, 12 Dec 2021 03:45:47 GMT

Joshua thanks for the great info. I have made adjustments to move the feature layer to a gdb. The code you provided did remove the OID fields but i am now suck with Shape_Length and Shape_Area fields, what would be the correct syntax to remove the OID, Shape_Length and Shape_Area fields?

I tired the following but got an error on line 7.

if desc.hasShape_Length:

AttributeError: DescribeData: Method hasShape_Length does not exist

fc = "C:/Temp/Default.gdb/Blah"  
desc = arcpy.Describe(fc)  
flds = [fld.name for fld in desc.fields]
if desc.hasOID:  
    flds.remove(desc.OIDFieldName)
    
if desc.hasShape_Length:  
    flds.remove(desc.Shape_LengthFieldName)
    
if desc.hasShape_Area:  
    flds.remove(desc.Shape_AreaFieldName) 
arcpy.MakeQueryTable_management(fc, "tmp_view", "NO_KEY_FIELD", "", flds)  
#Result 'tmp_view'  
arcpy.TableToExcel_conversion("tmp_view", r"C:/Temp/fc_dump.xls")  
#Result 'C:/tmp/fc_dump.xls'
arcpy.Delete_management("tmp_view")

Dan,

This script generates a mailing list for notifications and the office software uses Excel file to generate mailing stickers. once I generate the list i have to open up excel and delete the OID, Shape_Length and Shape_Area fields.

Thanks.

Re: Delete column before exporting to excel

WesMiller — Mon, 02 May 2016 16:21:09 GMT

If you are using mail merge it will hurt nothing to leave the OID field in place.

Re: Delete column before exporting to excel

BlakeTerhune — Sun, 12 Dec 2021 03:45:49 GMT

Something like this might work.

# Get list of usable field names (exclude OID and SHAPE fields)
fieldNames = [
    f.name for f in arcpy.ListFields(inputTable)
    if f.type != "Geometry" and f.editable == True
]

Re: Delete column before exporting to excel

DanPatterson_Retired — Mon, 02 May 2016 17:10:51 GMT

so you are saying switching to a gdb increases the number of fields you need to delete?? excel macro time

Re: Delete column before exporting to excel

JoshuaBixby — Sun, 12 Dec 2021 03:45:52 GMT

fc = "C:/Temp/Default.gdb/Blah"   
desc = arcpy.Describe(fc)
flds = [fld.name for fld in desc.fields]
flds_remove = [desc.OIDFieldName,
              desc.shapeFieldName,
              desc.areaFieldName,
              desc.lengthFieldName]
for fld in flds_remove:
    flds.remove(fld)
arcpy.MakeQueryTable_management(fc, "tmp_view", "NO_KEY_FIELD", "", flds)   
#Result 'tmp_view'   
arcpy.TableToExcel_conversion("tmp_view", r"C:/Temp/fc_dump.xls")   
#Result 'C:/tmp/fc_dump.xls' 
arcpy.Delete_management("tmp_view")

If you want it to work with tables as well as feature classes, you would need to add some code to check for a shape field before adding it to flds_remove; otherwise, an error will be generated.

Re: Delete column before exporting to excel

CCWeedcontrol — Tue, 03 May 2016 21:16:56 GMT

Yes when changing to a geodatabase and exporting it exports the area and shape fields.

I was using a macro but i got tired of having to open the export and run the macro.

Re: Delete column before exporting to excel

CCWeedcontrol — Tue, 03 May 2016 21:31:00 GMT

Worked. Thanks.

Re: Delete column before exporting to excel

JoshuaBixby — Wed, 04 May 2016 16:43:16 GMT

If my or someone else's answer addressed your issue, please mark it correct so others know it has been answered.