Monday, February 4, 2013

Convert Table or Feature class to CSV (10.1)

Sometimes you need to export data from a feature class or table to a CSV file.  CSV stands for comma separated values.  Wikipedia defines a CSV file as such:

A comma-separated values (CSV) file stores tabular data (numbers and text) in plain-text form. Plain text means that the file is a sequence of characters, with no data that has to be interpreted instead, as binary numbers. A CSV file consists of any number of records, separated by line breaks of some kind; each record consists of fields, separated by some other character or string, most commonly a literal comma or tab. Usually, all records have an identical sequence of fields.

To create a CSV file at 10.1, you need to strip out certain field types: Geometry, Blob and Raster because the file format only supports plain text.  This can be done by doing the following:

 fieldnames = [ for f in desc.fields if f.type not in ["Geometry", "Raster", "Blob"]]

Next you need to write the field names and rows to a file.  This is extremely easy with the CSV library in python.  Documentation about this standard library can be found here.

def get_rows(data_set, fields):
   with da.SearchCursor(data_set, fields) as cursor:
      for row in cursor:
         yield row
if __name__ == "__main__":
   data_set = arcpy.GetParameterAsText(0) # feature class/Table
   output = arcpy.GetParameterAsText(1) # csv file
   desc = arcpy.Describe(data_set)
   fieldnames = [ for f in desc.fields if f.type not in ["Geometry", "Raster", "Blob"]]
   rows = get_rows(data_set, fieldnames)
   with open(output,'wb') as out_file:
      out_writer = csv.writer(out_file)
      for row in rows:

Here we have a function called get_rows() which takes two parameters.  The first is the data_set, which can be a table or feature class.  The next is the fields.  At 10.1, you must define your fields unlike the 10.0 cursor objects.  The function uses the yield, which is a generator.  Basically the code only runs if the function is called in a loop (I know that's not 100% correct), but here is a better explanation.  Using the CSV module in python, we can then easily write out each row within the rows generator object.

FYI, this is written so you can put this code into a script and toolbox for ArcGIS 10.1.  Just add the imports.



james said...

Nice, but for tables there is a much easy method:

arr = arcpy.da.TableToNumPyArray('tablename', ('fieldnames'))

numpy.savetxt('output_path.csv', arr, delimited = ",")

Andrew said...

Good note!