creating excel from sqlsqliquery.com/datastore/iqueryexcel.pdftransforming sql to excel using iquery...
TRANSCRIPT
CreatingExcelfromSQLTransformingSQLtoExcelonIBMi
SQLiQuery.com
iQ©2016CozziProductions,Inc.
with
[email protected]:@bobcozzi
DownloadthisPresentation
• WeborCommandLineSQLprocessor
• PowerfulScriptingToolsupportingIBMi DB2SQL• RunanySQLcommand
• SendSQLSELECTresultstoyourchoiceofoutputformats• CSV,Sylk,JSON,Excel,XML,PDF,Print,Display,etc.
SQL iQueryPowerful Database Query and SQL Scripting for IBM i
© Cozzi Productions, Inc.www.SQLiQuery.com
SQLiQuery.com
iQResultsareblah
TXT
{JSON}CSV
,
Your Data with SQL iQueryYour Data
*WriteoneSQLstatement,outputtoanysupportedformat.
SQL iQuerySQL Processing and Scripting for IBM i
©2016CozziProductions,Inc.
*WriteOneandDone!
RunSQLusingiQuery
• RunSQLfrommanyinterfaces• CommandEntry• CL• MenuOptions• Batch• JobScheduler• YourownWebPage
• Outputtoagrowinglistofmediaformats• Excel,CSV,JSON,HTML,XML,PDF,Print,Display,Db2table,etc.
• EmailingofResultsisavailable• SaveandRecalloftenusedSQLstatements
• AsSourceFileMembers• AsIFSTextFiles
• UsediQueryScripttoenhancesavedSQL
RUNiQRY BasicResults- Display
RUNiQRY SQL('SELECT * FROM QIWS/QCUSTCDT')
RUNiQRY BasicResults- Printed
RUNiQRY SQL('SELECT * FROM QIWS/QCUSTCDT') OUTPUT(*PRINT)
WRKSPLF
TransformingSQLtoExcelusingiQuery
• SQLtoExcelisjustaseasyasstandardoutput,displayorprint.• TheOUTPUTparameterissimplysettoOUTPUT(*EXCEL)
• ThreeExcelformatsaresupported1. ClassicCSV(Commaseparatedvalues)2. SyLK (MultiplanSymbolicLink)(originalExcelformat)3. .XLS(NativeExcelformatinternally:SpreadsheetML format)
• StandardOUTPUT(*EXCEL)formatisusuallygoodenough• Modifications/Customizationsaresupported• UseSQLiQueryScripttoset/savemoreExcelpropertieswhenneeded
SQLtoExcelusingiQuery's RUNiQRY Command
RUNiQRY SQL('SELECT * FROM QIWS/QCUSTCDT') OUTPUT(*EXCEL)EMAIL([email protected]) SUBJECT('SQL to Excel Demo')
CustomizingSQLtoExcelTransformation
• Let'saddcolumntotalstothefinaltwocolumnsintheresultset• Columns10and11(BALDUEandCDTDUE)willbetotaled
• TheCOLTOTALparametersupports• Relativecolumnnumber(10and11)• Columnnames(BALDUE,CDTDUE)
• Iprefertouserelativecolumnnumberswhenexpressionsareusedinthestatement,otherwiseItendtousecolumnnames
• Toaddcolumntotals:• AddtheCOLTOTAL(BALDUECDTDUE)parameterisaddedtotheRUNiQRYcommand
SQLtoExcelusingiQuery's RUNiQRY Command
RUNiQRY SQL('SELECT * FROM QIWS/QCUSTCDT') OUTPUT(*EXCEL)EMAIL([email protected]) SUBJECT('SQL to Excel Demo')COLTOTAL(BALDUE CDTDUE)
SQLiQuery.com
iQResultsareblah
TXT
{JSON}CSV
,
Your Data with SQL iQueryYour Data
*WriteoneSQLstatement,outputtoanysupportedformat.
SQL iQueryTransforming SQL results into the formats your users need
©2016CozziProductions,Inc.
*WriteOneandDone!
