Recently I had to wrangle some csv files, including some data calculations and outputting a semi-colon delimited file instead of a comma-delimited file.
A colleague in our Namibian office recently asked me to project some points for him. Easy enough, except it was in a xyz file and it was a million points and the file was huge and their internet is not great (which is saying something, as our internet is abysmal). After splitting the file into 10 separate csv files and emailing them (I know, I know), I gave one file to another colleague who is a projections ninja (silently beats them into submission? I don’t know).
Once she had figured out the multiple issues (there are always multiple issues – “Can you quickly project this arb file for me” invariably turns into a full day escapade), it was time to automate the process for all the csvs. To Python!
''' Created on 17 Sep 2014 @author: Cindy Williams Process csv and project coordinates on the fly Output new csv file ''' import arcpy import csv import os arcpy.env.overwriteOutput = True in_fld = r"C:\Some\Arb\Folder" out_fld = os.path.join(in_fld, "constant") end_fld = os.path.join(in_fld, "schwarzeck") gdb = r"C:\Some\Arb\Folder\working.gdb" sr_nam = arcpy.SpatialReference(r"C:\Some\Arb\Folder\Schwarzeck17.prj") sr_cape = arcpy.SpatialReference(r"C:\Some\Arb\Folder\cape17.prj") lyr = "XY_Layer" excl = ["constant", "schwarzeck"] def checkPath(p): if not os.path.exists(p): os.mkdir(p) return "Created path " + p else: return "Path exists: " + p print checkPath(out_fld) print checkPath(end_fld) for root, dirs, filenames in os.walk(in_fld, topdown=True): dirs[:] = [d for d in dirs if d not in excl] # Prune directories in-place for f in filenames: if f.endswith(".csv"): cur_csv = os.path.join(root, f) new_csv = os.path.join(out_fld, "CapeConstant_" + str(f.split(") "))) # Read in the values and add the constant with open(cur_csv, 'rb') as infile: with open(new_csv, 'wb') as outfile: csvreader = csv.reader(infile) csvwriter = csv.writer(outfile) csvwriter.writerow(["y", "x", "z"]) for row in csvreader: csvwriter.writerow([row, float(row) - 2000000], row) print "Finished processing " + f for root, dirs, filenames in os.walk(out_fld): for f in filenames: cur_xy = os.path.join(root, f) end_csv = os.path.join(end_fld, "Schwarzeck17_" + f.split("_")) ftr = os.path.join(gdb, f[:-4]) # Create temporary points layer from coordinates in the csv arcpy.management.MakeXYEventLayer(cur_xy, "y", "x", lyr, sr_cape) print "Created XY Layer for " + f # Copy temp layer to disk arcpy.management.CopyFeatures(lyr, ftr) print "Created feature class" # Project on the fly and write new coordinates to csv with open(end_csv, 'wb') as csvfile: csvwriter = csv.writer(csvfile) with arcpy.da.UpdateCursor(ftr, ["SHAPE@XY", "z"], "#", sr_nam) as cursor: for row in cursor: csvwriter.writerow([row, row, row]) print "Processed " + end_csv