SaveAs to CSV from XLS, Excel

[jld730]

Greetings!

A user will supply a .xls or .csv file (with location/directory) as a system argument. If it is a CSV file, the script moves on. If it is a XLS/XLSX file, the script should save it as a CSV file. I wrote the below code, which works, but in actuality it doesn't save the CSV file properly as a CSV file, if you know what I mean. I looked on MSDN site and found some guidance -- line 12 below should be something like

xlApp.ActiveWorkbook.SaveAs(csvOutFile, xlApp.XlFileFormat.xlCSV)

... but that isn't working for me (AttributeError: Excel.Application.XlFileFormat).

Can someone help me with this?

Thanks!

inFile = sys.argv[1]
print inFile
whereDot = inFile.find('.')
print whereDot
end = inFile[whereDot:]
if end <> ".csv":
    csvOutFile = inFile[0:whereDot] + ".csv"
    print "xls to csv"
    xlApp = win32com.client.Dispatch("Excel.Application")
    xlApp.Visible = 0
    xlApp.Workbooks.Open(inFile)
    xlApp.ActiveWorkbook.SaveAs(csvOutFile)
    xlApp.Quit()
else:
    print "csv already exists, ok"
    csvOutFile = inFile
 

[jld730]

Still no luck on my end of figuring this out - surely its simple. I did run across the CSV module for Python, but I don't think that is what I need. I simply need to save an XLS file as a CSV file and then move on -- no need to access the content inside the CSV file. Essentially, after save the file as a true CSV file, I execute GIS/ESRI code (I don't imagine many of you deal in this realm, but if interested, once I have the CSV file I immediately turn it into a geographic layer with

gp.MakeXYEventLayer_management(csvOutFile, "POINT_X", "POINT_Y", "XY_event_lyr", "")

). This code works on a CSV file that I saveAs a CSV file in Excel, but if I just do what I did first (replace xlsx wqith csv) in the code, it doesn't work. Other testing direct with my GIS sw proves this -- that I need to do a true saveAs CSV.
So, please any help would be greatly appreciated.

[dazzler]

bad guess, maybe it's
xlApp.ActiveWorkbook.SaveAs(csvOutFile, xlApp.FileFormat.CSV) ?

can you point out the msdn page where did you get that info =)

[jld730]

Yeah, I tried that form, too! It was one of my first bad guesses! ;-)

MSDN code library is at > http://msdn2.microsoft.com/en-us/lib...zh(VS.80).aspx

But anyway... yea! I finally found another posting on a GIS forum that gave me this code, which works.

xlApp.ActiveWorkbook.SaveAs(csvOutFile, FileFormat=24)

I haven't found any documentation out there that states what the various codes are -- like, what if I wanted to save as a txt file? Can any of you experts direct me to it?

[jld730]

I finally ran across a method to get at the value of Excel constants for use in Pyhton scripting.

From http://aspn.activestate.com/ASPN/Coo.../Recipe/528870 ...

To get the value of Excel Constants such as xlEdgeLeft (7) or xlThin (2)
type e.g. Debug.Print xlEdgeLeft in the Immediate window of the VBA editor and press enter.