473,578 Members | 3,050 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Working with Excel inside Python

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,A 8)
* 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("Exce l.Application")
excel.Visible = 0
workbook=excel. Workbooks.Open( 'D:\AN00GALL.pl t')

excel.Columns(" A:A").Select
excel.Selection .Replace What:=".", Replacement:=", ", LookAt:=xlPart, _
SearchOrder:=xl ByRows, MatchCase:=Fals e, SearchFormat:=F alse, _
ReplaceFormat:= False

excel.Columns(" A:A").Select
excel.Selection .TextToColumns Destination:=Ra nge("A1"),
DataType:=xlFix edWidth, _
FieldInfo:=Arra y(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)),
ThousandsSepara tor:=" ", _
TrailingMinusNu mbers:=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:D").Select
excel.Selection .NumberFormat = "0.00"
excel.ActiveWor kbook.SaveAs Filename:= _
"D:\AN00GALL.db f", FileFormat:= _
xlDBF4, CreateBackup:=F alse

excel.Quit()
## ------------------------------------------

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

Jan 7 '07 #1
6 6659
Al*******@gmail .com 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/Coo.../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,A 8)
* 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("Exce l.Application")
excel.Visible = 0
workbook=excel. Workbooks.Open( 'D:\AN00GALL.pl t')
excel.Columns(" A:A").Select
excel.Selection .Replace What:=".", Replacement:=", ", LookAt:=xlPart, _
SearchOrder:=xl ByRows, MatchCase:=Fals e, SearchFormat:=F alse, _
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.

Jan 7 '07 #2
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
Jan 7 '07 #3
gb****@cox.net wrote:
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

Jan 8 '07 #4
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 <wlfr...@ix.net com.comwrote:
On 7 Jan 2007 17:06:10 -0800, "John Machin" <sjmac...@lexic on.net>
declaimed the following in comp.lang.pytho n:
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
wlfr...@ix.netc om.com wulfr...@bestia ria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria. com)
HTTP://www.bestiaria.com/
Jan 8 '07 #5
gb****@cox.net 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...

Jan 8 '07 #6
Al*******@gmail .com 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
Jan 8 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2474
by: ÒÊÃÉɽÈË | last post by:
i want to compare the content in excel,but i don't know whick module to use! can you help me?
6
6923
by: LorcanM | last post by:
Hello, I use pdb under Linux to debug my Python code, as in: python -m pdb myprogram.py By default it does a postmortem of unhandled exceptions, is there a way to get it to break on warnings? Thanks a lot,
0
2730
by: liam_jones | last post by:
I'm very new to Python, well IronPython to precise, and have been having problems when using Excel. The problem I'm having is the closing of my Excel object. I'm able to successfully quit the Excel Application that I create, but when I open a Workbook in the Application I can't successfully Quit Excel (by this I mean I can quit it, but the...
3
71619
by: pradeep nair | last post by:
how to find out the present working directory using python. os.system('pwd') works good. But i need some specific one in python rather than embedding shell command into python.
1
4768
by: Girish | last post by:
Hi, I want to embed a txt document into an excel using python. Here is my code, but i get an error message =================================================== Traceback (most recent call last): File "C:\Documents and Settings\kusumap\Desktop\Girish.py", line 7, in ? worksheet.OLEObjects.Add(Filename="C:\Documents and Settings
2
14611
by: jld730 | last post by:
Hello All, I wrote Python code to write data to an Excel worksheet1, and also add 5 status types to worksheet2 to be used to make a dropdown list for one column in worksheet1. Could someone help me with the code to do this in Python? I recorded the macro >> Sub Macro2() ' ' Macro2 Macro '
1
1096
Elias Alhanatis
by: Elias Alhanatis | last post by:
Hello to everybody! I have created an app which calculates some statistical data. I use the following function to get Excel to print a page with the data. def Create_Analysis_Page(Name,Job,Phone,Month,Analysing_Dict,preview=False): xlApp = Dispatch ("Excel.Application") xlWb = xlApp.Workbooks.Open ("C:\\Python25\\Misthos.xlsx")
0
1750
by: Belsirk | last post by:
Hi, i'm using C# Visual Studio 2008 for made a apps able to take some information from the app and sending them to a excel template, i'm using Microsoft.Office.Interop.Excel namespace and i don't have problem getting the file and putting the info to the rows, but i'm not getting the correct result because all the values are seen like text. ...
2
2413
by: dubana09 | last post by:
Hi all, I’m relatively new to python. I’m using a process simulation package which is stuck in the past unfortunately – it would not support any version older than python 2.2. This fairly frustrating as you can imagine. This means I cannot use pyExcelerator or xlwt to create spreadsheets and write to excel. Unlike reading (using xlrd), there...
0
7778
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8125
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8290
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8148
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6522
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5664
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3798
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2292
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.