exporting Excel tables into PostgreSQL database with Python - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">Hi!</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">Here is a hopefully convenient description of my situation:</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">- I have a main folder, containing several subfolders. </span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">- Every (sub<span class="GramE">)folder</span> contains one or more .xls -
Workbooks.</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">- Every Workbook contains one or more different Spreadsheets.</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">- The workbooks contain some cells which have Hyperlink addresses to
other,</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span
class="GramE"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">relating</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> workbooks.</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">- Some cells in wokrbooks contain comments, which must also be
exported.</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">My ultimate aim is to get ALL <span class="GramE">data, that</span> means cell
values,hyperlink</span></font><p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span
class="GramE"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">addresses</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> in cells, and comments, into corresponding PostgreSQL database</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><span class="GramE"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">table</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">. Principally there are at maximum three different data per cell</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">(<span class="GramE">value</span>, hyperlink address, comment). </span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">Here is my <span class="GramE">idea.,</span> which I lack of Python programming
practiceto implement</span></font><p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span
class="GramE"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">in</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> a reasonable amount of time, so every help is welcome:</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><span class="GramE"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">Some kind of For - </span></font></span><span
class="GramE"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";mso-ansi-language:EN-US">Loop</span></font></span><spanclass="GramE"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">, which looks into all folders and
subfolders.</span></font></span><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";mso-ansi-language: 
EN-US"> Then</span></font><p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span
class="GramE"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">opens</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> all workbooks one after another. Then looks into every spreadsheet
of</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span class="GramE"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">a</span></font></span><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> workbook. For every spreadsheet, read out cell values, cell
hyperlink</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span
class="GramE"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">addresses</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> (if there are any), and cell comments (if there are any).</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">(Perhaps the win32com - module is helpful?)</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">Then open a PostgreSQL database connection, and insert the cell values
into</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span class="GramE"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">a</span></font></span><font face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> corresponding table. </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">(<span class="GramE">which</span> should already be created in the database
before?)</span></font><p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">This table should also contain a column for the possible
hyperlink</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span
class="GramE"><fontface="Courier New" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">addresses</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">, and possible cell comment strings.</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">I have enclosed various <span class="GramE">Code</span> which might be of
help:</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">- VBA Code samples for reading a cell comment and a hyperlink address from
a</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span class="GramE"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">spreadsheet</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> cell.</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">- PostgreSQLConnection.py, a class to connect to a PostgreSQL
database</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">- Gerold.py, which uses the win32com - module to access and manipulate
an</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">Excel workbook.</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">My problem is how to combine these code samples, especially implementing
the</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><span class="GramE"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">for-loop</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> for going through the folders and opening Excel</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><span class="GramE"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">workbooks/spreadsheets</span></font></span><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">, reading Excel data with win32com, and</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><spanclass="GramE"><font face="Courier New" size="2"><span
lang="EN-US"style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">creating/inserting</span></font></span><font face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US"> this data in corresonding tables in the database, using</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><span class="GramE"><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:"Courier New";mso-ansi-language:EN-US">pyPgSQL</span></font></span><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family: 
"Courier New";mso-ansi-language:EN-US">.</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:
"Courier New";mso-ansi-language:EN-US">So, this sounds like fun work... any help appreciated.</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><span class="SpellE"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New"">Cheers</span></font></span><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""></span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><spanclass="SpellE"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">Juergen</span></font></span><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""></span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font></div>