topic Re: Table to Excel in Python Questions

Table to Excel

JohnByrne — Wed, 18 Jun 2014 14:38:23 GMT

Hi,

I am currently trying to use the Table to Excel script to produce a table of a query that I have created. It returns all the fields not just the selected one. Any ideas? I am sure it is simple

Thanks

John

Re: Table to Excel

JoshuaChisholm — Wed, 18 Jun 2014 14:59:17 GMT

Hello John,

As far as I understand it, the Table To Excel tool will export selected records (not selected fields) to an excel table. If you only want selected field in the excel table, I think you would have to use the Make Table View tool before the Table To Excel tool. You could use the "field_info" parameter to pick out your desired fields.

Hope that helps!

Re: Table to Excel

JohnByrne — Wed, 18 Jun 2014 15:07:18 GMT

Hi Joshua,

Thanks for replying, that certainly looks promising. I am actually trying to export the table based on a query I have already ran and linked to Data Driven Pages. How would I refernce that in the Make Table View? I am new to this so please excuse the silly question.

I have linked my current attempt at the script, which works for the initial part of what I am trying to achieve, just failing at the output.
I have attached my script so you can maybe see it easier.
Thanks

Re: Table to Excel

JoshuaChisholm — Sun, 12 Dec 2021 05:21:21 GMT

Try this:

def table_to_excel(in_table, output, use_field_alias=False, use_domain_desc=False):
    desiredFields=["Field1","Field3","Field8"] #desired fields for excel output

    # Get the fields from the input
    fields= arcpy.ListFields(in_table)

    # Create a fieldinfo object
    fieldinfo = arcpy.FieldInfo()

    # Iterate through the fields and set them to fieldinfo
    for field in fields:
        if field in desiredFields:
            fieldinfo.addField(field.name, field.name, "VISIBLE", "")

    # The created crime_view layer will have fields as set in fieldinfo object
    arcpy.MakeTableView_management(in_table, "table_view", "", "", fieldinfo)

    # To persist the layer on disk make a copy of the view
    arcpy.TableToExcel_conversion("table_view", output)

Note that I couldn't fully test it because I'm still on 10.1.

Let me know if it works!

Re: Table to Excel

JohnByrne — Thu, 19 Jun 2014 06:57:03 GMT

It seems to work, unfortunately I don't know how to define the output

Re: Table to Excel

JohnByrne — Thu, 19 Jun 2014 07:37:27 GMT

This a view of the amended code, as per your advice

import arcpy
import os
import sys
import xlwt
import datetime
mxd=arcpy.mapping.MapDocument("current")
BUSID_TEMP=arcpy.GetParameterAsText(0)
LayersDF=arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
selLyr=arcpy.mapping.ListLayers(mxd, "FP_sample", LayersDF)[0]
for elm in arcpy.mapping.ListLayoutElements(mxd):
    if elm.name=="GridTable": GridTable = elm
ddp=mxd.dataDrivenPages
arcpy.AddMessage(BUSID_TEMP)
pageID=mxd.dataDrivenPages.getPageIDFromName(str(BUSID_TEMP))
mxd.dataDrivenPages.currentPageID=pageID
#excel export part of the script

def table_to_excel(FP_sample, output, use_field_alias=False, use_domain_desc=False):
    desiredFields=["Field1","Field3","Field4"] #desired fields for excel output

    # Get the fields from the input
    fields= arcpy.ListFields(in_table)

    # Create a fieldinfo object
    fieldinfo = arcpy.FieldInfo()

    # Iterate through the fields and set them to fieldinfo
    for field in fields:
        if field in desiredFields:
            fieldinfo.addField(field.name, field.name, "VISIBLE", "")

    # The created view layer will have fields as set in fieldinfo object
    arcpy.MakeTableView_management(FP_sample, "table_view", "", "", fieldinfo)

    # To persist the layer on disk make a copy of the view
    arcpy.TableToExcel_conversion("table_view", pop.xls)

Re: Table to Excel

JoshuaChisholm — Thu, 19 Jun 2014 13:45:14 GMT

Hello John,

I'm sorry if I was unclear, but I have some corrections for your script:

In the line desiredFields=["Field1","Field3","Field4"], you should specify the fields by their name (not their position or alias). This line should be something more like desiredFields=["CityName","Pop","Province"] or whatever fields you want to include. I'm sorry I was unclear on that line.

In the script you posted (TableToExcelJB.py), the defined method "table_to_excel" takes 2 inputs ("in_table", "output") and 2 optional inputs ("use_field_alias", "use_domain_desc"). This is all stated in the line def table_to_excel(in_table, output, use_field_alias=False, use_domain_desc=False):

table_to_excel(arcpy.GetParameterAsText(0),arcpy.GetParameterAsText(1),arcpy.GetParameter(2),arcpy.GetParameter(3))

output=r"C:\Path\To\pop.xls"

In further looking at the script (TableToExcelJB.py) you provided, it looks like it was designed to handle your data is a special way, reformatting some data as it runs. I'm worried a more spimlified version of this script (as I provided) will not handle your data in the same way the TableToExcelJB.py script does. Is this a problem?

Re: Table to Excel

DanPatterson_Retired — Thu, 19 Jun 2014 13:50:48 GMT

Just a thought, if the table is in ArcMap and you hide the fields you don't want, then export the file...do you get the results you want? If you are working with a file on disk, all fields will get exported

Re: Table to Excel

JohnByrne — Thu, 19 Jun 2014 14:11:54 GMT

Hi Dan,

I am going to have a play with your advice now. Essentially my problems are (many but will simplify)
1. I need to create datadriven pages from a specific query, which I have managed to do at the minute.
2. The results of this query needs to be also shown in a seperate table hence why I am trying to export it to Excel. We have had a look at using Production mapping but there are too many varied results between queries to maintain a consistent look.
3. I am unsure, due to my inexperience if I should be changing the input and out when it appears in the script to get the results.

Hope that makes sense and thanks for your help