Data in CSV file overwritten

[LauraBlackburn]

I am exporting my data from multiple Network Anayst VRPs to a CSV file.  I am able to get the data exported to the csv file, but it keeps putting all the data in one row and overwriting it.  I need to be able to advance down to the next row, but I am new to python and not sure how to do this.  any help is greatly appreciated.  Here's the code that writes to the CSV file.

# create table - fields: From script [Block (shortBlockName), Scale (scale)], From Routes layer [stops (OrderCount), dTime (TotalTravelTime), dDistance (TotalDistance)]
        CSVFile = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\analysis2\\allRoutes.csv'
        f = open (CSVFile, "wb")
        w = csv.writer(f)
        fieldNames = ['OrderCount', 'TotalTravelTime', 'TotalDistance', 'Block', 'Scale']
        w.writerow(fieldNames)
        recordList = []
        # print "Data type: " + desc.dataElement.dataType # dataType = layer

        # examine children and print their name and datatype - no children
        lyrFile = arcpy.mapping.Layer(outLayerFile)
        for lyr in arcpy.mapping.ListLayers(lyrFile):
            if lyr.name == "Routes":
                #print "Routes location: " + lyr.datasetName # returns 'Routes'
                #print "Routes long Name: " + lyr.longName   # returns 'TrapRoute_IA1_1K\Routes'
                desc = arcpy.Describe(lyr)
                # indexFields = 0
                # for field in desc.fields:
                #    print "%-22s %s %s" % (field.name, ":", field.type) # this returns all of the fields in the routes table
                cntCursor = arcpy.da.SearchCursor(lyr, "OrderCount")
                for row in cntCursor:
                    cnt = row[0]
                    recordList.append(str(cnt))
                    print ("OrderCount: {}".format(cnt))
                timeCursor = arcpy.da.SearchCursor(lyr, "TotalTravelTime")
                for row in timeCursor:
                    t = row[0]
                    recordList.append(str(t))
                    print ("TravelTime: {}".format(t))
                distCursor = arcpy.da.SearchCursor(lyr,"TotalDistance")
                for row in distCursor:
                    dist = row[0]
                    recordList.append(str(dist))
                    print ("TravelDist: {}".format(dist))
                recordList.append(str(shortBlockName))
                recordList.append(str(scale))
                print ("Records: {}".format(recordList))    # returns:  [102, 144.19780824705958, 61.627489665697794, 'IA1', '1000']
                print ("Block: {}".format(shortBlockName))  # returns: IA1
                print ("Scale: {}".format(scale))           # returns: 1000
        w.writerow(recordList)
        w.next() # returns error - I need a way to move to the next row
    f.close()
# create table - fields: From script [Block (shortBlockName), Scale (scale)], From Routes layer [(OrderCount), (TotalTravelTime), (TotalDistance)]        CSVFile = 'F:\\Workspace\\Sandy\\GM_costAnalysis\\analysis2\\allRoutes.csv'        f = open (CSVFile, "wb")        w = csv.writer(f)        fieldNames = ['OrderCount', 'TotalTravelTime', 'TotalDistance', 'Block', 'Scale']        w.writerow(fieldNames)        recordList = []        # print "Data type: " + desc.dataElement.dataType # dataType = layer        # examine children and print their name and datatype - no children        lyrFile = arcpy.mapping.Layer(outLayerFile)        for lyr in arcpy.mapping.ListLayers(lyrFile):            if lyr.name == "Routes":                #print "Routes location: " + lyr.datasetName # returns 'Routes'                #print "Routes long Name: " + lyr.longName  # returns 'TrapRoute_IA1_1K\Routes'                desc = arcpy.Describe(lyr)                # indexFields = 0                # for field in desc.fields:                #    print "%-22s %s %s" % (field.name, ":", field.type) # this returns all of the fields in the routes table                cntCursor = arcpy.da.SearchCursor(lyr, "OrderCount")                for row in cntCursor:                    cnt = row[0]                    recordList.append(str(cnt))                    print ("OrderCount: {}".format(cnt))                timeCursor = arcpy.da.SearchCursor(lyr, "TotalTravelTime")                for row in timeCursor:                    t = row[0]                    recordList.append(str(t))                    print ("TravelTime: {}".format(t))                distCursor = arcpy.da.SearchCursor(lyr,"TotalDistance")                for row in distCursor:                    dist = row[0]                    recordList.append(str(dist))                    print ("TravelDist: {}".format(dist))                recordList.append(str(shortBlockName))                recordList.append(str(scale))                print ("Records: {}".format(recordList))    # returns:  [102, 144.19780824705958, 61.627489665697794, 'IA1', '1000']                print ("Block: {}".format(shortBlockName))  # returns: IA1                print ("Scale: {}".format(scale))          # returns: 1000        w.writerow(recordList)        w.next() # returns error - I need a way to move to the next row    f.close() 

[DarrenWiens2]

The line containing writerow is not within a loop, so it only writes once, after the code exits the for loop. Indent further to include in the loop and write multiple rows.

[LauraBlackburn]

