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.
What if.... You were to perform a (arcpy) table to excel followed by (straight python) xls to csv ....
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.
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.
Why not use Table To Table—Conversion toolbox | ArcGIS Desktop which can also export csv.
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", "")
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.
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!
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
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.