From: Jürgen Kemeter <juergenkemeter@gmx.de>
To: pgsql-sql@postgresql.org
Subject: exporting Excel tables into PostgreSQL database with Python
Date: Tue, 13 Dec 2005 23:55:49 +1300
Message-ID: <000601c5ffd3$c9a80020$6106a8c0@samsungx30> (raw)
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
Attachments:VBAForCommentAndHyperlink.txt (text/plain, 952B) download
'----------------------------------------------------------------------------------------------------------------
'Extract the text from a cell comment
'----------------------------------------------------------------------------------------------------------------
Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
On Error Resume Next
strGotIt = WorksheetFunction.Clean _
(rCommentCell.Comment.Text)
GetCommentText = strGotIt
On Error GoTo 0
End Function
'----------------------------------------------------------------------------------------------------------------
'Extract the underlying address from a cell containing a Hyperlink
'----------------------------------------------------------------------------------------------------------------
Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function
Gerold.py (text/plain, 766B) download
#!/usr/bin/env python
# -*- coding: iso-8859-1 -*-
import win32com.client
import win32ui
# Zum Excel verbinden
app = win32com.client.Dispatch("Excel.Application")
app.visible = True
# Neue Arbeitsmappe
workbook = app.Workbooks.Add()
# Zum Arbeitsblatt verbinden
sheet = workbook.Sheets[0]
# Kommentare hinzufügen
sheet.Range("A1").AddComment()
sheet.Range("A1").Comment.Text("Hallo")
sheet.Range("B2").AddComment()
sheet.Range("B2").Comment.Text("Welt")
# Kommentare auslesen und anzeigen
win32ui.MessageBox(
"Kommentar in A1: %s" % sheet.Range("A1").Comment.Text()
)
win32ui.MessageBox(
"Kommentar in B2: %s" % sheet.Range("B2").Comment.Text()
)
# Variablen löschen und damit die Verbindung zu Excel lösen
del sheet
del app
PostgreSQLConnection.py (text/plain, 1013B) download
from pyPgSQL import PgSQL
class dbConn:
def __init__(self, **kwargs):
self.conn = None
self.conndata = kwargs
self.dbname = self.conndata['db']
self.PgSQL = PgSQL
self.connectPostGreSQL()
def connectPostGreSQL(self):
self.conn = self.PgSQL.connect( host=self.conndata['host'], \
database=self.conndata['db'], \
user=self.conndata['user'], \
password = self.conndata['passwd'])
self.cursor = self.conn.cursor()
# So und für die restliche SQL Syntax von Postgresql sollte man sich ein Buch holen, da
# so Befehle wie concat, die man aus mySql her kennt, nicht funktionieren.
# Hier muss man den Befehl umbauen und Concatinieren mit || . Nur als ein Beispiel
# von einigen.
newDB = dbConn(host="localhost", user="user1", passwd="", db="testDB")
=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: juergenkemeter@gmx.de
Subject: Re: exporting Excel tables into PostgreSQL database with Python
In-Reply-To: <000601c5ffd3$c9a80020$6106a8c0@samsungx30>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox