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
