nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

blake.terhune

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)

All comments

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.

blake.terhune

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

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.

blake.terhune

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)

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']
>>>

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.

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.