March 18

4 comments

How to Download IBM i SQL Results As CSV

By NickLitten

March 18, 2022

sql, excel, IBM i, results, spreadsheet

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:

How to Download IBM i SQL Results As CSV 1

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

How to Download IBM i SQL Results As CSV 2
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”

view all data with IBM i SQL Tool
IBM i SQL Tool showing ALL DATA

Right Click inside the results, and you will see a new menu option:

SAVE RESULTS – as a spreadsheet

How to Download IBM i SQL Results As CSV 3

Download those results as an Excel Spreadsheet!

Which will look something like this.

How to Download IBM i SQL Results As CSV 4

ba-da-sql-bing

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

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >