Changing Column Order with Python Export CSV

6290
3
Jump to solution
12-04-2013 07:25 AM
MatthewSchmitt1
New Contributor
If I wanted to change the order of columns and export a shapefiles attribute table to csv is there any way to do that with the following script? (I'm still really new at python sorry)

Currently using this script:

import arcpy, csv fc = "E:\HUC2_DATA\HUC02\NewShapes\Result.gdb\HUC02Transect200m15pt" #input feature class rows = arcpy.SearchCursor(fc) csvFile = csv.writer(open("E:\HUC2_DATA\HUC02\NewShapes\HUC02Test.csv", 'wb')) #output csv fieldnames = [f.name for f in arcpy.ListFields(fc)]  allRows = [] for row in rows:     rowlist = []     for field in fieldnames:         rowlist.append(row.getValue(field))     allRows.append(rowlist)      csvFile.writerow(fieldnames) for row in allRows:     csvFile.writerow(row)


Can I augment this script to reformat the table?

Visual example (This comes out of SQL so there's no commas):

pointID Latitude Longitude HydroID UnqHydroID UnqTransID OBJECTID IS_Xing
1 45.35959 -72.30345 337314 337314001 33731400100 1 0
2 45.35805 -72.30215 337314 337314001 33731400085 2 0


to

Longitude Latitude HydroID UnqHydroID UnqTransID OBJECTID IS_Xing
-72.30345 45.35959 337314 337314001 33731400100 1 0
-72.30215 45.35805 337314 337314001 33731400085 2 0
Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JohnReiser
New Contributor III
Take a look at the csv.DictWriter class: http://docs.python.org/2/library/csv.html

You can then specify a dictionary to writerow, with the order specified as a list when the DictWriter was created.

You should then also change your loop to be a list of dictionaries instead of a list of lists, aka a 2D array, as it is now.

Not tested, but this should work:
import arcpy, csv fc = "E:\HUC2_DATA\HUC02\NewShapes\Result.gdb\HUC02Transect200m15pt" #input feature class rows = arcpy.SearchCursor(fc) fieldnames = [f.name for f in arcpy.ListFields(fc)] csvFile = csv.DictWriter(open("E:\HUC2_DATA\HUC02\NewShapes\HUC02Test.csv", 'wb'), fieldnames) #output csv  allRows = [] for row in rows:     rowlist = {}     for field in fieldnames:         rowlist['field'] = row.getValue(field))     allRows.append(rowlist)      csvFile.writeheader() for row in allRows:     csvFile.writerow(row)


Hope this helps.

View solution in original post

0 Kudos
3 Replies
JohnReiser
New Contributor III
Take a look at the csv.DictWriter class: http://docs.python.org/2/library/csv.html

You can then specify a dictionary to writerow, with the order specified as a list when the DictWriter was created.

You should then also change your loop to be a list of dictionaries instead of a list of lists, aka a 2D array, as it is now.

Not tested, but this should work:
import arcpy, csv fc = "E:\HUC2_DATA\HUC02\NewShapes\Result.gdb\HUC02Transect200m15pt" #input feature class rows = arcpy.SearchCursor(fc) fieldnames = [f.name for f in arcpy.ListFields(fc)] csvFile = csv.DictWriter(open("E:\HUC2_DATA\HUC02\NewShapes\HUC02Test.csv", 'wb'), fieldnames) #output csv  allRows = [] for row in rows:     rowlist = {}     for field in fieldnames:         rowlist['field'] = row.getValue(field))     allRows.append(rowlist)      csvFile.writeheader() for row in allRows:     csvFile.writerow(row)


Hope this helps.
0 Kudos
MatthewSchmitt1
New Contributor
Thanks! I believe that's the module I'm looking for. As a new comer to Python though, I can't seem to figure out how to properly format the code to change the fieldnames. Do I separate them at the end of
csvFile = csv.DictWriter(open("E:\HUC2_DATA\HUC02\NewShapes\HUC02Test.csv", 'wb'), fieldnames)

where the 'fieldnames' is?

Another question is does the 2D array free up memory? When I tried running my original script on a shape with 23 million rows it ran out of memory.
0 Kudos
NathanHeick
Occasional Contributor II
When you create the csv.DictWriter, the list of fieldnames determines the order in which the fields are written to the .csv file.  I would recommend modifying your code to write the rows right after you read them in.  With 23 million rows, you don't want to read all of the rows into memory and then write out the data.

If you want to keep working on performance, there is a good session from the 2013 DevSummit about geoprocessing in Python and performance.  The newer versions of ArcGIS have a new search cursor that runs significantly faster.  You may have some other alternative approaches that are even faster, possibly using numPy arrays.  Look at the documentation on search cursors.  It's usually good to del row, rows when you are done with the search cursor to release any locks.
0 Kudos