Visokio website     Downloads     Video tutorials     KnowledgeBase  
Sources: Excel-Decimals imported as integers using Excel Reader? - Visokio Forums
Sources: Excel-Decimals imported as integers using Excel Reader?
  • Alexander     Alexander March 27, 2014 8:04AM
    Hi all,

    I am encoutering a bug in Omniscope. My source is a group of .xls files. In the configuration of the "Group Import" I have chosen the Omniscope reader option, as there is no Office installed on our server.

    In the Excel files there are decimals, but Excel shows them as integers. Omniscope reads them as integers, instead of decimals.

    When importing into the same Omniscope file on my local machine, I use the Excel direct reader option. Then the decimals are imported into the DataManager source block. Therefore, I assume that the problem is the Omniscope Reader. I am available for a screen share.

    (Using 2.8 b.1235)

  • 6 Comments
  •     donald March 28, 2014 6:14AM
    You can use a Field organiser to ensure that the fields are correctly interpreted.
    image

    Note that when the Omniscope is run on a machine that does not have Microsoft Excel installed Omniscope will revert to the internal reader. It is good practice to use a Field Organiser to ensure that the fields are interpreted exactly as you expect as the "Auto-recognise".

    This appears to be the case here where the field has been interpreted differently when Omniscope has defaulted to the internal reader.
    Attachments
    		 field_organiser_excel_integer_decimal.png 61K
  • Alexander     Alexander March 28, 2014 6:17AM
    Hi Donald,

    The decimals are lost in the input block. Converting the field to decimals does not change this. The 4,75 in the cell in Excel is shows as "5", even when converting the field to decimals it is still "5".

    In Excel the settings is that there are no decimal places, so the value shown is "5", but underlying the value is "4,75".

    If helpfull I can do a screen share.
  •     tjbate March 28, 2014 8:29AM
    Alex - What happens when you change the Excel display option to show the actual decimal value, rather than an integer rounding?
  • Alexander     Alexander March 28, 2014 8:32AM
    That solves the problem. As discussed on the phone with Donald, our client sends us 40 rounded Excel files every week, and in total we already have over 1200 files with this problem. So adjusting the display option in Excel for this dataflow not an option.
  •     tjbate March 31, 2014 2:06PM
    Alex - There are at least 3 different Excel Reader options available in Omniscope...one will import the displayed values (rounded integers) just as the client is providing the data to you, and others can import the underlying decimal values, ignoring the client's display rounding. The quickest way to solve this problem is probably just to install Excel on the server running Omniscope, so that you have all Excel Reader options available. Is there some reason you can't do this?
  • Alexander     Alexander March 31, 2014 2:08PM
    Hi Thomas, OK. Thanks for your reaction.

    No, there is no reason except that we do not need MS Office for any other purpose.
    We will install Office asap and expect the process to run smoothly.

    Thanks guys for your help!
Add a Comment

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

Tagged