Clarity

  • 1.  Read data from Excel

    Posted Apr 08, 2010 05:24 AM
      |   view attached
    Hi All,  We have created gel to read data from excel. I just want to share this gel with you.  Regards.    

    Attachment(s)



  • 2.  Re: Read data from Excel

     
    Posted Apr 09, 2010 10:21 AM
    Thank you for sharing this Kayahan!
     
    Chris


  • 3.  RE: Read data from Excel

    Posted Jun 15, 2010 10:22 AM
    Hi Kayahan,

    Could you please attach the gel to read data from Excel.

    Your help is much appreciated!!!

    Thanks


  • 4.  Re: Read data from Excel

    Posted Sep 09, 2015 02:29 AM

    Hi Kayahan

     

    I have the same requirement for reading the excel file from a ftp folder, I have to try your attached code, Could you please share your thoughts on below.

     

    We have to install any drivers or anything in Application server? Is it possible to directly connect excel file which is located in a ftp location.

     

    Thanks,

    Ram



  • 5.  Re: Read data from Excel

    Posted Sep 09, 2015 10:20 AM

    The script uses a library that provides a 'JDBC to ODBC bridge', however as you may know, ODBC is a Windows technology/implementation, and even if you have the 'bridge' classes available to you (up to / including JDK 1.7), depending on your platform you may not have (or may not be able to have) the other drivers and elements in place to be able to make calls to the ODBC driver platform.  There are a number of references in Google to those difficulties in opening Excel/Access/etc. files using this bridge on unix platforms due to this.

     

    Although I'm not having direct experience of using this library, I would also suspect that the location of the (Excel) file is going to be subject to the limitations of the ODBC driver, and I have not heard of any ODBC drivers for Office document file formats that would directly open a file from an FTP source - though you could consider using / investigating the ftp GEL library calls in order to get the file moved locally and then open it as normal.

     

    That said, I would agree with Dave_3.0 in this post that other approaches to the solution would be prefered: https://communities.ca.com/message/241823128#241823128

     

    You also need to be aware that use of the Java-provided JDBC to ODBC bridge is being removed in Java 8 and so any solution using this would not work/last for very long: JDBC-ODBC Bridge

    Status of the JDBC-ODBC Bridge

    The JDBC-ODBC Bridge should be considered a transitional solution; it will be removed in JDK 8. In addition, Oracle does not support the JDBC-ODBC Bridge. Oracle recommends that you use JDBC drivers provided by the vendor of your database instead of the JDBC-ODBC Bridge.



  • 6.  Re: Read data from Excel

    Posted Sep 09, 2015 11:11 AM

    After dealing with a multitude of Excel – PPM imports/exports, I found the easiest method is to build a small helper application for each Excel formatted file.

     

    Since you are in an On-Demand environment, you will need an external machine to run the imports.  A small Azure machine is a simple / low cost method for doing this type of work.  For example, I have an hosted service that sends a very detailed financial / multiple sheets Excel file to a given user based on a formatted email request.

     

    You can used any number of Excel components (Apache POI, Closed  XML,…) to provide the ability to read Excel files and the PPM XOG web services to do the import.

     

    V/r,

    Gene



  • 7.  Re: Read data from Excel

    Posted Sep 10, 2015 02:22 AM

    Thanks for the information Nick and Gene