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:
* CONC URBAN 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.00000 4580537.00000 0.25426 19.28
430842.00000 4580537.00000 0.27233 14.72
431342.00000 4580537.00000 0.30566 2.84
431842.00000 4580537.00000 0.30379 0.21
432342.00000 4580537.00000 0.27413 1.13
432842.00000 4580537.00000 0.25462 0.00
433342.00000 4580537.00000 0.25114 0.00
433842.00000 4580537.00000 0.28779 0.00
434342.00000 4580537.00000 0.29707 0.00
434842.00000 4580537.00000 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.00000"
excel.Columns("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.

 

[Bruno Desthuilliers]

(e-mail address removed) a écrit :

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

Then you may want to have a look here:
http://docs.python.org/lib/module-csv.html
and here:
http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/362715

(I'm using Pythonwin).

The .plt file looks like this:

* ISCST3 (02035): Tersa
* MODELING OPTIONS USED:
* CONC URBAN 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.00000 4580537.00000 0.25426 19.28
430842.00000 4580537.00000 0.27233 14.72
431342.00000 4580537.00000 0.30566 2.84
431842.00000 4580537.00000 0.30379 0.21
432342.00000 4580537.00000 0.27413 1.13
432842.00000 4580537.00000 0.25462 0.00
433342.00000 4580537.00000 0.25114 0.00
433842.00000 4580537.00000 0.28779 0.00
434342.00000 4580537.00000 0.29707 0.00
434842.00000 4580537.00000 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

Ok. Where is your attempt to "translate" to Python, exactly ? So far,
except for the first 2 lines, it's vb code, not Python. Please start by
learning Python, then start doing the translation - and then we'll be
glad to help you.

(snip vb code)

Any ideas on what am I doing wrong?

Yes : trying to feed vb code to the Python interpreter.

 

[gblais]

Or, you might want to look at two packages:

xlrd

pyExcelerator

The first can "read" .xls files, and the second can write them. I've had
great results with both.

Gerry

 

[John Machin]

Or, you might want to look at two packages:

xlrd

pyExcelerator

The first can "read" .xls files, and the second can write them. I've had
great results with both.

Hi Gerry,

Thanks for the testimonial for xlrd

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 ...

Cheers,
John

 

[Dennis Lee Bieber]

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...
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[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.

 

[Bruno Desthuilliers]

(e-mail address removed) a écrit :

Or, you might want to look at two packages:

xlrd

pyExcelerator

The first can "read" .xls files, and the second can write them. I've had
great results with both.

That's fine, but since the OP is mainly using Excel for reformating a
csv file and saving it as a dbf file, I fail to see how these packages
would help...

 

[Bruno Desthuilliers]

(e-mail address removed) a écrit :

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.

I would definitively not qualify function call syntax as "in-depth
knowledge"

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.

http://docs.python.org/tut/tut.html