X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 6FDC09DC81A for ; Tue, 13 Dec 2005 06:56:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 11159-04 for ; Tue, 13 Dec 2005 06:56:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.gmx.net (mail.gmx.net [213.165.64.21]) by postgresql.org (Postfix) with SMTP id 17BBC9DC803 for ; Tue, 13 Dec 2005 06:55:57 -0400 (AST) Received: (qmail invoked by alias); 13 Dec 2005 10:55:53 -0000 Received: from 203-173-160-11.bliink.ihug.co.nz (EHLO samsungx30) [203.173.160.11] by mail.gmx.net (mp029) with SMTP; 13 Dec 2005 11:55:53 +0100 X-Authenticated: #9172198 From: =?iso-8859-1?Q?J=FCrgen_Kemeter?= To: Subject: exporting Excel tables into PostgreSQL database with Python Date: Tue, 13 Dec 2005 23:55:49 +1300 Message-ID: <000601c5ffd3$c9a80020$6106a8c0@samsungx30> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0007_01C60040.C0A14820" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Y-GMX-Trusted: 0 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200512/95 X-Sequence-Number: 23637 This is a multi-part message in MIME format. ------=_NextPart_000_0007_01C60040.C0A14820 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0008_01C60040.C0A2CEC0" ------=_NextPart_001_0008_01C60040.C0A2CEC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi! Here is a hopefully convenient description of my situation: - I have a main folder, containing several subfolders. - Every (sub)folder contains one or more .xls - Workbooks. - Every Workbook contains one or more different Spreadsheets. - The workbooks contain some cells which have Hyperlink addresses to other, relating workbooks. - Some cells in wokrbooks contain comments, which must also be exported. My ultimate aim is to get ALL data, that means cell values, hyperlink addresses in cells, and comments, into corresponding PostgreSQL database table. Principally there are at maximum three different data per cell (value, hyperlink address, comment). Here is my idea., which I lack of Python programming practice to implement in a reasonable amount of time, so every help is welcome: Some kind of For - Loop, which looks into all folders and subfolders. Then opens all workbooks one after another. Then looks into every spreadsheet of a workbook. For every spreadsheet, read out cell values, cell hyperlink addresses (if there are any), and cell comments (if there are any). (Perhaps the win32com - module is helpful?) Then open a PostgreSQL database connection, and insert the cell values into a corresponding table. (which should already be created in the database before?) This table should also contain a column for the possible hyperlink addresses, and possible cell comment strings. I have enclosed various Code which might be of help: - VBA Code samples for reading a cell comment and a hyperlink address from a spreadsheet cell. - PostgreSQLConnection.py, a class to connect to a PostgreSQL database - Gerold.py, which uses the win32com - module to access and manipulate an Excel workbook. My problem is how to combine these code samples, especially implementing the for-loop for going through the folders and opening Excel workbooks/spreadsheets, reading Excel data with win32com, and creating/inserting this data in corresonding tables in the database, using pyPgSQL. So, this sounds like fun work... any help appreciated. Cheers Juergen ------=_NextPart_001_0008_01C60040.C0A2CEC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi!

 

Here is a hopefully convenient description of my situation:

- I have a main folder, = containing several subfolders.

- Every (sub)folder contains one or more .xls - Workbooks.

- Every Workbook contains one or = more different Spreadsheets.

- The workbooks contain some = cells which have Hyperlink addresses to other,

relating = workbooks.

- Some cells in wokrbooks contain comments, which must also be exported.

 

My ultimate aim is to get ALL = data, that means cell values, = hyperlink

addresses in cells, and comments, into corresponding PostgreSQL database

table. Principally there are at = maximum three different data per cell

(value, hyperlink address, comment).

 

Here is my idea., which I lack of Python programming practice to = implement

in a reasonable amount of time, so = every help is welcome:

Some kind of = For - Loop, which looks = into all folders and subfolders. Then

opens all workbooks one after another. = Then looks into every spreadsheet of

a workbook. For every spreadsheet, = read out cell values, cell hyperlink

addresses (if there are any), and cell = comments (if there are any).

(Perhaps the win32com - module is helpful?)

 

Then open a PostgreSQL database connection, and insert the cell values into

a corresponding table. =

(which = should already be created in the database before?) =

This table should also contain a = column for the possible hyperlink

