SQL2XLS - Run sql and export output to excel

page d'accueil
Boite � outils


Contact

Welcome page
Tools box

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) 

Type choices, press Enter.

SQL statement . . . . . . . . . > 'select * from qiws/qcustcdt'
...
Show NULL as . . . . . . . . . . '#N/A'
IFS root . . . . . . . . . . . . > HOME
IFS folder . . . . . . . . . . . > LAMONTRE
IFS file . . . . . . . . . . . . 'sql2xls3.xls'
PUBLIC data authority . . . . . *RWX *SAME, *NONE, *RWX, *RX...
PUBLIC object authority . . . . *ALL *NONE, *ALL, *OBJEXIST...

.

This tool is based on SSML, the SpreadSheet XML from Office 11, that is a little more pretty than any spool. 

Features:

  • Style of cell: of text, of number, of date, of hour 
  • Police (font, size, bold, italic, underlined) 
  • Border and grey 
  • Format of cell: left, centered, right 
  • height of line 
  • width of column 
  • colors
  • headers (line 1) freezed
  • supports all SBCS, all DBCS & all UNICODE characters simultaneously

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