AnsweredAssumed Answered

Exporting Records to .CSV.  Problem with Comma data offsets to another field - CODE INCLUDED

Question asked by ipeebles on Nov 10, 2014
Latest reply on Nov 13, 2014 by bixb0012

I have a python script tool that exports records from a feature layer using an (area of interest - aoi).  The tool works well, but the problem is that if I have a comma in text in one of the field records, it will break the text after the comma and move it to the next field.  The shown below that are highlighted in yellow show records that are offset.

 

ExportRecordsOffsetFromFields.jpg

 

I am attaching the toolbox along with the script.  Is there a way I can fix this issue?  I didn't get any help from ESRI.  They just forwarded me some links, but I figure I can get some assistance from here.

 

Here is a copy of the toolbox and script as an attachment:

 

 

Here is the script code itself:

 

<CODE>

# -*- 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.
'''
# **********************************************************************
# * 01. Import arcpy Module                                            *
# **********************************************************************

import arcpy
import os
import zipfile

# **********************************************************************
# * 02. Variable Descriptions                                          *
# **********************************************************************
# Variable    Definition
#
# InputDataset   Dataset used for input.
# DataDescription  Described properties of the input dataset.
# FieldNames   Field names from table.
# FileFormat   File format of output either CSV or XLS.
# SelectedFeatures  Features that are selected from input dataset.
# OutputFile   Name of the output file.

# **********************************************************************
# * 03. Prepare Data for Table                                         *
# **********************************************************************

def get_ID_message(ID):
    return re.sub('%1|%2', '%s', arcpy.GetIDMessage(ID))

def zipUpFolder(folder, outZipFile):
    # zip the data
    try:
        zip = zipfile.ZipFile(outZipFile, 'w', zipfile.ZIP_DEFLATED)
        zipws(unicode(folder), zip, 'CONTENTS_ONLY')
        zip.close()
    except RuntimeError:
        # Delete zip file if exists
        if os.path.exists(outZipFile):
            os.unlink(outZipFile)
        zip = zipfile.ZipFile(outZipFile, 'w', zipfile.ZIP_STORED)
        zipws(unicode(folder), zip, 'CONTENTS_ONLY')
        zip.close()
        #Message'  Unable to compress zip file contents.'
        arcpy.AddWarning(get_ID_message(86133))

def zipws(path, zip, keep):
    path = os.path.normpath(path)
    # os.walk visits every subdirectory, returning a 3-tuple
    #  of directory name, subdirectories in it, and filenames
    #  in it.
    for (dirpath, dirnames, filenames) in os.walk(path):
        # Iterate over every filename
        for file in filenames:
            # Ignore .lock files
            if not file.endswith('.lock'):
                #arcpy.AddMessage('Adding %s...' % os.path.join(path, dirpath, file))
                try:
                    if keep:
                        zip.write(os.path.join(dirpath, file),
                        os.path.join(os.path.basename(path), os.path.join(dirpath, file)[len(path)+len(os.sep):]))
                    else:
                        zip.write(os.path.join(dirpath, file),
                        os.path.join(dirpath[len(path):], file))

                except Exception as e:
                    #Message '    Error adding %s: %s'
                    arcpy.AddWarning(get_ID_message(86134) % (file, e[0]))
    return None

def createFolderInScratch(folderName):
    # create the folders necessary for the job
    folderPath = arcpy.CreateUniqueName(folderName, arcpy.env.scratchFolder)
    arcpy.CreateFolder_management(arcpy.env.scratchFolder, os.path.basename(folderPath))
    return folderPath

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

# Describe Properties of Input Dataset
    DataDescription = arcpy.Describe(InputDataset)

# Field Name Definitions
    FieldNames = [f.name for f in DataDescription.fields if f.type not in ['Geometry', 'Raster', 'Blob']]
    def iterator_for_feature():
        cursor = arcpy.SearchCursor(InputDataset)
        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_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:
        arcpy.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 and 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)

    # Completed Workbook
    workbook.save(output)

if __name__ == '__main__':

# ***********************************
# * 01. Input Parameters            *
# ***********************************
   
    InputDataset  = arcpy.GetParameterAsText(0) # Input Dataset
    aoi           = arcpy.GetParameter(1) # Select Area of Interest
    FileFormat    = arcpy.GetParameterAsText(2) # Output format (CSV or XLS)
    OutputFolder  = createFolderInScratch('FlatFiles')
    OutputZipFile = arcpy.GetParameterAsText(3)

    for i,ds in enumerate(InputDataset.split(';')):

# ***********************************
# * 02. Select Dataset By Location  *
# ***********************************
arcpy.SelectLayerByLocation_management(ds, 'INTERSECT', aoi)

# Selected Features from Input Dataset
SelectedFeatures = arcpy.MakeFeatureLayer_management(ds)

# ***********************************
# * 03. Format                      *
# ***********************************

# Comma Separated Values (CSV)
if FileFormat == 'CSV':
     fields = ''
     for field in arcpy.ListFields(SelectedFeatures):
  if field.name.lower() not in ['shape']:
      fields = fields + field.name + ';'
     fields = fields.rstrip(';')
     arcpy.AddMessage('Export to CSV')
   
      # Create CSV File
     OutputFile = os.path.join(OutputFolder,'c%s_%s.csv' % (str(i).zfill(2), ds.split(os.sep)[-1]))
     arcpy.ExportXYv_stats(SelectedFeatures,fields,'COMMA',OutputFile,'ADD_FIELD_NAMES')
   
     # Excel File (XLS)
elif FileFormat == 'XLS':
     try:
   # Create XLS File
  OutputFile = os.path.join(OutputFolder,'x%s_%s.xls' % (str(i).zfill(2), ds.split(os.sep)[-1]))
  export_to_xls(ds, OutputFile)
     except:
  import traceback
  arcpy.AddError(traceback.format_exc())
else:
     raise ValueError('Don\'t know how to export to %r' % FileFormat)
   
   
    zipUpFolder(OutputFolder, OutputZipFile)

</CODE>

Outcomes