addresses, and possible cell comment = strings.

 

I have enclosed various Code which might be of = help:

- VBA Code samples for reading a = cell comment and a hyperlink address from a

spreadsheet = cell.

- PostgreSQLConnection.py, a = class to connect to a PostgreSQL database

- Gerold.py, which uses the = win32com - module to access and manipulate an

Excel = workbook.

 

My problem is how to combine = these code samples, especially implementing the

for-loop for going through the folders = and opening Excel

workbooks/spreadsheets= , reading Excel data with = win32com, and

creating/inserting this data in corresonding tables = in the database, using

pyPgSQL.

 

So, this sounds like fun work... = any help appreciated.

Cheers

Juergen

 

 

------=_NextPart_001_0008_01C60040.C0A2CEC0-- ------=_NextPart_000_0007_01C60040.C0A14820 Content-Type: text/plain; name="VBAForCommentAndHyperlink.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="VBAForCommentAndHyperlink.txt" '------------------------------------------------------------------------= ---------------------------------------- 'Extract the text from a cell comment '------------------------------------------------------------------------= ---------------------------------------- Function GetCommentText(rCommentCell As Range) Dim strGotIt As String On Error Resume Next strGotIt =3D WorksheetFunction.Clean _ (rCommentCell.Comment.Text) GetCommentText =3D strGotIt On Error GoTo 0 End Function '------------------------------------------------------------------------= ---------------------------------------- 'Extract the underlying address from a cell containing a Hyperlink '------------------------------------------------------------------------= ---------------------------------------- Function GetAddress(HyperlinkCell As Range) GetAddress =3D Replace _ (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "") End Function ------=_NextPart_000_0007_01C60040.C0A14820 Content-Type: text/plain; name="Gerold.py" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="Gerold.py" #!/usr/bin/env python=20 # -*- coding: iso-8859-1 -*-=20 import win32com.client=20 import win32ui=20 # Zum Excel verbinden=20 app =3D win32com.client.Dispatch("Excel.Application")=20 app.visible =3D True=20 # Neue Arbeitsmappe=20 workbook =3D app.Workbooks.Add()=20 # Zum Arbeitsblatt verbinden=20 sheet =3D workbook.Sheets[0]=20 # Kommentare hinzuf=FCgen=20 sheet.Range("A1").AddComment()=20 sheet.Range("A1").Comment.Text("Hallo")=20 sheet.Range("B2").AddComment()=20 sheet.Range("B2").Comment.Text("Welt")=20 # Kommentare auslesen und anzeigen=20 win32ui.MessageBox(=20 "Kommentar in A1: %s" % sheet.Range("A1").Comment.Text()=20 )=20 win32ui.MessageBox(=20 "Kommentar in B2: %s" % sheet.Range("B2").Comment.Text()=20 )=20 # Variablen l=F6schen und damit die Verbindung zu Excel l=F6sen=20 del sheet=20 del app=20 ------=_NextPart_000_0007_01C60040.C0A14820 Content-Type: text/plain; name="PostgreSQLConnection.py" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="PostgreSQLConnection.py" from pyPgSQL import PgSQL =0A= =0A= class dbConn: =0A= def __init__(self, **kwargs): =0A= self.conn =3D None =0A= self.conndata =3D kwargs =0A= self.dbname =3D self.conndata['db'] =0A= =0A= self.PgSQL =3D PgSQL =0A= self.connectPostGreSQL() =0A= =0A= =0A= def connectPostGreSQL(self): =0A= self.conn =3D self.PgSQL.connect( host=3Dself.conndata['host'], = \ =0A= = database=3Dself.conndata['db'], \ =0A= = user=3Dself.conndata['user'], \ =0A= password =3D = self.conndata['passwd']) =0A= self.cursor =3D self.conn.cursor() =0A= =0A= # So und f=FCr die restliche SQL Syntax von Postgresql sollte man = sich ein Buch holen, da =0A= # so Befehle wie concat, die man aus mySql her kennt, nicht = funktionieren. =0A= # Hier muss man den Befehl umbauen und Concatinieren mit || . Nur = als ein Beispiel =0A= # von einigen. =0A= =0A= newDB =3D dbConn(host=3D"localhost", user=3D"user1", passwd=3D"", = db=3D"testDB") =0A= ------=_NextPart_000_0007_01C60040.C0A14820--