topic Re: How to convert excel to csv? in Python Questions

How to convert excel to csv?

LeroneSavage — Fri, 19 Jul 2013 18:27:08 GMT

I been looking for the code to convert an excel into a csv? I also look in ArcHelp for code that can convert excel into a xy event
but they require csv. So, if I there no way to convert the code to a csv is there a way to make an excell file into a xy event?

Re: How to convert excel to csv?

JimCousins — Fri, 19 Jul 2013 18:45:37 GMT

Arcmap will read an excel worksheet and you can create an event theme based on coordinates stored in the file.
Right out of the help:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//005s0000001w000000

Re: How to convert excel to csv?

LeroneSavage — Fri, 19 Jul 2013 20:20:51 GMT

I know that. But I am looking for the python code that does the conversion. Thnx.

Re: How to convert excel to csv?

Zeke — Fri, 19 Jul 2013 20:39:09 GMT

There's also a tool to convert between tables, excel and csv. You can add it to a model, or adapt the code in your own scripts, or just use it on it's own. Very handy.

Re: How to convert excel to csv?

DarrenWiens2 — Fri, 10 Dec 2021 22:04:44 GMT

You can control Excel from Python using win32com.client.

import win32com.client

xlApp = win32com.client.Dispatch("Excel.Application") #Open Excel
xlWb = xlApp.Workbooks.Open(PATH_TO_WORKBOOK) #Open a workbook - change the path
xlApp.ActiveWorkbook.SaveAs(PATH_TO_NEW_CSV_FILE, FileFormat=24) #Save to csv - FileFormat=24 means .csv
xlApp.ActiveWorkbook.Close(SaveChanges=0) #Close Workbook
xlApp.Quit() #Close Excel

Re: How to convert excel to csv?

LeroneSavage — Sun, 28 Jul 2013 11:11:09 GMT

There's also a tool to convert between tables, excel and csv. You can add it to a model, or adapt the code in your own scripts, or just use it on it's own. Very handy.

I download the tool. But there is no documentation for the arguments. Do you happen to know them? Thanks in advance for your consideration.

# convert .dbfs to CSV
def export_to_csv(dataset, output, dialect):
    """Output the data to a CSV file"""
    # create the output writer
    out_writer = csv.writer(open(output, 'wb'), dialect=dialect)
    # return the list of field names and field values
    header, rows = domainvalues.header_and_iterator(dataset)

    # write the field names and values to the csv file
    out_writer.writerow(map(domainvalues._encodeHeader, header))
    for row in rows:
        out_writer.writerow(map(domainvalues._encode, row))

if __name__ == "__main__":
    # Get parameters
    dataset_name = arcpy.GetParameterAsText(0)
    output_file = arcpy.GetParameterAsText(1)
    delim = arcpy.GetParameterAsText(2).lower()
    dialect = 'excel'
    if delim == 'comma':
        pass
    else:
        dialect = 'excel-tab'
    try:
        export_to_csv(dataset_name, output_file, dialect)
    except Exception as err:
        arcpy.AddError('Error: {0}'.format(err))

dataset = outLocation+"Nearness.dbf" # what goes here?
output = "Nearness" # same from above
dialect = # not sure what goes here?

# function call

export_to_csv(dataset, output, dialect)

#print "Convered .dbfs into CSVs"

Help understanding the argments for a TableToCSV function

LeroneSavage — Sun, 28 Jul 2013 11:15:54 GMT

I download the tool that had this function that converts .dbf to csv. But there is no documentation for how to implement the arguments of the tool. Do you happen to know to do so? Thanks in advance for your consideration.

# convert .dbfs to CSV

def export_to_csv(dataset, output, dialect):
"""Output the data to a CSV file"""
# create the output writer
out_writer = csv.writer(open(output, 'wb'), dialect=dialect)
# return the list of field names and field values
header, rows = domainvalues.header_and_iterator(dataset)

# write the field names and values to the csv file
out_writer.writerow(map(domainvalues._encodeHeader, header))
for row in rows:
out_writer.writerow(map(domainvalues._encode, row))

if __name__ == "__main__":
# Get parameters
dataset_name = arcpy.GetParameterAsText(0)
output_file = arcpy.GetParameterAsText(1)
delim = arcpy.GetParameterAsText(2).lower()
dialect = 'excel'
if delim == 'comma':
pass
else:
dialect = 'excel-tab'
try:
export_to_csv(dataset_name, output_file, dialect)
except Exception as err:
arcpy.AddError('Error: {0}'.format(err))

