Direct and quick export Datas from an Uniface form to Excel

Author: daniel.cabero@euralis.com (cabero)

We have performance problems during export Datas from our Uniface forms to Excel. We simply concatenate the fields of the forms in a special "big" field. So, after that, we can filedump this field in file in text mode. and then we open this file directly with Excel. But this method has several disadvantages: -> In case of numerous occurences, this export is VERY long -> if the data , (even if it's a field types Character)  start  with '- ' or '+' or '0' , etc,  o Excel interpret the column as a numeric field I Tried to use: ComponentToStruct and then StructToXml to filedump the XML file. But I have errors in Excel while opening the xml file created.   Is anyone have a solution to export directly, simply and faster, the datas of Uniface forms to Excel  ? Thanks in advance for any help

11 Comments

  1. Hi Cabero, Unfortunately you are stuck with Excel's way of doing things. Using COM works the best but takes very long. The best way I've found is to dump the data to a CSV file and then let Excel open it. When dumping the data to csv, put all your values in double quotes and that will keep Excel from trying to be clever and converting the data types, so it will keep the '+' and '-' and will keep the leading zeros. Hope this helps! Cheers! e.


    Author: eg (eugene.truter@sita.co.za)
  2. Hi Cabero, Which version do you use ? Before 9.7, your proc may be impacted by BUG: 30884 - Performance issue dumping a large file without linefeeds. Cordialy,


    Author: Gilles (gls.tools@free.fr)
  3. Daniel, Have you thought of using the SEQ or TXT driver to do this export?? Saves you having to concat the full strings - set the field separator = comma and you should be good to go... I changed a process from using concat to txt driver - and got the process done in a fractio of the time - and 90% less code executed... If you need some assistance - let me know.... Knut


    Author: Knut (knut.dybendahl@gmail.com)
  4. That you for your answers ! The solution proposed by Knut seems to me interesting to test. But, my forms are based on an (or more)  oracle table. So that the users can retrieve easily the datas they want to see and extract. So, Knut, you have to copy all the rows retrieved to another entity based on SEQ or TXT ? Could you explain me the way you do that ? many Thanks  Daniel


    Author: cabero (daniel.cabero@euralis.com)
  5. If the row layout is pretty much the same, copy one entity into another model and set the driver connection for the new entity to SEQ/TXT. Depending on the size of your dataset, there are a couple of ways to move data from one entity to another; 1) loop with putlistitems/occ, creocc in new entity and getlistitems/occ... 2) As long as fieldnames are the same, an XMLSAVE from source, $replace in XMLSTREAM of from ENTITY/MODEL name with to ENTITY/MODEL name followed by an XMLLOAD. Once done, do the store/e "new_entity" and you're good. I prefer option 2, as I only do a few commands and let Uniface handle the rest. I think you still have my email address - right? Send me a CIF (Goto/Admin/Exhange Model/Case Unload) and I'll knock out a small sample.... Regards, Knut


    Author: Knut (knut.dybendahl@gmail.com)
  6. Maybe you could define a functional subtype of the DATABASE ENTITY for this use.   BTW, UNIDOC says:  Database Connectors

    "At runtime, however, you can use path assignments to choose yet another connector for that entity."

     

    Regards.


    Author: fearandir (fearandir@gmail.com)
  7. I did'nt know it was possible to change the path assignement dynamically at runtime ... How can I do that by proc ? Thanks for help


    Author: cabero (daniel.cabero@euralis.com)
  8. Hi Cabero, I do NOT think it is possible to change the path assignment dinamically at runtime. IMHO the word "dinamically" if present should be get out from the sentence in the documentation because is misleading. AFAIK it is possible: - Define a base path for an entity within an application model. - Change a path at runtime using assignment file [ENTITIES] section. - Know at runtime which path is assigned to a specific entity with $entinfo(). About subtypes with path different from its supertype: AFAIK it could be changed but choices are reduced to - Same path as supertype - Not in database ...but also: it could be I am missing something... :-) Regards, Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  9. I agree with gianni. Not DINAMICALLY. Documentation does not include that word.   But i think you can define different "entity to path assignments", to direct individual entities to an additional path, beside the one specified by the DBMS Path property.   Then, MAYBE, when creating a functional subtype , the DBMS_PATH DROPDOWNLIST allows you to select TXT connector.   Give it a try or... just pray for an unnexpected DISELI appearance Wink   Regards,  Sergio


    Author: fearandir (fearandir@gmail.com)
  10. Hi guys, I can only agree with Sergio... - Every day of our (short) life a slice of prosciutto San Daniele with its aroma and taste, accompanied by a glass of good wine, it is a pleasure for our eyes, nose, mouth and soul. - During few bad Uniface days of our life "an unnexpected San Daniel Iseli appearance" could change that day for sure! LaughLaughLaughWinkWinkWink Ciao, Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  11. cabero said I did'nt know it was possible to change the path assignement dynamically at runtime ... How can I do that by proc ? Thanks for help  

    Hi Daniel I did find a way to change the path parameters at runtime :-) Unfortunately not the DBMS-Type :-( Define a dummy-path like this [PATHS] $DUMMY_PATH MSS:|| Now you can open this path by: OPEN "<odbcname>|<user>|<password>","$DUMMY_PATH" All tables assigned to this DUMMY_PATH a now using this access to database Don't forget to close the path! CLOSE "$DUMMY_PATH" You can OPEN and CLOSE a path as many times as you want Maybe there is someone how find the "trick" to change the DBMS-type as well :-) Ingo


    Author: istiller (i2stiller@gmx.de)