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; } } }

Table to Excel

JohnByrne

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

Finde mehr Beiträge mit dem Tag

Accepted answers

All comments

JohnByrne

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

DanPatterson_Retired

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

JoshuaChisholm

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):
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?

JohnByrne

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)

JohnByrne

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

JoshuaChisholm

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!

JohnByrne

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

TableToExcelJB.zip

JoshuaChisholm

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!

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

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