SQL2XLS - Run sql and export output to excel
Contact |
Target :
convert a SQL statement to an EXCEL file.
Characteristics :
RPG and CL only, no JAVA.
What will not do :
Write data back to the DB2 database. My client's dabatase are not
enough strong to support direct insert or update from Excel.
But, as
it's just an XML file, (m)any XML tool can read it. And there
are some for RPG.
What could do :
Could be recompiled as a service program. I've received a mail from a
team that did the job to use SQL2XLS from a Cobol program.
Could
implement more parts of the XLXML dialect, for example to build Pivot
tables.
Sample :
select * from qiws/qcustcdt
Green screen interface :
Export SQL to Excel (SQL2XLS3) |
.
This tool is based on SSML, the
SpreadSheet XML from Office 11, that is a little more pretty than
any
spool.
Features:
|
When using the unicode version, be carefull to have your PC configured for Unicode
If you can show this with Iseries Navigator :
MISSING : show how to create and load a multi-ccsid PF ; use same file to show with Navigator and SQL2XLS3
SQL2XLS3 can copy it into Excel :sql2xls3.xls
MISSING insert here a print-screen in english : configuration panel / localisation / language / add all supplemental language
Start / Configuration panel
localisation
Take care to have a font that supports unicode, there is one in MS Office
http://en.wikipedia.org/wiki/Help:Multilingual_support
Most computers with Microsoft Windows or Microsoft Office will already have several fonts with support for Latin, Greek, Cyrillic, Hebrew, Arabic, Chinese, Japanese, Korean and the International Phonetic Alphabet installed. Several historic and accented characters (used in the transliteration of foreign scripts) are missing, though. |
Versionning
last version : The SaveFiles
There is an overhead to use the UNICODE version. Say +25 % CPU and +80% elaps. I've analyzed the two version in same conditions with PEX, but don't find reason for elaps overhead. The CPU overhead is due to the conversions tasks. Converting to Unicode is not cheap : but the world is a small village.
sql2xls3.xls a sample of XLS file with unicode characters inside