Working with Excel inside Python

[AleydisGP]

I have a .plt file (which is a tab delimited ASCII file) and I want to
format it to get a .dbf with data in rows and columns, detele some
rows/columns and substitute decimal '.' with ','. All this using Python
(I'm using Pythonwin).

The .plt file looks like this:

* ISCST3 (02035): Tersa
* MODELING OPTIONS USED:
*  CONCURBAN ELEV DFAULT

* PLOT FILE OF ANNUAL VALUES FOR SOURCE GROUP: ALL
* FOR A TOTAL OF   400 RECEPTORS.
* FORMAT: (3(1X,F13.5),1X,F8.2,2X,A6,2X,A8,2X,I8.8,2X,A8)
*X Y  AVERAGE CONC   ZELEV
*  ___   ___   ___   __
  430342.0 4580537.0   0.2542619.28
  430842.0 4580537.0   0.2723314.72
  431342.0 4580537.0   0.30566 2.84
  431842.0 4580537.0   0.30379 0.21
  432342.0 4580537.0   0.27413 1.13
  432842.0 4580537.0   0.25462 0.00
  433342.0 4580537.0   0.25114 0.00
  433842.0 4580537.0   0.28779 0.00
  434342.0 4580537.0   0.29707 0.00
  434842.0 4580537.0   0.31067 0.00
 


I recorded a macro in Excel with the whole process, but when trying to
translate it into Python I get syntax errors which I don't know how to
solve.

This is my python code:
## --
import win32com.client
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = 0
workbook=excel.Workbooks.Open('D:\AN00GALL.plt')

excel.Columns("A:A").Select
excel.Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

excel.Columns("A:A").Select
excel.Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(14, 1),
Array(29, 1), Array(42, 1), _
Array(53, 1), Array(59, 1), Array(71, 1), Array(79, 1)),
ThousandsSeparator:=" ", _
TrailingMinusNumbers:=True

excel.Selection.Delete Shift:=xlToLeft

excel.Rows("1:6").Select
excel.Selection.Delete Shift:=xlUp
excel.Rows("2:2").Select
excel.Selection.Delete Shift:=xlUp

excel.Columns("A:C").Select
excel.Selection.NumberFormat = "0.0"
excel.Columns("D:D").Select
excel.Selection.NumberFormat = "0.00"
excel.ActiveWorkbook.SaveAs Filename:= _
"D:\AN00GALL.dbf", FileFormat:= _
xlDBF4, CreateBackup:=False

excel.Quit()
## --

Any ideas on what am I doing wrong?
Thank you.

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: Working with Excel inside Python

[AleydisGP]

Sorry for my little knowledge on Python. Actually my knowledge is
specific for automating geo-processing tasks within ESRI environment,
but sometimes I need to automate some other tasks (like this one) which
require more in-depth knowledge of this language.
Lots of documentation are of no use when you don't know exactly what to
look for or have a wrong idea of what to do.
Thanks for the guidance.

On 8 ene, 02:21, Dennis Lee Bieber <[EMAIL PROTECTED]> wrote:
> On 7 Jan 2007 17:06:10 -0800, "John Machin" <[EMAIL PROTECTED]>
> declaimed the following in comp.lang.python:
>
> > However I don't understand how a reasonable solution to the OP's
> > requirement (translate a tab-separated file to a DBF file with a bit of
> > slicing and dicing on the way) would have anything to do with .xls
> > files, or  Excel, or VB ...Only in that the original poster stated 
> > they were trying to
> translate an Excel VBA "macro" into Python, and the malformed (for
> Python) code appeared to be using Window's COM access to run all the
> work via Excel (I presume via importing the TSV, performing the edits,
> then exporting via some DBF compatible format -- ODBC?).
>
> I'd agree, however, that the specification of the task to be
> performed does not, it would seem, require any of the clumsiness of
> using Excel. Read the lines of the TSV file using proper specifications
> to the Python CSV file handling module, edit the lines as needed, and
> write them via an ODBC (or other) database adapter that generates the
> desired DBF format...
> --
> WulfraedDennis Lee Bieber   KD6MOG
> [EMAIL PROTECTED] [EMAIL PROTECTED]
> HTTP://wlfraed.home.netcom.com/
> (Bestiaria Support Staff:   [EMAIL PROTECTED])
> HTTP://www.bestiaria.com/

-- 
http://mail.python.org/mailman/listinfo/python-list