Delete column before exporting to excel

5788
16
Jump to solution
04-29-2016 09:14 AM
CCWeedcontrol
Regular Contributor

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)
0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor
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.

View solution in original post

16 Replies
DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos
CCWeedcontrol
Regular Contributor

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.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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
Regular Contributor

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

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor
JoshuaBixby
MVP Esteemed Contributor

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 SequenceMeaningNotes
\newlineIgnored
\\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.

JoshuaBixby
MVP Esteemed Contributor

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.

DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos
CCWeedcontrol
Regular Contributor

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.

0 Kudos