topic Re: How to convert a feature class attribute table to csv using arcpy? in Python Questions

How to convert a feature class attribute table to csv using arcpy?

JustinBridwell2 — Tue, 05 Nov 2019 15:44:26 GMT

Is there an arcpy method/mod that allows for the exporting of a feature class's attribute table to a csv file? I see arcpy.ConvertTableToCsvFile_roads, but that requires a special license. I am currently trying an `ogr2ogr` solution, but I am working within an ArcMap 10.5/Python 2.7 env and thought there would be something I could use.

Re: How to convert a feature class attribute table to csv using arcpy?

JoeBorgione — Tue, 05 Nov 2019 16:25:57 GMT

What if.... You were to perform a (arcpy) table to excel followed by (straight python) xls to csv ....

Re: How to convert a feature class attribute table to csv using arcpy?

RandyBurton — Sun, 12 Dec 2021 03:37:34 GMT

Here's one possibility:

import arcpy
import csv

fc = r'C:\Path\to\file.gdb\feature'

# field names into list (you could use arcpy.Describe to get/select field names)
fields = ['Field1','Field2','FieldN'] # list of field names

sheetname = 'csv_test'
with open('{}.csv'.format(sheetname), 'wb') as outf:
    dw = csv.DictWriter(
        outf,
        quotechar='"',
        fieldnames=fields
    )
    dw.writeheader()
    with arcpy.da.SearchCursor(fc, fields) as rows:
        for row in rows:
            dw.writerow(dict(zip(fields, row)))

print "File written: {}".format(sheetname)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You can use Describe to get information on the feature's fields and use that information to create your field list. If you build a tool using the code above, you could allow the user to select fields. You could also modify the DictWriter to output a tab delimited file.

Re: How to convert a feature class attribute table to csv using arcpy?

JustinBridwell2 — Tue, 05 Nov 2019 17:58:12 GMT

This looks like a viable solution except....my features literally have millions of rows. Using da.SearchCursor() to iterate through each row and write it to a file would probably take a huge amount of time.

Re: How to convert a feature class attribute table to csv using arcpy?

irfanlatif — Wed, 06 Nov 2019 07:34:33 GMT

Why not use Table To Table—Conversion toolbox | ArcGIS Desktop which can also export csv.

Re: How to convert a feature class attribute table to csv using arcpy?

RobertBorchert — Wed, 06 Nov 2019 14:02:25 GMT

import arcpy

# Local variables:
Points = "C:\\01CAD\\BoardWord\\Boardwork.mdb\\Boards\\Points"
table_csv = Points
BoardWord = "C:\\01CAD\\BoardWord"

# Process: Table to Table
arcpy.TableToTable_conversion(Points, BoardWord, "table.csv", "", "Ratio \"Size\" true true false 50 Text 0 0 ,First,#,C:\\01CAD\\BoardWord\\Boardwork.mdb\\Boards\\Points,Ratio,-1,-1", "")

Re: How to convert a feature class attribute table to csv using arcpy?

RobertBorchert — Wed, 06 Nov 2019 14:04:02 GMT

What Ifran writes is the most correct answer. Often I am perplexed that people insist on trying to figure it out the more difficult way.

Re: How to convert a feature class attribute table to csv using arcpy?

DanPatterson_Retired — Sun, 12 Dec 2021 03:37:37 GMT

In the beginning, there was a table

The desire was to produce a csv file... there are lots of ways... here is one

# ---- convert a table or featureclass table to a numpy array

t = tbl_data(in_fc)

t

array([( 1, 2, 19, 0, 'A', 'a', 10, 0.9 ,  0),
       ( 2, 2, 19, 0, 'B', 'a',  8, 0.93,  5),
       ( 3, 1,  4, 0, 'A', 'a',  3, 0.74,  1),
       ( 4, 1,  5, 0, 'B', 'b',  4, 1.06,  7),
       ( 5, 1,  7, 0, 'C', 'b',  7, 1.15, 11),
       ( 6, 1, 30, 0, 'A', 'b',  6, 0.87,  3),
       ( 7, 1, 24, 0, 'A', 'b',  2, 0.88,  4),
       ( 8, 1,  7, 0, 'B', 'a',  8, 0.96,  6),
       ( 9, 1,  7, 0, 'C', 'a',  3, 0.98,  9),
       (10, 1,  4, 0, 'A', 'a',  7, 0.47,  2),
       (11, 1,  6, 0, 'B', 'b',  8, 1.06,  8),
       (12, 1, 13, 0, 'C', 'a',  9, 1.04, 10)],
      dtype=[('OID_', '<i4'), ('X_cent', '<i4'),
             ('Y_cent', '<i4'), ('Curves', '<i4'),
             ('A0', '<U5'), ('A1', '<U5'),
             ('Value_0', '<i4'), ('Area_perim', '<f8'),
             ('srt_a0a1', '<i4')])

