How to Download IBM i SQL Results As CSV
I had to run a rather interesting SQL statement to download journal information:
select entry_timestamp, journal_code, journal_entry_type, job_name, job_user, job_number, substr(object, 1, 10), substr(object, 11, 10), substr(object, 21, 10), program_name, cast(entry_data as char(2000)), sequence_number from table ( display_journal('JRNLIBNAME','JOURNALNAME', journal_codes => 'R', object_library => 'LIBRARYNAME', object_name => 'FILENAME', object_member => 'MEMBERNAME', object_objtype => '*FILE') ) as x -- WHERE SUBSTR(CHAR(ENTRY_TIMESTAMP),1,10) = :CheckDate order by sequence_number;
What does this do?
Essentially, this SQL snippet retrieves journal entry information for the given journal and the file that is journaled.
Running this in the IBM i ACS SQL script explorer gives you the results in a nice spreadsheet friendly format:
The trouble is – you cannot download those results to a spreadsheet by default!
Luckily, you can turn on ACS’s Spreadsheet download ability in a couple of easy steps.
First, load all the SQL results into the results’ area, ready for download.
Enable the Spreadsheet download function
Goto PREFERENCES, then enable saving of results using scrollable cursors
Stop and restart the SQL tools - which means CLOSE THE WINDOW ;)
Now you have restarted the SQL Explorer, you can run the SQL command again, and you will now see a new download option under your RIGHT CLICK menu.
By the Way – By default, the screen only shows a sample of the file data… but you can click the little box in the bottom right to retrieve all data to the screen, so you can view before download. When you click download, all the data will be retrieved anyway – but this little click is useful, so you can see what you are getting before you get it.
Hover over this, and it says “retrieve all rows”
Right Click inside the results, and you will see a new menu option:
SAVE RESULTS – as a spreadsheet
Download those results as an Excel Spreadsheet!
Which will look something like this.
You don’t have to perform the extra step of retrieving all rows. If you right-click and save the data, all rows will be retrieved automatically.
You are right, of course – my text was not clear on this. #duh
I just updated it to hopefully make more sense. Thanks!
how do I Stop and restart the SQL tools?
Just click the little RED X to close the window and restart it.