Select to view content in your preferred language

Modify CSV writer script to define list of columns by field name

4618
21
Jump to solution
11-26-2021 02:27 AM
David_Brooks
MVP Regular Contributor

I have a script that writes a CSV based on fields from a feature class. Currently, the columns are defined by a field list, and a Search cursor loops through each item in the list to write to the CSV. 

The list is defined by the positions of each field in the list. However, if the field order ever changes on the Feature Class, this list goes to pot. 

How do I rewrite it so that I can define fld_list_final as a list of strings that match the actual field names, instead of their positions in the list?

So, for example, instead of the list being fn[5], fn[6] etc, i want to be able to define it as 'TreeRef', 'Species', etc

 

 

   # Create CSV of data for schedule if there are veteran trees
   import csv
   import os
   def tableToCSV(input_tbl, csv_filepath):
       fld_list = arcpy.ListFields(input_tbl)
       fn = [fld.name for fld in fld_list]
       fld_list_final = [fn[3], fn[4], fn[5], fn[6], fn[7], fn[8], fn[38], fn[41], fn[22], fn[23], fn[24], fn[25], fn[26], fn[27], fn[28], fn[29], fn[30], fn[36], fn[40], fn[39], fn[33], fn[37]]
       with open(csv_filepath, 'w', newline='') as csv_file:
           writer = csv.writer(csv_file)
           writer.writerow(fld_list_final)
           with arcpy.da.SearchCursor(input_tbl, fld_list_final) as cursor:
               for row in cursor:
                   writer.writerow(row)
       csv_file.close()
   out_csv = csv_path+"\\"+(os.path.basename(fc_pnt))+".csv"
   tableToCSV(fc_pnt, out_csv)
   arcpy.AddMessage("CSV Generated.")

 

 

 


David
..Maps with no limits..
Tags (3)
0 Kudos
1 Solution

Accepted Solutions
DanPatterson
MVP Esteemed Contributor

 

import arcpy
import numpy as np
tbl = r"C:\Arc_projects\Test_29\Test_29.gdb\sq2"
flds = arcpy.ListFields(tbl)
fnames = [f.name for f in flds]
fnames
['OBJECTID', 'Shape', 'ids', 'CENTROID_X', 'CENTROID_Y', 'INSIDE_X', 'INSIDE_Y', 'PART_COUNT', 'PNT_COUNT', 'Sort_', 'Shape_Length', 'Shape_Area', 'Sort2_']

keepers = ['Sort2_', 'PNT_COUNT', 'PART_COUNT']
arr = arcpy.da.TableToNumPyArray(tbl, keepers)

# -- def time ----------------------------------------
def save_txt(a, name="arr.txt", sep=", ", dt_hdr=True):
    """Save a NumPy structured/recarray to text.

    Parameters
    ----------
    a : array
        Input array.
    name : filename
        Output filename and path otherwise save to script folder.
    sep : separator
        Column separator, include a space if needed.
    dt_hdr : boolean
        If True, add dtype names to the header of the file.
    """
    a_names = ", ".join(a.dtype.names)
    hdr = ["", a_names][dt_hdr]  # use "" or names from input array
    s = np.array(a.tolist(), dtype=np.unicode_)
    widths = [max([len(i) for i in s[:, j]])
              for j in range(s.shape[1])]
    frmt = sep.join(["%{}s".format(i) for i in widths])
    np.savetxt(name, a, fmt=frmt, header=hdr, comments="")
    print("\nFile saved...")
# -----------------------------------------------------------

# now save it    
save_txt(arr, name="c:/temp/test.csv", sep=", ", dt_hdr=True)

 

results

 

Sort2_, PNT_COUNT, PART_COUNT
0, 11.0, 1.0
1,  8.0, 1.0
2,  6.0, 1.0
3,  5.0, 1.0
4, 10.0, 1.0
5, 10.0, 1.0
6,  5.0, 1.0

 


... sort of retired...

View solution in original post

21 Replies
DanPatterson
MVP Esteemed Contributor

perhaps the suggestion of using a dictionary to alter field order when exporting to csv can be exploited 

Solved: Changing Column Order with Python Export CSV - Esri Community


... sort of retired...
jcarlson
MVP Esteemed Contributor

If you have a set list of field names that you want in your output, you can define those separately, then filter your field list with it. Try this? I don't have a dataset handy to test it on.

 

 

# Create CSV of data for schedule if there are veteran trees
import csv
import os

