Results 1 to 4 of 4

Thread: Excel node - real decimal

  1. #1
    Contributor
    Join Date
    Apr 2011
    Location
    Copenhagen
    Posts
    142

    Default Excel node - real decimal

    Hi Guys.

    I have this excel sheet where the values a formatted to have 2 decimals.
    However, the true value could have 3 decimals or more.
    But since the format in excel shows only 2 decimals, only 2 decimals will be imported in the Excel Node.

    I cant change the format, since SAP only accept 2 decimals when uploading, but I have to be able to read if there is more decimals in the file.

    Is there a way to change the way the excel node behaves?

  2. #2
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    230

    Default

    Hi Thomas,

    When the 'IgnoreCellFormatting' property on the Optional tab is set to 'true' the node uses the underlying value of a numeric cell rather than the displayed value.

    This property was included in the LAL 6.1.45 update. You can confirm which LAL version you have installed by opening a new data flow in BRE and clicking on
    'View' --> 'Dependent Libraries ...' and then in the pop-up window, check the version number against the lal1.brg library e.g.
    C:\Program Files\Lavastorm\LAE6.1\lib\brain\brg\lal1.brg - 6.1.48.0.19

    Regards,
    Adrian

  3. #3
    Contributor
    Join Date
    Apr 2011
    Location
    Copenhagen
    Posts
    142

    Default

    Thank you Adrian.

    I can see that we are on 6.1.41, so I don't have that feature.


    Thomas

  4. #4
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    230

    Default

    If you cannot upgrade your LAL version at this time and you have Open Source R and the R node installed on your machine you could also use it to access the Excel file.
    It is a little convoluted to set up if you are not already using it and you need to have RServe running on your R installation, so it may not be appropriate for everyone.

    The R node information is available here. See the release notes for details:
    http://www.lavastorm.com/current-rel...orm-libraries/

    Your R installation would need to be provisioned with the 'xlsx' package e.g. using:

    install.packages("xlsx")


    The following R node imports the data. The values are the underlying cell values rather than the displayed values.

    Code:
    node:R_3
    bretype:r::R
    editor:sortkey=5a0d802d7d3f5e33_2
    output:5a0d8033038f6146/out1=
    prop:RScript=<<EOX
    ## Load the library
    library(xlsx)
    
    ## Set the path to the Excel file
    FP = "C:\\temp\\Decimal_Places.xlsx"
    
    ## Read in the data and output it on the first output pin
    out1 <- read.xlsx(FP, sheetIndex = 1)
    EOX
    editor:XY=490,110
    end:R_3
    Last edited by awilliams1024; 11-16-2017 at 12:34 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •