SQL2XLS - Run sql and export output to excel

page d'accueil
Boite à outils

Cette page a été mise à jour le16 Juillet 2010
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 : How to upload to my 400

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.

Compile SQL2XLS (deprecated)
CHGPFM FILE(JPLTOOLS) MBR(XMLDB2P ) SRCTYPE(PF ) EXPDATE(*NONE) SHARE(*NO) TEXT('Structure hierarchique DB2 pour générer du XML ')
CHGPFM FILE(JPLTOOLS) MBR(XMLDB2R ) SRCTYPE(SQLRPGLE ) EXPDATE(*NONE) SHARE(*NO) TEXT('extract XML data from workfile ')
CHGPFM FILE(JPLTOOLS) MBR(SQL2XLS ) SRCTYPE(CMD ) EXPDATE(*NONE) SHARE(*NO) TEXT('exporter des données SQL en Excel SSML ')
CHGPFM FILE(JPLTOOLS) MBR(SQL2XLSC ) SRCTYPE(CLP ) EXPDATE(*NONE) SHARE(*NO) TEXT('exporter des données SQL en Excel SSML ')
CHGPFM FILE(JPLTOOLS) MBR(SQL2XLSR ) SRCTYPE(SQLRPGLE ) EXPDATE(*NONE) SHARE(*NO) TEXT('exporter des données SQL en Excel SSML ')
DLTF FILE(XMLDB2P)
CRTPF SRCFILE(JPLTOOLS) SRCMBR(XMLDB2P) FILE(XMLDB2P) size(*nomax)
dltPF FILE(XMLDB2W)
CRTPF FILE(XMLDB2W) RCDLEN(1000)
CRTSQLRPGI OBJ(XMLDB2R) SRCFILE(JPLTOOLS) COMMIT(*NONE)OBJTYPE(*MODULE) OPTION(*NOSEQSRC *XREF *SECLVL) CLOSQLCSR(*ENDMOD) DBGVIEW(*SOURCE) COMPILEOPT('enbpfrcol(*full)')
CRTPGM PGM(XMLDB2R) BNDDIR(QC2LE) OPTION(*DUPPROC *DUPVAR)
CRTCMD CMD(SQL2XLS) PGM(*LIBL/SQL2XLSc) SRCFILE(JPLTOOLS) SRCMBR(SQL2XLS) THDSAFE(*YES) REPLACE(*YES)
CRTCLPGM PGM(SQL2XLSC) SRCFILE(JPLTOOLS) USRPRF(*USER) REPLACE(*YES)
CRTSQLRPGI OBJ(SQL2XLSR) SRCFILE(JPLTOOLS) COMMIT(*NONE) OBJTYPE(*MODULE) OPTION(*NOSEQSRC *XREF *SECLVL)CLOSQLCSR(*ENDMOD) DBGVIEW(*SOURCE) COMPILEOPT('enbpfrcol(*full)')
CRTPGM PGM(SQL2XLSR) BNDDIR(QC2LE) OPTION(*DUPPROC *DUPVAR)

Compile SQL2XLS3

/*=-----*CURLIB/JPLTOOLS(SQL2XLS3)------------------------=*/
CHGJOB CCSID(37)

CHGCURLIB CURLIB(JPLTOOLS)

CHGPFM FILE(JPLTOOLS) MBR(SQL2XLS3) SRCTYPE(CMD) TEXT('exporter des données SQL en Excel SSML') EXPDATE(*NONE) SHARE(*NO)

CRTCMD CMD(SQL2XLS3) PGM(SQL2XLS3C) SRCFILE(JPLTOOLS) PRDLIB(JPLTOOLS)


/*=-----*CURLIB/JPLTOOLS(SQL2XLS3C)------------------------=*/
CHGJOB CCSID(37)

CHGCURLIB CURLIB(JPLTOOLS)

CHGPFM FILE(JPLTOOLS) MBR(SQL2XLS3C) SRCTYPE(CLLE) TEXT('exporter des données SQL en Excel SSML') EXPDATE(*NONE) SHARE(*NO)

CRTCLMOD MODULE(SQL2XLS3C) SRCFILE(JPLTOOLS) TGTRLS(V5R3M0) DBGVIEW(*ALL) ENBPFRCOL(*FULL)

CRTPGM PGM(SQL2XLS3C) BNDDIR(QC2LE JPLTOOLS) ACTGRP(JPLTOOLS) TGTRLS(V5R3M0)


/*=-----*CURLIB/JPLTOOLS(SQL2XLS3R)------------------------=*/
CHGJOB CCSID(37)

CHGCURLIB CURLIB(JPLTOOLS)

CHGPFM FILE(JPLTOOLS) MBR(SQL2XLS3R) SRCTYPE(SQLRPGLE) TEXT('exporter des données SQL en Excel SSML') EXPDATE(*NONE) SHARE(*NO)

EXECSQL3 LIBRARY(JPLTOOLS) FILE(JPLTOOLS) MEMBER(SQL2XLS3Q) JOBLOG(*YES)

Note: if you have not already compiled EXECSQL3, you can just use STRSQL to execute the SQL in SQL2XLS3Q

CRTSQLRPGI OBJ(SQL2XLS3R) SRCFILE(JPLTOOLS) OBJTYPE(*MODULE) OUTPUT(*PRINT) OPTION(*NOSEQSRC *XREF *SECLVL) RPGPPOPT(*LVL2) TGTRLS(V5R3M0) DBGVIEW(*SOURCE) COMPILEOPT('ENBPFRCOL(*full)')

CRTPGM PGM(SQL2XLS3R) BNDDIR(QC2LE JPLTOOLS) ACTGRP(JPLTOOLS) TGTRLS(V5R3M0)

 sql2xls3.xls a sample of XLS file with unicode characters inside

The source code is provided as an open source tool under the GNU General Public License, version 2.  In summary, this license specifies:

By clicking the download link you acknowledge and agree to the terms of the license.

Download the (zipped) SAVF containing the JPLTOOLS : the source file, the message file, the bnddir

THIS TOOLBOX IS PROVIDED ''AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE PROVIDER OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS TOOLBOX, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.