nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Delete column before exporting to excel

CCWeedcontrol

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)

Najděte více příspěvků označených jako

Accepted answers

JoshuaBixby

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.

All comments

CCWeedcontrol

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.

DanPatterson_Retired

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

blake.terhune

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
]

WesMiller

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

CCWeedcontrol

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.

DanPatterson_Retired

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

JoshuaBixby

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.

JoshuaBixby

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
`<SPAN class="pre">\newline</SPAN>`	Ignored
`<SPAN class="pre">\\</SPAN>`	Backslash (`<SPAN class="pre">\</SPAN>`)
`<SPAN class="pre">\'</SPAN>`	Single quote (`<SPAN class="pre">'</SPAN>`)
`<SPAN class="pre">\"</SPAN>`	Double quote (`<SPAN class="pre">"</SPAN>`)
`<SPAN class="pre">....</SPAN>`	....

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.

DanPatterson_Retired

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

CCWeedcontrol

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

DanPatterson_Retired

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.

CCWeedcontrol

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.

DanPatterson_Retired

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

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.