Re: IBM i database to Excel spreadsheet

Someone had mentioned SQL2JXL - we have used that for many jobs here - it can write only to XLS, as it uses a Java class that is not being updated to put out XLSX.

Giuseppe Costagliola created this, as well as SQL2POI (I think is the name).

This command takes a SELECT statement and calls a single Java class that uses another Java thing that is like POI - so RPG or CL makes only 1 call to Java.

It can use an existing XLS file as a template, to provide a heading and column headings. Example that Giuseppe gives shoe even more complex results, and I've not learned how to do that - haven't tried, actually.

But for a quick data dump with some formatting capability, his commands can be fine. The POI version might be modifiable - he provides the source, as I recall.

We use another commercial product at times - SEQUEL - it can produce either XLS or XLSX - it is a pretty basic data dump if you just give it a SELECT and run its EXECUTE command. You can get better results if you create one of their VIEWs.

Lots of options, including roll your own - but I do that with a method using CGIDEV2, which is better suited to a specific kind of report, not a generic data dump.

HTH
Vern

On 12/1/2016 12:46 AM, Gad Miron wrote:

Hello Elden

We use GUMBO's Excel-erator <http://www.gumbo.com/Licpgm/Excelerator.html> for
several years and it works fine for us.
See http://www.gumbo.com/

Regards
Gad



date: Wed, 30 Nov 2016 09:12:21 -0800

from: Elden Fenison <Elden.Fenison@xxxxxxxxxxxxxxxxx>
subject: IBM i database to Excel spreadsheet

I'd like to figure out a better way to get data from an IBM i database
file into an Excel spreadsheet.

We are currently using an open source tool apparently written by Hideyuki
Yahagi. It will take an IBM i database and create a CSV file. We land that
on a QNTC file system (on a Windows server) where the user goes and grabs
it. The user then imports the CSV file into an XLS file via Excel.

The problem we face is that the CSV format doesn't retain any real
information with regards to column formatting. So when we try to import the
CSV to an XLS, Excel tries to guess about the column formatting to where
fields that appear that they could be valid dates (but are not) are brought
in as a date data type. The only way we've found around this is to tell the
import process what the format should be for each column, every time we
need to import from CSV to Excel. We think that exporting directly from the
IBM i database to an XLS format might be allow us to retain the column
formatting so that we don't have to go through all those hoops.

So I am seeking a way to go directly from the IBM i database to an Excel
spreadsheet. If there are no free solutions, we would definitely consider a
paid solution.

Ideas?

Elden Fenison
Programmer/Analyst
Murphy Company