dataset = # what goes here?
output = " # not sure what goes here
dialect = # same as above

# function call

export_to_csv(dataset, output, dialect)

#print "Convered .dbfs into CSVs"

Re: How to convert excel to csv?

T__WayneWhitley — Fri, 10 Dec 2021 22:04:46 GMT

I'll be fairly brief in my comments here...

First of all, in what I'll call the 'main' script you posted, you are missing lines importing other modules/scripts this main script depends on:

import arcpy
import os
import csv
import domainvalues

...and with that, let's pay particular attention to the last line of that import set (you already have ArcGIS 10.0, correct?...in other words, the ability to import arcpy??). That line imports a 'helper' py file called domainvalues (which you should have downloaded with this package, contained in the 'scripts' folder) and this should handle converting any db domain descriptor values you have defined (if converting a db table). This script has to be in a python-recognizable location, so provided you have a default install, you could place the domainvalues.py in this common library location for '3rd party modules':

C:\Python\Lib\site-packages\

About that convention on lib files, you can read more here:
http://docs.python.org/2/using/windows.html#finding-modules

Also in the download, there's a tbx that likely contains an already 'wired' tool interface (check this out in your download) -- I strongly recommend you implement that so that you can call this as a script tool. I think you should be able to then use the script unaltered - you may have to 'wire' the script to this toolbox interface but that is easy via ArcToolbox (or Catalog in ArcMap). If the script tool has been saved with relative paths, you may only need to run the tool through your toolbox interface from your download location (the one you unzipped to).

Having said all that, you can make this run without arcpy or domainvalues - but this was written to leverage your ArcGIS install, so why cripple it, working out a different hack when it appears your problem seems to be only with install details?

Hope that helps,
Wayne

...one more FYI: enclose all code in tags-- use the hash tool on the toolbar, or type after the code and

 before the code.  So this would be the actual priginally published py code from earlier in this thread, part of the package you downloaded:
"""
   This script will convert a table to a .csv file. It will transfer domain
   descriptions, rather than just their coded values
"""
import arcpy
import os
import csv
import domainvalues


def export_to_csv(dataset, output, dialect):
    """Output the data to a CSV file"""
    # create the output writer
    out_writer = csv.writer(open(output, 'wb'), dialect=dialect)
    # return the list of field names and field values
    header, rows = domainvalues.header_and_iterator(dataset)

    # write the field names and values to the csv file
    out_writer.writerow(map(domainvalues._encodeHeader, header))
    for row in rows:
        out_writer.writerow(map(domainvalues._encode, row))

if __name__ == "__main__":
    # Get parameters
    dataset_name = arcpy.GetParameterAsText(0)
    output_file = arcpy.GetParameterAsText(1)
    delim = arcpy.GetParameterAsText(2).lower()
    dialect = 'excel'
    if delim == 'comma':
        pass
    else:
        dialect = 'excel-tab'
    try:
        export_to_csv(dataset_name, output_file, dialect)
    except Exception as err:
        arcpy.AddError('Error: {0}'.format(err))

Re: Help understanding the argments for a TableToCSV function

T__WayneWhitley — Sun, 28 Jul 2013 15:44:25 GMT

see your other thread:
http://forums.arcgis.com/threads/88874-How-to-convert-excel-to-csv

Re: How to convert excel to csv?

RhettZufelt — Fri, 10 Dec 2021 22:04:49 GMT

I been looking for the code to convert an excel into a csv? I also look in ArcHelp for code that can convert excel into a xy event
but they require csv. So, if I there no way to convert the code to a csv is there a way to make an excell file into a xy event?

Do you really need a csv file, or are you just trying to create the xy event theme?
As pointed out earlier, you can use the excel sheet as input to the create xy event theme, the do NOT require a csv.

# Import arcpy module
import arcpy


# Local variables:
Sheet1 = "D:\\Book1.xls\\Sheet1$"
Output_Location = "D:\\"
Sheet1Layer = "Sheet1$Layer"

# Process: Make XY Event Layer
arcpy.MakeXYEventLayer_management(Sheet1, "Easting", "Northing", Sheet1Layer, "", "")

This make an even layer just fine using xls as input (docs say xlxs supported also).

R_