pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedexporting Excel tables into PostgreSQL database with Python
3+ messages / 3 participants
[nested] [flat]
* exporting Excel tables into PostgreSQL database with Python
@ 2005-12-13 10:55 Jürgen Kemeter <juergenkemeter@gmx.de>
0 siblings, 1 reply; 3+ messages in thread
From: Jürgen Kemeter @ 2005-12-13 10:55 UTC (permalink / raw)
To: pgsql-sql
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")
=
* Re: exporting Excel tables into PostgreSQL database with Python
@ 2005-12-13 21:40 Christian Kratzer <ck-lists@cksoft.de>
parent: Jürgen Kemeter <juergenkemeter@gmx.de>
0 siblings, 1 reply; 3+ messages in thread
From: Christian Kratzer @ 2005-12-13 21:40 UTC (permalink / raw)
To: Jürgen Kemeter <juergenkemeter@gmx.de>; +Cc: pgsql-sql
Hi,
On Tue, 13 Dec 2005, Jürgen Kemeter wrote:
> 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.
[snipp]
I am not familiar with python but we have done imports and exports from
and to excel in perl using the Spreadsheet::ParseExcel and
Spreadsheet::WriteExcel modules from CPAN.
Greetings
Christian
--
Christian Kratzer ck@cksoft.de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: exporting Excel tables into PostgreSQL database with Python
@ 2005-12-14 08:49 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Christian Kratzer <ck-lists@cksoft.de>
0 siblings, 0 replies; 3+ messages in thread
From: Achilleus Mantzios @ 2005-12-14 08:49 UTC (permalink / raw)
To: Christian Kratzer <ck@cksoft.de>; +Cc: Jürgen Kemeter <juergenkemeter@gmx.de>; pgsql-sql
O Christian Kratzer Ýãñáøå óôéò Dec 13, 2005 :
> Hi,
>
> On Tue, 13 Dec 2005, Jürgen Kemeter wrote:
> > 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.
> [snipp]
>
> I am not familiar with python but we have done imports and exports from
> and to excel in perl using the Spreadsheet::ParseExcel and
> Spreadsheet::WriteExcel modules from CPAN.
Same here but with java, using jakarta-poi.
>
> Greetings
> Christian
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-14 08:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-13 10:55 exporting Excel tables into PostgreSQL database with Python Jürgen Kemeter <juergenkemeter@gmx.de>
2005-12-13 21:40 ` Christian Kratzer <ck-lists@cksoft.de>
2005-12-14 08:49 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
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