Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Excel Input Adapter - XLSX file format

  1. #1

    Default Excel Input Adapter - XLSX file format

    Has anyone been able to successfully read an XLSX file with version 5.3 or later ?

  2. #2
    Join Date
    Apr 2008
    Posts
    4,686

    Default

    Quite a few times.
    Just remember to change the Spreadsheet Engine on the first tab to Apache POI or Apache POI Streaming.

    If you can't get it to work, post a REALLY simple example of what you're trying to do, and we'll work through it with you.
    Also, have a look at "Excel Input - XLS - XLSX - ODS reading" in the samples folder of your PDI install... It might provide the hints you need.

  3. #3

    Default

    Just discovered: the worksheets have got to be in perfect table format.
    My sheets had missing headers for some columns, missing rows, etc.
    Also,there is no way to open an XLAM file is there ? I'd like to fire-off an excel macro procedure.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,686

    Default

    Quote Originally Posted by marksimms View Post
    My sheets had missing headers for some columns, missing rows, etc.
    Missing headers is sometimes OK... But missing rows will be considered the end of data for that tab.
    And yes, CONSISTENT input is key.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,686

    Default

    Quote Originally Posted by marksimms View Post
    Also,there is no way to open an XLAM file is there ? I'd like to fire-off an excel macro procedure.
    None that I'm aware of.
    Is the procedure supposed to help with the reading of the data?

  6. #6

    Default

    Exactly...once it is opened, it fetches data from a data warehouse.
    This would be a cool plug-in if it can be pulled-off, but I don't think there are any Java classes to do this, are there ?

  7. #7
    Join Date
    Apr 2008
    Posts
    4,686

    Default

    I don't know.

    However, if you're pulling data from a data warehouse, why not just grab it directly with a Table Input?

  8. #8

    Default

    Ahhhhh....because I need to create the intricate formatted report in Excel.
    The report designer may be considered for this, but I need to show something fast.....and this was going to be an interim solution.

  9. #9

    Default

    http://sourceforge.net/p/jacob-proje...read/83e49c58/

    Found a cool set of Java classes that will open an XLAM or XLSM file and execute a macro.
    This could be the basis for a Java plug-in, no ?

  10. #10
    Join Date
    Nov 2015
    Posts
    1

    Default

    I'm new to Pentaho and trying to extract the xml map within an xlsx file. Icreated an xml schema as it would be easier to extract unstructured data.Basically we do not have a header in the xlsx. We are using Pentaho5.4

    My xml map is in this format inside xlsx.

    < ?xml version="1.0" encoding="UTF-8"standalone="yes"?><Attestation_From_Cxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
    <individual_medical>Yes</individual_medical>
    <individual_pharmacy>Yes</individual_pharmacy>
    <psg1_avg_deductible>80</psg1_avg_deductible>
    <psg1_eff_deductible>80</psg1_eff_deductible>
    <psg1_eff_pre_ded_coins_rate>80</psg1_eff_pre_ded_coins_rate>
    <psg1_eff_post_ded_coins_rate>80</psg1_eff_post_ded_coins_rate>
    <psg1_non_ded_cost_share>80</psg1_non_ded_cost_share>
    <psg1_eff_claims_ceil>80</psg1_eff_claims_ceil>
    < /Attestation_From_C>

    The data is in the below format

    PLAN SUBGROUP 1: Individual Medical EFFECTIVE PARAMETERS
    Average Deductible:
    80
    Effective Deductible:
    80
    Effective Pre-deductible Coinsurance Rate:
    80
    Effective Post-deductible Coinsurance Rate:
    80

    Please suggest what best way to extract this is. I tried 'Get Data from XML'but the xlsx zip has data in different xml files. I also tried 'Microsoft ExcelInput' but that did not work either.
    Appreciate your help very much.
    Thanks


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.