When I try to indent my line: w.writerow(recordList), I get 5 blank lines and then 4 duplicate lines of code.  So there is something weird going on here.  I thought I might just be able to add a line return to my record list using recordList.append('\n') but that doesn't do it either.  I am sure this isn't the most elegant way to write code, but it is my first stab at it.

[DanPatterson_Retired]

\n is a line return in python but not for most text editors, you may as well just write to the file as you go along...in fact

look at Python's builtin csv module...explore the functionality of 'writer' and reader

>>> import csv
>>> dir(csv)
['Dialect', 'DictReader', 'DictWriter', 'Error', 'QUOTE_ALL', 'QUOTE_MINIMAL',
 'QUOTE_NONE', 'QUOTE_NONNUMERIC', 'Sniffer', 'StringIO', '_Dialect', '__all__',
 '__builtins__', '__doc__', '__file__', '__name__', '__package__', '__version__',
 'excel', 'excel_tab', 'field_size_limit', 'get_dialect', 'list_dialects', 're',
 'reader', 'reduce', 'register_dialect', 'unregister_dialect', 'writer']
>>>

[BlakeTerhune · Accepted solution]

If you're writing one row at a time, use

w.writerow()

If you're writing all the data at once in some kind of iterable variable, use

w.writerows()

Also, I recommend using the csv writer in a with statement to ensure it is closed even if there is an error. Here is what I use for writing a geodatabase table to csv.

import arcpy
import os
import csv

# Environment variables
workingDir = r"C:\temp"
workingGDB = os.path.join(workingDir, "MyGeodatabase.gdb")
inputTable = os.path.join(workingGDB, "MyInputTable")
outputCSV = os.path.join(workingDir, "MyOutput.csv")

# Create CSV
with open(outputCSV, "w") as csvfile:
    csvwriter = csv.writer(csvfile, delimiter=',', lineterminator='\n')
    ## Write field name header line
    fields = ['FirstField','NextField','AndThirdExample']
    csvwriter.writerow(fields)
    ## Write data rows
    with arcpy.da.SearchCursor(inputTable, fields) as s_cursor:
        for row in s_cursor:
            csvwriter.writerow(row)

[LauraBlackburn]

I very much appreciate you all taking the time to help!

When I use w.writerows() it breaks up all of my string variables into one character width fields and spits out something like this:

OrderCount,TotalTravelTime,TotalDistance,Block,Scale

1,6

6,7,.,7,7,6,9,4,8,9,7,9,1

2,9,.,2,9,1,2,5,7,6,3,1

I,A,1

3,0,0,0

if I use w.writerow() it keeps writing on the same line, but the format is correct and looks like this:

OrderCount,TotalTravelTime,TotalDistance,Block,Scale

16, 67.7769489791, 29.291257631, IA1, 3000

I know I am close to getting this right, I just need to tell it to write the next list of variables on the following line instead of overwriting.

[BlakeTerhune]

I don't know what outLayerFile is so this is the best I can do for you. See if you can make this work.

import arcpy
import os
import csv

lyrFile = arcpy.mapping.Layer(outLayerFile)

for lyr in arcpy.mapping.ListLayers(lyrFile):
    if lyr.name == "Routes":
        # Create CSV
        CSVFile = r'F:\Workspace\Sandy\GM_costAnalysis\analysis2\allRoutes.csv'
        with open(outputCSV, "w") as csvfile:
            csvwriter = csv.writer(csvfile, delimiter=',', lineterminator='\n')
            ## Write field name header line
            fields = ['OrderCount', 'TotalTravelTime', 'TotalDistance', 'Block', 'Scale']
            csvwriter.writerow(fields)
            ## Write data rows
            with arcpy.da.SearchCursor(lyr.dataSource, fields) as s_cursor:
                for row in s_cursor:
                    csvwriter.writerow(row)

[LauraBlackburn]

Hi Blake,

One quick question for you...if I use the "with open(outputCSV, "w") as csvfile:" do I also need a line to close this file?  If I do, would that be at the same indent level as that "with open" statement?

I did find that my file was being overwritten because the open CSV command was inside of a larger loop, so my code was just doing what I told it.  Now that I have moved the statements to open the CSV and write the headers outside of my main loop for solving the VRP (not shown in the snippets above) I am no longer overwriting my data output.

[BlakeTerhune]

There is nothing special you have to write to the file to close it, but you do need to close the CSV open object in the code. If you have it in a with statement (like in my code) it will always close automatically. In the code you posted, you do not have it in a with statement so you need the f.close() at the end.

Glad you got it working. Keep in mind that when you open the CSV file for writing, you have different modes to open it as. Use 'r' when the file will only be read, 'w' for only writing (an existing file with the same name will be erased), and 'a' opens the file for appending. Any data written to the file is automatically added to the end. The mode argument is optional; 'r' will be assumed if it’s omitted. If you try using the append mode, make sure you don't write the field name header row again!

[LauraBlackburn]

Thanks for all of your help Blake!  Also thanks for the link to the modes, I must have spent over an hour yesterday trying to find exactly what the various CSV format paramaters were w/o any luck.