iQuery

www.cozTools.com

www.MidrangeNews.com

Contact Us

COZTOOLS

SQL iQuery for IBM i - Excel Support Page

SQL iQuery Supports 3 Excel Output Choices

Exporting IBM i db2 database files to Excel has been around for more than two decades. Today the most popular format to use is CSV (Comma Separated Values) for export from Db2 into Excel, followed by the ODBC driver available to Excel Users for importing data directly from the IBM i system.

SQL iQuery supports exporting to Excel in any of three different formats:

  1. OUTPUT(*EXCEL) - Outputs Open Source SpreadSheetML format as an XML file with a .XLS suffix. This format is supported by most non-Excel spreadsheet programs, except Apple's "Numbers" app.
  2. OUTPUT(*CSV) - The classic Comma Separated Values format.
  3. OUTPUT(*SLK | *SYLK) produces a file type of "SyLK" or "Symbolic Link" which is a native Excel/Microsoft format for interchange with other spreadsheets. Use this format when you want better results than plain CSV files. Advantages over CSV include Bold Fonts, column totals, and proper numeric and date results.

CSV files may be imported into Excel, and under Windows if you double-click on the CSV file, it will launch Excel and automatically import the content. This assumes you have Microsoft Office/Excel installed.

To use industry standard SQL and output CSV, use our RUNIQRY command as follows:

==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE

                 FROM QIWS/QCUSTCDT') OUTPUT(*CSV) 

OUTPUT(*CSV) Result:

12 rows selected.
12 records + 1 heading lines written to /home/cozzi/QCUSTCDT.CSV

Viewing the results on the IFS, looks like this:

By default, SQL iQuery creates a CSV file with the same name as the primary or "base" database file name. It adds the .CSV suffix and stores it on the IFS in the User's HOME directory. In our example, if user COZZI was running the command the output file name would be: /home/cozzi/qcustcdt.csv but you could override that name using the STMF and STMFNAME parameters of the RUNIQRY command.

Column Totals

There is also a hidden feature in SQL iQuery for CSV (and Excel) output. The COLTOTAL (Column Total) parameter allows users to embed a column total Excel function directly into the CSV file. For example, to total up the BALDUE column include COLTOTAL(BALDUE) on the RUNIQRY command. If the column being totaled is a derived column, specify the relative column number (6 in our example) instead of the field name. It embeds the =SUM() function into an addition row added to the CSV file. Then when Excel imports the CSV file, it will run that function and total up the cell as expected.

*EXCEL/SyLK - A Native but limited Excel Output Option

While the format of native Excel files has been seemingly ever-changing, (from DIFF, to XLS, to XLSX and others) we decided that a good fit for Db2 database to Excel conversion was the Symbolic Link or Sylk format. That Sylk format is a text-based format that is compatible with all versions of Excel. So there's no backwards compatibility issues as often occurs with XML, contemporary XLSX or XLS binary formats. And, similar to CSV, Sylk files may be opened in a text editor and when double-clicked, open automatically in Excel. But there are more advantages.

Sylk Files may contain formatting for Excel Cells or columns. This means we can embed things like real Excel Date values, numeric fields, and even bold headings directly from SQL output. No additional end-user formatting required. Our users always know when they receive an Excel file created by SQL iQuery because it looks better.

 To use industry standard SQL to create an Excel Sylk file, include the OUTPUT(*EXCEL) option on the RUNIQRY command:

==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE

                 FROM QIWS/QCUSTCDT') OUTPUT(*EXCEL) 

OUTPUT(*EXCEL) Result:

While this is a text file, it is a bit unreadable for humans compared to CSV; but the good news is it is much nicer looking when actually loaded into Excel.

Copy the output file to your Windows or Mac environment. You can also drag the file to the desktop from the HOME folder of the IFS. Here is what the desktop icon would look like for our example QCUSTCDT file output as Sylk/Excel format:

Notice that the Excel Icon is actually used. This is because all version of Excel (and by all, we me even the old Windows 3.0 version of Excel) recognize Sylk files and automatically open it as a native format. Once the user has open the file by double-clicking on it, it would appear as follows:

The above image was take immediately after opening the file, no reformatting is done at all. Note the column headings are included and in bold; numeric fields appear correctly. Also, DB2 DATE fields are converted into Excel date integers and formatted as a date so you rarely have to screw around with the dates after sending them off to Excel.

Check out SQL iQuery for yourself. Download our free trial today.

Copyright © 2014 Cozzi Research All Rights Reserved.