Python script modification

[RandallClark1]

Can someone please help me modify this script?

It basically out puts a table to CSV, which i am liking immensely, but what i would love if there is a way to modify it so that when the CSV is made it gets rid of the Header and the drops the FID field

Thanks


Randall Clark, GISP
GIS Manager
InDepth Corporation

[JakeSkinner]

Hi Randall,

I didn't see a script attached to your post, but you can try adding the below code to your script:

import csv, os

table = r"C:\temp\python\XY.csv"
outFile = r"C:\temp\python\XY_1.csv"
outFile2 = r"C:\temp\python\XY_2.csv"

#read lines of CSV
f = open(table, "r")
lines=f.readlines()
lines=lines[1:]

f.close()

#write all lines except header to CSV
f = open(table, "w+")
for line in lines:
    f.write(line)

f.close()

#remove first column
with open(table,"r") as input:
    with open(outFile,"w+") as output:
        writer=csv.writer(output)
        for row in csv.reader(input):
            writer.writerow(row[1:])

#remove empty rows
input = open(outFile, 'rb')
output = open(outFile2, 'wb')
writer = csv.writer(output)
for row in csv.reader(input):
    if row:
        writer.writerow(row)
        
input.close()
output.close()

os.remove(table)
os.remove(outFile)

#rename to original CSV
os.rename(outFile2, table)

print "Finished"

The 'table' variable is your original CSV file.  'outFile' and 'outFile2' do not need to exist; these will be created on the fly.  Also, this code assumes the FID field is the first column in your CSV file. 

For some reason, after removing the first column the output CSV file contained an empty row between each row.  That is why there is code to remove empty rows.

[RandallClark1]

So here is the original

# -*- coding: utf-8 -*-
"""
   This script will convert a table to an excel spreadsheet. If the third-party
   module xlwt is available, it will use that. Otherwise, it will fall back to
   CSV.
"""
import arcgisscripting
import os

gp = arcgisscripting.create(9.3)

def header_and_iterator(dataset_name):
    """Returns a list of column names and an iterator over the same columns"""

    data_description = gp.Describe(dataset_name)
    fieldnames = [f.name for f in data_description.fields if f.type not in ["Geometry", "Raster", "Blob"]]
    def iterator_for_feature():
        cursor = gp.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()

def export_to_csv(dataset, output):
    """Output the data to a CSV file"""
    import csv

    def _encode(x):
        if isinstance(x, unicode):
            return x.encode("utf-8")
        else:
            return str(x)

    def _encodeHeader(x):
        return _encode(x.replace(".","_"))
        
    out_writer = csv.writer(open(output, 'wb'))
    header, rows = header_and_iterator(dataset)
    out_writer.writerow(map(_encodeHeader, header))
    for row in rows:
        out_writer.writerow(map(_encode, row))

def export_to_xls(dataset, output):
    """
    Attempt to output to an XLS file. If xmlwt is not available, fall back
    to CSV.
       
    XLWT can be downloaded from http://pypi.python.org/pypi/xlwt"""
    try:
        import xlwt
    except ImportError:
        gp.AddError("import of xlwt module failed")
        return
    header, rows = header_and_iterator(dataset)

    # Make spreadsheet
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet(os.path.split(dataset)[1])

    #Set up header row, freeze panes
    header_style = xlwt.easyxf("font: bold on; align: horiz center")
    for index, colheader in enumerate(header):
        worksheet.write(0, index, colheader.replace(".","_"))
    worksheet.set_panes_frozen(True)
    worksheet.set_horz_split_pos(1)
    worksheet.set_remove_splits(True)

    # Write rows
    for rowidx, row in enumerate(rows):
        for colindex, col in enumerate(row):
            worksheet.write(rowidx+1, colindex, col)

    # All done
    workbook.save(output)
    
if __name__ == "__main__":
    dataset_name = gp.GetParameterAsText(0)
    output_file = gp.GetParameterAsText(1)
    format = gp.GetParameterAsText(2)
    if format == "CSV":
        export_to_csv(dataset_name, output_file)
    elif format == "XLS":
        try:
            export_to_xls(dataset_name, output_file)
        except:
            import traceback
            gp.AddError(traceback.format_exc())
    else:
        raise ValueError("Don't know how to export to %r" % format)
    
print "FINISHED"

and here is yours added. Did i get it right?

# -*- coding: utf-8 -*-
"""
   This script will convert a table to an excel spreadsheet. If the third-party
   module xlwt is available, it will use that. Otherwise, it will fall back to
   CSV.
"""
import arcgisscripting
import os

gp = arcgisscripting.create(9.3)

def header_and_iterator(dataset_name):
    """Returns a list of column names and an iterator over the same columns"""

    data_description = gp.Describe(dataset_name)
    fieldnames = [f.name for f in data_description.fields if f.type not in ["Geometry", "Raster", "Blob"]]
    def iterator_for_feature():
        cursor = gp.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()

def export_to_csv(dataset, output):
    """Output the data to a CSV file"""
    import csv

    def _encode(x):
        if isinstance(x, unicode):
            return x.encode("utf-8")
        else:
            return str(x)

    def _encodeHeader(x):
        return _encode(x.replace(".","_"))
        
    out_writer = csv.writer(open(output, 'wb'))
    header, rows = header_and_iterator(dataset)
    out_writer.writerow(map(_encodeHeader, header))
    for row in rows:
        out_writer.writerow(map(_encode, row))