# ---- amazing things can be done with arrays‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Now on to simple things.

save_txt(t, name="c:/Temp/arr.csv", sep=", ", dt_hdr=True)

File saved...

# ---- 
OID_, X_cent, Y_cent, Curves, A0, A1, Value_0, Area_perim, srt_a0a1
 1, 2, 19, 0, A, a, 10, 0.9020246711679641,  0
 2, 2, 19, 0, B, a,  8, 0.9285714285714286,  5
 3, 1,  4, 0, A, a,  3, 0.7443162540690755,  1
 4, 1,  5, 0, B, b,  4, 1.0606601717798239,  7
 5, 1,  7, 0, C, b,  7, 1.1538461538461537, 11
 6, 1, 30, 0, A, b,  6,   0.86944295103144,  3
 7, 1, 24, 0, A, b,  2,  0.883142045739337,  4
 8, 1,  7, 0, B, a,  8, 0.9561553109261145,  6
 9, 1,  7, 0, C, a,  3, 0.9750407519379175,  9
10, 1,  4, 0, A, a,  7, 0.4661397467216941,  2
11, 1,  6, 0, B, b,  8, 1.0570527149133586,  8
12, 1, 13, 0, C, a,  9,  1.036191067705632, 10‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Now the code for posterity.

def tbl_data(in_tbl):
    """Pull all editable attributes from a featureclass tables.  During the
    process, <null> values are changed to an appropriate type.

    Parameters
    ----------
    in_tbl : text
        Path to the input featureclass.

    Notes
    -----
    The output objectid and geometry fields are renamed to
    `OID_`, `X_cent`, `Y_cent`, where the latter two are the centroid values.
    """
    flds = ['OID@']
    null_dict, fld_names = _make_nulls_(in_tbl, include_oid=True,
                                        int_null=-999)
    if flds not in fld_names:
        new_names = out_flds = fld_names
    if fld_names[0] == 'OID@':
        out_flds = flds + fld_names[1:]
        new_names = ['OID_', 'X_cent', 'Y_cent'] + out_flds[3:]
    a = arcpy.da.TableToNumPyArray(
            in_tbl, out_flds, skip_nulls=False, null_value=null_dict
            )
    a.dtype.names = new_names
    return np.asarray(a)


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

    Parameters
    ----------
    a : array
        input array
    fname : filename
        output filename and path otherwise save to script folder
    sep : separator
        column separater, include a space if needed
    dt_hdr : boolean
        if True, add dtype names to the header of the file

    """
    a_names = ", ".join(i for i in 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])
    # vals = ", ".join([i[1] for i in a.dtype.descr])
    np.savetxt(name, a, fmt=frmt, header=hdr, comments="")
    print("\nFile saved...")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

And if you don't want to deal with those pesky nulls, you can include

def _make_nulls_(in_fc, include_oid=True, int_null=-999):
    """Return null values for a list of fields objects, excluding objectid
    and geometry related fields.  Throw in whatever else you want.

    Parameters
    ----------
    in_fc : featureclass or featureclass table
        Uses arcpy.ListFields to get a list of featureclass/table fields.
    int_null : integer
        A default to use for integer nulls since there is no ``nan`` equivalent
        Other options include

    >>> np.iinfo(np.int32).min # -2147483648
    >>> np.iinfo(np.int16).min # -32768
    >>> np.iinfo(np.int8).min  # -128

    >>> [i for i in cur.__iter__()]
    >>> [[j if j else -999 for j in i] for i in cur.__iter__() ]
    """
    nulls = {'Double': np.nan, 'Single': np.nan, 'Float': np.nan,
             'Short': int_null, 'SmallInteger': int_null, 'Long': int_null,
             'Integer': int_null, 'String': str(None), 'Text': str(None),
             'Date': np.datetime64('NaT'), 'Geometry': np.nan}
    #
    desc = arcpy.da.Describe(in_fc)
    if desc['dataType'] not in ('FeatureClass', 'Table'):
        print("Only Featureclasses and tables are supported")
        return None, None
    in_flds = desc['fields']
    good = [f for f in in_flds if f.editable and f.type != 'Geometry']
    fld_dict = {f.name: f.type for f in good}
    fld_names = list(fld_dict.keys())
    null_dict = {f: nulls[fld_dict[f]] for f in fld_names}
    # ---- insert the OBJECTID field
    if include_oid and desc['hasOID']:
        oid_name = 'OID@'  # desc['OIDFieldName']
        oi = {oid_name: -999}
        null_dict = dict(list(oi.items()) + list(null_dict.items()))
        fld_names.insert(0, oid_name)
    return null_dict, fld_names‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Failed to mention blindingly fast for very large data sets and you can change the separator, include the field names and blindingly fast.. and no Pandas or Excel and everything is built into Arc. See line 23 in tbl_data … that is one of those arcpy functions!

Re: How to convert a feature class attribute table to csv using arcpy?

DanPatterson_Retired — Sun, 12 Dec 2021 03:37:40 GMT

I did mention nulls...

OID_, Int_nulls, Float_nulls, Text_nulls
 1,    1,  nan,    a
 2, -999,  6.1, None
 3, -999,  2.1, None
 4, -999,  8.1, None
 5, -999, 12.1, None
 6, -999,  4.1, None
 7,    5,  nan,    b
 8,    7,  nan,    d
 9, -999, 10.1, None
10,    3,  nan,    c
11,    9,  nan,    f
12,   11,  nan,    e‍‍‍‍‍‍‍‍‍‍‍‍‍

In the _make_nulls_ script, integer nulls were assigned a value of -999, floating point numbers are assigned 'nan' (not a number, and test fields a text representation of None... not None itself, but None in text form... got it

Re: How to convert a feature class attribute table to csv using arcpy?

irfanlatif — Sun, 12 Dec 2021 03:37:42 GMT

Wow dan, very nice.

Here are my way to export table to csv in case I dont want to use arcpy.TableToTable_conversion.

First iterrat over data set for fields and data.

def header_and_iterator(dataset_name):
       """Returns a list of column names and an iterator over the same columns
       Please see export_to_csv and export to excel for more details """

       data_description = arcpy.Describe(dataset_name)
       fieldnames = [f.name for f in data_description.fields if f.type not in ["Geometry", "Raster", "Blob", "FID"]]
       if  'Shape_Length' in fieldnames:
           fieldnames.remove('Shape_Length')
       if  'Shape_Area' in fieldnames:
           fieldnames.remove('Shape_Area')
       if  'FID' in fieldnames:
           fieldnames.remove('FID')
       if  'OBJECTID' in fieldnames:
           fieldnames.remove('OBJECTID') 
           
    

       def iterator_for_feature():
           cursor = arcpy.SearchCursor(dataset_name)
           row = cursor.next()
           while row:
               yield [getattr(row, col) for col in fieldnames]
               row = cursor.next()
           del row, cursor
       return fieldnames, iterator_for_feature()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

then just export it to csv.

def export_to_unicode_csv(fc, out_Path):
        
        """<datasets, output> Export a feature class to a csv file.
        INPUTS:
        fc: path with name of feature class.
        output(required): Out put path.
        Notes:
        - Unicode data in CSV file is encoded in UTF-8.
               
        """
                
        header_row, rows = header_and_iterator(fc)
        
        output_csv = os.path.join(out_Path, get_BasenamePrefix(fc)+".csv")
        f = open(output_csv, 'wb') 
        #f.write(codecs.BOM_UTF8) # Write Byte Order Mark character so Excelknows this is a UTF-8 file        
        csv_writer = UnicodeWriter(f, dialect='excel', encoding='utf-8-sig')
        if header_row:
            csv_writer.writerow(header_row)
        csv_writer.writerows(rows)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

In the same way I have function to export to xls and xlsx.