TechTip: Excel on the Fly

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly **
What were your findings when comparing RPG against the pure Java version you provided?

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly **
Giuseppe, When we run the following statement we get an error (listed under the statement). SQL2XLS SQLSTMT('select baldue from qcustcdt') TOXLS('/home/rshaler/test.xls') Error message below: Cause . . . . . : An incorrect value... parameters: -- Function -- Commitment control -- Date format -- Date separator -- Time format -- Time separator -- Naming option -- DESCRIBE option -- Allow copy data Recovery . . . : Change the incorrect parameter value and try the request again. Refer to the description of the QSQPRCED API for a list of the valid parameter values.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly **
Richard, there might be a limit on the number of bytes that you can pass, so if your file is overly large. You might want to give more selection criteria to your SQL stmt, as it would narrow down the number of hits you get. Just an idea, until Guiseppe gets back to you. We send data to excel spreadsheets all the time and I remember when we were setting up a command, that we ran into this limit. We were do IFS folder processing. If the .csv is too large, it can't import into excel.

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly **
Hi Richard, take a look at the parameters in the Function template "sqlp0100". Some of these have been set for Europe. Maybe you should change: "datefmt", "datesep" "timefmt", "timesep", "decpos" according to your locale.

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly **
I’ve tested on my iSeries “private” partition (a very little one – where the poi jar compile takes 1hr and 25 minutes !!). The following timings are not meaningful by theirself, but they can be useful for comparison. Maybe you ran the test on a powerful machine and would give us the results. Both db2_classes.jar and POI-2.0.jar have been compiled at 40 before running the test. Best elapsed: ===> SQL2XLS SQLSTMT('SELECT DBXFIL, DBXLIB, DBXTXT, DBXATR FROM QADBXREF') TOXLS('/home/costagliol/dbref.xls') COLHDRS(*FLDNAM) 3.500 rows -> 2’10’’ ===> RUNJVA CLASS(JSQL2XLS) PARM('SELECT DBXFIL, DBXLIB, DBXTXT, DBXATR FROM QSYS.QADBXREF') CLASSPATH('/home/costagliol/:/home/costagliol/db2_classes.jar:/excel/poi-2.0.jar') 3.500 rows -> 1’15’’ The “pure java” version wins, as expected; however I think that most of as/400 programmers feel more confortable with rpg than java, then they would accept to wait a little bit more to get it.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly **
Well, I think if IBM can fix the CPYTOIMPF and have the first line the column headings, it will be very easy to send db2 to Excel format. Tiki

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly **
With CPYTOIMPF you create a .csv that's another thing. However there are free utilities in internet that allow you to create .csv with column headings.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly **
One thing I noticed. When trying to move a newly created file with OPS Navigator, I get the message that the file is being used. I can map a drive and open the file with EXCEL. I can also save the file. But, OPS Nav thinks the file is being used.

[gcostagliola@tin.it]

** This thread discusses the article: TechTip: Excel on the Fly **
If you have the folder in share, you can even delete it from windows explorer immediately after it has been created; from wrklink you can also delete it. I think it's only a problem of OPSnav.

[Guest.Visitor]

** This thread discusses the article: TechTip: Excel on the Fly **
Can anyone point me in the right direction for the free utilities to create column headings in CSV files? I really need this....thanks