def tableToCSV(input_tbl, csv_filepath):
    fld_list = arcpy.ListFields(input_tbl)
    keepers = ['some', 'list', 'of', 'field', 'names']
    fn = [fld.name for fld in fld_list if fld.name in keepers]
    with open(csv_filepath, 'w', newline='') as csv_file:
        writer = csv.writer(csv_file)
        writer.writerow(fn)
        with arcpy.da.SearchCursor(input_tbl, fn) as cursor:
            for row in cursor:
                writer.writerow(row)
    csv_file.close()

out_csv = f"{csv_path}\\{os.path.basename(fc_pnt)}.csv"
tableToCSV(fc_pnt, out_csv)
arcpy.AddMessage("CSV Generated.")

 

 

 

- Josh Carlson
Kendall County GIS
David_Brooks
MVP Regular Contributor

@jcarlson thanks for the re-write, but I get 

TypeError: list indices must be integers or slices, not str

....which i think is because Im not extracting items from a list by index, rather by string values. Any ideas?


David
..Maps with no limits..
0 Kudos
jcarlson
MVP Esteemed Contributor

What line is that error pointing you to? The change I made shouldn't really matter with respect to list indices.

jcarlson_1-1637963182731.png

 

- Josh Carlson
Kendall County GIS
David_Brooks
MVP Regular Contributor

@jcarlson i've rewritten, and the csv now exports. however, the order of fields doesn't match the order i've written them in the keepers list


David
..Maps with no limits..
0 Kudos
jcarlson
MVP Esteemed Contributor

Looks like I missed quite a bit over the weekend! It looks like you've got your solution, but I would also like to throw in an ArcGIS Python API solution for you, in case you'd like to test it.

from arcgis.features import GeoAccessor

out_fields = ['your', 'output', 'fields', 'in', 'order']
df = GeoAccessor.from_featureclass('path/to/featureclass')

df[[out_fields]].to_csv('path/to/export.csv')
- Josh Carlson
Kendall County GIS
0 Kudos
DanPatterson
MVP Esteemed Contributor

 

import arcpy
import numpy as np
tbl = r"C:\Arc_projects\Test_29\Test_29.gdb\sq2"
flds = arcpy.ListFields(tbl)
fnames = [f.name for f in flds]
fnames
['OBJECTID', 'Shape', 'ids', 'CENTROID_X', 'CENTROID_Y', 'INSIDE_X', 'INSIDE_Y', 'PART_COUNT', 'PNT_COUNT', 'Sort_', 'Shape_Length', 'Shape_Area', 'Sort2_']

keepers = ['Sort2_', 'PNT_COUNT', 'PART_COUNT']
arr = arcpy.da.TableToNumPyArray(tbl, keepers)

# -- def time ----------------------------------------
def save_txt(a, name="arr.txt", sep=", ", dt_hdr=True):
    """Save a NumPy structured/recarray to text.

    Parameters
    ----------
    a : array
        Input array.
    name : filename
        Output filename and path otherwise save to script folder.
    sep : separator
        Column separator, include a space if needed.
    dt_hdr : boolean
        If True, add dtype names to the header of the file.
    """
    a_names = ", ".join(a.dtype.names)
    hdr = ["", a_names][dt_hdr]  # use "" or names from input array
    s = np.array(a.tolist(), dtype=np.unicode_)
    widths = [max([len(i) for i in s[:, j]])
              for j in range(s.shape[1])]
    frmt = sep.join(["%{}s".format(i) for i in widths])
    np.savetxt(name, a, fmt=frmt, header=hdr, comments="")
    print("\nFile saved...")
# -----------------------------------------------------------

# now save it    
save_txt(arr, name="c:/temp/test.csv", sep=", ", dt_hdr=True)

 

results

 

Sort2_, PNT_COUNT, PART_COUNT
0, 11.0, 1.0
1,  8.0, 1.0
2,  6.0, 1.0
3,  5.0, 1.0
4, 10.0, 1.0
5, 10.0, 1.0
6,  5.0, 1.0

 


... sort of retired...
David_Brooks
MVP Regular Contributor

tried this code but get the error

Traceback (most recent call last):
  File "C:\Users\DAVID\Desktop\BS5837_TreePoint_v1.2.4.py", line 100, in <module>
    np.savetxt(name, a, fmt=frmt, header=hdr, comments="")
  File "C:\Users\DAVID\Desktop\BS5837_TreePoint_v1.2.4.py", line 92, in save_txt
    If True, add dtype names to the header of the file.
NameError: name 'np' is not defined

David
..Maps with no limits..
0 Kudos
DanPatterson
MVP Esteemed Contributor

Ahhh, forgot the old

import numpy as np

It is assumed in numpy world and oft forgotten since my IDE automatically loads my needed packages 😉


... sort of retired...
0 Kudos