How to Download IBM i SQL Results As CSV

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 because it only loads a page at a time. You could sit and press PAGE DOWN… PAGE DOWN… PAGE DOWN… ad infinitum

Or 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.

ba-da-sql-bing