def export_to_xls(dataset, output):
    """
    Attempt to output to an XLS file. If xmlwt is not available, fall back
    to CSV.
       
    XLWT can be downloaded from http://pypi.python.org/pypi/xlwt"""
    try:
        import xlwt
    except ImportError:
        gp.AddError("import of xlwt module failed")
        return
    header, rows = header_and_iterator(dataset)

    # Make spreadsheet
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet(os.path.split(dataset)[1])

    #Set up header row, freeze panes
    header_style = xlwt.easyxf("font: bold on; align: horiz center")
    for index, colheader in enumerate(header):
        worksheet.write(0, index, colheader.replace(".","_"))
    worksheet.set_panes_frozen(True)
    worksheet.set_horz_split_pos(1)
    worksheet.set_remove_splits(True)

    # Write rows
    for rowidx, row in enumerate(rows):
        for colindex, col in enumerate(row):
            worksheet.write(rowidx+1, colindex, col)

    # All done
    workbook.save(output)
    
if __name__ == "__main__":
    dataset_name = gp.GetParameterAsText(0)
    output_file = gp.GetParameterAsText(1)
    format = gp.GetParameterAsText(2)
    if format == "CSV":
        export_to_csv(dataset_name, output_file)
    elif format == "XLS":
        try:
            export_to_xls(dataset_name, output_file)
        except:
            import traceback
            gp.AddError(traceback.format_exc())
    else:
        raise ValueError("Don't know how to export to %r" % format)

print "Finished"

#start new script
import csv, os

table = r"C:\temp\python\XY.csv"
outFile = r"C:\temp\python\XY_1.csv"
outFile2 = r"C:\temp\python\XY_2.csv"

#read lines of CSV
f = open(table, "r")
lines=f.readlines()
lines=lines[1:]

f.close()

#write all lines except header to CSV
f = open(table, "w+")
for line in lines:
    f.write(line)

f.close()

#remove first column
with open(table,"r") as input:
    with open(outFile,"w+") as output:
        writer=csv.writer(output)
        for row in csv.reader(input):
            writer.writerow(row[1:])

#remove empty rows
input = open(outFile, 'rb')
output = open(outFile2, 'wb')
writer = csv.writer(output)
for row in csv.reader(input):
    if row:
        writer.writerow(row)
        
input.close()
output.close()

os.remove(table)
os.remove(outFile)

#rename to original CSV
os.rename(outFile2, table)

print "Finished"

[JakeSkinner]

The below should work.  I added the additional code to a new function called 'reformat' and then called this function when CSV is chosen for the format type.

# -*- coding: utf-8 -*-
"""
   This script will convert a table to an excel spreadsheet. If the third-party
   module xlwt is available, it will use that. Otherwise, it will fall back to
   CSV.
"""
import arcgisscripting
import os

gp = arcgisscripting.create(9.3)

def header_and_iterator(dataset_name):
    """Returns a list of column names and an iterator over the same columns"""

    data_description = gp.Describe(dataset_name)
    fieldnames = [f.name for f in data_description.fields if f.type not in ["Geometry", "Raster", "Blob"]]
    def iterator_for_feature():
        cursor = gp.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()

def export_to_csv(dataset, output):
    """Output the data to a CSV file"""
    import csv

    def _encode(x):
        if isinstance(x, unicode):
            return x.encode("utf-8")
        else:
            return str(x)

    def _encodeHeader(x):
        return _encode(x.replace(".","_"))
        
    out_writer = csv.writer(open(output, 'wb'))
    header, rows = header_and_iterator(dataset)
    out_writer.writerow(map(_encodeHeader, header))
    for row in rows:
        out_writer.writerow(map(_encode, row))

def export_to_xls(dataset, output):
    """
    Attempt to output to an XLS file. If xmlwt is not available, fall back
    to CSV.
       
    XLWT can be downloaded from http://pypi.python.org/pypi/xlwt"""
    try:
        import xlwt
    except ImportError:
        gp.AddError("import of xlwt module failed")
        return
    header, rows = header_and_iterator(dataset)

    # Make spreadsheet
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet(os.path.split(dataset)[1])

    #Set up header row, freeze panes
    header_style = xlwt.easyxf("font: bold on; align: horiz center")
    for index, colheader in enumerate(header):
        worksheet.write(0, index, colheader.replace(".","_"))
    worksheet.set_panes_frozen(True)
    worksheet.set_horz_split_pos(1)
    worksheet.set_remove_splits(True)

    # Write rows
    for rowidx, row in enumerate(rows):
        for colindex, col in enumerate(row):
            worksheet.write(rowidx+1, colindex, col)

    # All done
    workbook.save(output)

def reformat(output):
    import csv
    #read lines of CSV
    f = open(output, "r")
    lines=f.readlines()
    lines=lines[1:]

    f.close()

    #write all lines except header to CSV
    f = open(output, "w+")
    for line in lines:
        f.write(line)

    f.close()

    #remove first column
    with open(output,"r") as input:
        with open(output + "_1", "w+") as output2:
            writer=csv.writer(output2)
            for row in csv.reader(input):
                writer.writerow(row[1:])

    #remove empty rows
    input = open(output + "_1", 'rb')
    output2 = open(output + "_2", 'wb')
    writer = csv.writer(output2)
    for row in csv.reader(input):
        if row:
            writer.writerow(row)
            
    input.close()
    output2.close()
    
    os.remove(output)
    os.remove(output + "_1")

    #rename to original CSV
    os.rename(output + "_2", output)
    
if __name__ == "__main__":
    dataset_name = gp.GetParameterAsText(0)
    output_file = gp.GetParameterAsText(1)
    format = gp.GetParameterAsText(2)
    if format == "CSV":
        export_to_csv(dataset_name, output_file)
        reformat(output_file)
    elif format == "XLS":
        try:
            export_to_xls(dataset_name, output_file)
        except:
            import traceback
            gp.AddError(traceback.format_exc())
    else:
        raise ValueError("Don't know how to export to %r" % format)
    
print "FINISHED"