Rocket U2 | UniVerse & UniData

 View Only
Expand all | Collapse all

Is there a way I can output to excel in universe retrieve sentence?

  • 1.  Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-13-2021 22:46
    Hi there,

    Our core banking is running on universe 11.2.5 and have written a couple of reports as paragraphs but currently outputting them on screen and to printer using LPTR.
    Is there any functions or programs I can send the output straight into excel.

    Note I do not want to spool the report. I want as user runs the paragraph or the retrieve sentence it should outputs to excel on the users pc..

    Ant help/advise.

    Cheers!

    ------------------------------
    Francis Aquila
    Core Banking Support Officer
    TISA
    Papua New Guinea
    ------------------------------


  • 2.  RE: Is there a way I can output to excel in universe retrieve sentence?

    ROCKETEER
    Posted 04-14-2021 04:28
    Several options come to mind for you

    You can use the XML capabilities to UniVerse to produce an XML file that Excel can read, refer to XML sections of the Using Retrive Guide for UniVerse for more detail on this.

    From our previous conversations I recall you use Wintegrate and this gives you access to a host of other options to put data into Excel, these include the File Export option from the Run Option at the top of screen that allows many formats to be converted to including comma delimited, there is also a Query Builder from the same Run menu that allows many output options, there are also wintegrate api's allowing you to write directly to a excel spreadsheet. Please refer to the Wintegrate manuals for more information on these options.

    Regards,
    Jonathan

    ------------------------------
    Jonathan Smith
    UniData ATS and Principal Engineer
    Rocket Software
    ------------------------------



  • 3.  RE: Is there a way I can output to excel in universe retrieve sentence?

    ROCKETEER
    Posted 04-14-2021 04:34
    An simple example of XML is like this

    LIST CUSTOMER FULLNAME FULLADDR TOXML

    <?xml version="1.0" encoding="UTF-8"?>
    <ROOT>
    <CUSTOMER _ID = "2" FULLNAME = "Ms. Diana Morris" FULLADDR = "431 Third Ave."/>
    <CUSTOMER _ID = "4" FULLNAME = "Ms. Jill Kahn" FULLADDR = "12 School St."/>
    <CUSTOMER _ID = "6" FULLNAME = "Ms. Betty Burke" FULLADDR = "400 Technology Path MS10-27"/>
    <CUSTOMER _ID = "3" FULLNAME = "Mr. David Argonne" FULLADDR = "75 Great Road"/>
    <CUSTOMER _ID = "5" FULLNAME = "Mr. Kenneth Williams" FULLADDR = "837 Ocean Ave."/>
    <CUSTOMER _ID = "7" FULLNAME = "Dr. Martha Gill" FULLADDR = "555 Main Street"/>
    <CUSTOMER _ID = "10" FULLNAME = "Dr. Andrew McCaig" FULLADDR = "999 Hill Road"/>
    <CUSTOMER _ID = "8" FULLNAME = "Mr. Steven Holland" FULLADDR = "4325 Hill Road"/>
    <CUSTOMER _ID = "12" FULLNAME = "Mrs. Laurie Patry" FULLADDR = "10 Rustic Trail"/>
    <CUSTOMER _ID = "1" FULLNAME = "Mr. Samuel Smith" FULLADDR = "10 Commerical St."/>
    <CUSTOMER _ID = "9" FULLNAME = "Ms. Nicole Orlando" FULLADDR = "820 Middlesex Turnpike"/>
    <CUSTOMER _ID = "11" FULLNAME = "Mr. Skip Lewis" FULLADDR = "10 Dock Street"/>
    </ROOT>

    There are more options around xmlmaps and xmlschemas discussed in the manuals along with how to send the output directly to a file.

    ------------------------------
    Jonathan Smith
    UniData ATS and Principal Engineer
    Rocket Software
    ------------------------------



  • 4.  RE: Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-14-2021 22:03
    Hi Jonathan,

    Thanks again.
    I have been using TOXML a lot in my reports.
    I just need to find how to send the output directly to file as you said.

    Thanks alot.

    Regards
    Francis

    ------------------------------
    Francis Aquila
    Core Banking Support Officer
    TISA
    Papua New Guinea
    ------------------------------



  • 5.  RE: Is there a way I can output to excel in universe retrieve sentence?

    ROCKETEER
    Posted 04-15-2021 05:09
    Francis,

    How to create an XML document from a Retrieve statement is covered in 'Guide to Retrieve' in the section 'Creating an XML document with Reterive'. Can I suggest you read this so that you are aware of all the options available and specifically how to use a XML DTD and a XML Mapping file.

    The syntax for the command is as follows.

    LIST[DICT|USING[DICT]dictname]filename...[TOXML[ELEMENTS][WITHDTD][WITHSCHEMA|SCHEMAONLY][XMLMAPPINGmapping_file][TOxmlfile]]

    If I use a simple version of sending something to a XML file and use the command 

    LIST CUSTOMER FULLNAME FULLADDR TOXML ELEMENTS TO JDS

    Then a record the &XML& directory called JDS.xml is produced as below.

    Top of "JDS.xml" in "&XML&", 63 lines, 1,475 characters.
    001: <?xml version="1.0" encoding="UTF-8"?>
    002: <ROOT>
    003: <CUSTOMER>
    004: <_ID>2</_ID>
    005: <FULLNAME>Ms. Diana Morris</FULLNAME>
    006: <FULLADDR>431 Third Ave.</FULLADDR>
    007: </CUSTOMER>
    008: <CUSTOMER>
    009: <_ID>4</_ID>
    010: <FULLNAME>Ms. Jill Kahn</FULLNAME>
    011: <FULLADDR>12 School St.</FULLADDR>
    012: </CUSTOMER>
    013: <CUSTOMER>
    014: <_ID>6</_ID>
    015: <FULLNAME>Ms. Betty Burke</FULLNAME>
    016: <FULLADDR>400 Technology Path MS10-27</FULLADDR>
    017: </CUSTOMER>
    018: <CUSTOMER>
    019: <_ID>3</_ID>
    020: <FULLNAME>Mr. David Argonne</FULLNAME>
    021: <FULLADDR>75 Great Road</FULLADDR>
    022: </CUSTOMER>

    I again strongly suggest you have a read of the 'Guide to Reterive' and associated manuals to better understanding of the XML capabilites.

    ------------------------------
    Jonathan Smith
    UniData ATS and Principal Engineer
    Rocket Software
    ------------------------------



  • 6.  RE: Is there a way I can output to excel in universe retrieve sentence?

    ROCKETEER
    Posted 04-14-2021 04:40
    Here is an example of the Wintegrate Query Builder and File Export. Which once built can be run from TCL. Please refer to the Wintegrate manuals for more information.




    ------------------------------
    Jonathan Smith
    UniData ATS and Principal Engineer
    Rocket Software
    ------------------------------



  • 7.  RE: Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-14-2021 22:00
    Thanks Jonathan,

    This will really help.
    I would be able to run this but our users will need some tutoring on this as they are not familiar with Wintegrate and the universe database files we have.

    Appreciate the screenshots.
    Will try this out.

    ------------------------------
    Francis Aquila
    Core Banking Support Officer
    TISA
    Papua New Guinea
    ------------------------------



  • 8.  RE: Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-14-2021 07:03
    Hi,

    Unless you buy some third-party tool there isn't a solution straight out of Universe.  You could look at outputting your retrieve statement to xml and opening the XML document with excel but it might be a little manual if it is required for users.

    Other option is look at writing a python program to create the excel document via U2.py and openpyxl.

    Thanks,
    Greg

    ------------------------------
    greg Livingston
    Outsurance
    ------------------------------



  • 9.  RE: Is there a way I can output to excel in universe retrieve sentence?

    ROCKETEER
    Posted 04-14-2021 07:21
    Hi Greg,

    I don't think python was added to UniVerse until 11.3 , Francis is using 11.2.5 that's why I didn't mention Python.

    Thanks,
    Jonathan

    ------------------------------
    Jonathan Smith
    UniData ATS and Principal Engineer
    Rocket Software
    ------------------------------



  • 10.  RE: Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-14-2021 21:49
    Thanks Jonathan.
    Yes. Don't have python in our system.

    ------------------------------
    Francis Aquila
    Core Banking Support Officer
    TISA
    Papua New Guinea
    ------------------------------



  • 11.  RE: Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-14-2021 21:55
    Thanks Greg.
    Been using the TOXML and ELEMENTS a lot on my reports. But just trying to make the reports more user friendly to our users so they can run and get the outputs straight away from universe.

    Thanks alot.

    Regards
    Francis.

    ------------------------------
    Francis Aquila
    Core Banking Support Officer
    TISA
    Papua New Guinea
    ------------------------------



  • 12.  RE: Is there a way I can output to excel in universe retrieve sentence?

    ROCKETEER
    Posted 04-14-2021 09:12

    There are several YouTube videos to explain about how to retrieve UniVerse data from Excel using U2 ODBC client.
    Here are the links.

    How to install 64-bit U2 ODBC client and set up a system DSN
    https://youtu.be/dNneOb3G7v0
    Import UniVerse Data to 64-bit Excel 365 using Microsoft Query
    https://youtu.be/lLWcHWInA6o
    Importing Data To 64-bit Excel 365 from UniVerse Using 64-bit U2 ODBC
    https://youtu.be/qgdKB7_GgyA



    ------------------------------
    Paul Chang
    Rocket Software
    ------------------------------



  • 13.  RE: Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-14-2021 21:48
    Hi Paul,
    Appreciate the videos.
    Giving me more ideas on how to get the data in Universe.

    We have export of data that happens from Universe to SQL database every 1 am in the night. I am trying to get the data straight from universe to get the real time update of the data. Unfortunately not using Excel 365 at the moment but thats another good solution to what I am after.

    Thanks alot.

    Regards
    Francis

    ------------------------------
    Francis Aquila
    Core Banking Support Officer
    TISA
    Papua New Guinea
    ------------------------------



  • 14.  RE: Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-14-2021 09:40
      |   view attached
    You can't do this with a retrieve sentence unless your running it through your connection software (if it has that capability).  I've converted most of our reports to Excel by using a trick I stumbled upon.  I build a basic program and build the report as tab-delimited, then I run it through a convertor to change it to an .html table, then change the suffix to ".xls".  Excel complains about this but it does open it and recognizes the html.  I have this installed in our menus and spooler and it works great.  The convertor I have, also checks column sizing and formats the table so it doesn't have to be reworked once you're in Excel.  There are other ways to do this but this is fairly easy once you have the convertor coded.  I attached a copy of the convertor.  It has some custom coding for our system but you shouldn't have to change much.

    For viewing I copy it to our web server and open a link to the report using Accuterm.

    ------------------------------
    Mark Vander Veen
    Project Leader, System Administrator
    University of Chicago Press
    ------------------------------

    Attachment(s)

    txt
    UTIUC.TABTOHTM.txt   12 KB 1 version


  • 15.  RE: Is there a way I can output to excel in universe retrieve sentence?

    ROCKETEER
    Posted 04-14-2021 09:56
    Excel accepts the csv file with different formats, e.g. tab-delimited. You can use a basic program or tool to generate the csv file format. If you are familiar with U2 Clients, you can generate the csv files too.

    ------------------------------
    Paul Chang
    Principal Technical Support Engineer
    Rocket Software
    ------------------------------



  • 16.  RE: Is there a way I can output to excel in universe retrieve sentence?

    PARTNER
    Posted 04-14-2021 09:58
      |   view attached

    Hi Francis:

    I've written a couple of programs that allow me to do what you are requesting, one produces a Tab-delimited file suitable for Excel and the other produces an Excel (XML) file which can also be opened in Excel, both can be run from the TCL passing in the sentence the filename and the dictionary field names of the file.  The limitation is that it does not understands complex sentence structures, it expects the SELECTed Records to be active and acts upon that previously selected set of records.

    Then using wIntegrate's interface it writes it to the users PC and automatically starts Excel.  Here is a sample of the EXCEL.TAB program, the one for XML (Excel) is a bit more complicated because of the need of identifying the Excel Headers and Column and Row definitions as well as Footers.  but the Tab is easy to use and see what the program is doing.  

    Hope that helps.

    Regards, Sam



    ------------------------------
    Sam Powell
    ATS, Inc
    ------------------------------

    Attachment(s)

    txt
    EXCEL.TAB.txt   5 KB 1 version


  • 17.  RE: Is there a way I can output to excel in universe retrieve sentence?

    Posted 04-14-2021 21:41
    Hi Sam,
    That would be what I am looking for. As long it helps the users with the reports. But some of my reports include complex dictionaries. The code is useful though for other reports.
    How do I make it work on our system. Can I copy the codes and compile and catalog?

    This is very helpful.

    Regards
    Francis

    ------------------------------
    Francis Aquila
    Core Banking Support Officer
    TISA
    Papua New Guinea
    ------------------------------



  • 18.  RE: Is there a way I can output to excel in universe retrieve sentence?

    PARTNER
    Posted 04-15-2021 10:02

    Hi Francis:

    The program won't compile because you would be missing the includes the program calls for.  The CONSTANTS is an include that contains a whole bunch of items that I use in a myriad of programs, which many would not be needed for this particular program.  The DIM.DICT.FILE is simply the following.

    DIM D.DICT.FILE(30) ; DICT.FILE.MATRIX = 30
    EQU DICT.FILE.TYPE TO D.DICT.FILE(001)
    EQU DICT.FILE.LOC TO D.DICT.FILE(002)
    EQU DICT.FILE.CONV TO D.DICT.FILE(003)
    EQU DICT.FILE.DISP.NAME TO D.DICT.FILE(004)
    EQU DICT.FILE.FORMAT TO D.DICT.FILE(005)
    EQU DICT.FILE.SNG.MLT TO D.DICT.FILE(006)
    EQU DICT.FILE.ASSOC TO D.DICT.FILE(007)
    EQU DICT.FILE.DESC TO D.DICT.FILE(008)
    EQU DICT.FILE.FIELD TO D.DICT.FILE(009)

    The other section that won't work for you is the calls to upload the file to the PC and run Excel, You're more than welcome to modify as you see fit and replace those calls with some of your own stuff.

    Regards, Sam




    ------------------------------
    Sam Powell
    ATS, Inc
    ------------------------------