Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Cognos BI report download converting alphanumeric ##E### in csv to exponential numbers

    Posted Thu May 07, 2020 02:15 PM
    Hi,

    When I download Cognos BI report into csv file, it changes an alphanumeric text to exponential number. For example, it converts the Cognos BI data 54E031 to 5.4E+32 in the downloaded excel csv file. I can't figure out the syntax to use in the expression to ensure the data 54E031 is downloaed as the text instead of the exponential number 5.4E+32. I am hoping someone could help me with resolving this issue. I can't seen any solution online and Cognos documentation is not quite helpful. Thanks.

    Regards,

    Chuck


    ------------------------------
    Chuck Egboka
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Cognos BI report download converting alphanumeric ##E### in csv to exponential numbers

    Posted Fri May 08, 2020 05:31 AM
    Try to append a single quote (') prior to the string so that it is not treated as a no. in excel. The single quote will appear in address bar when you focus on the cell or when you double click on the cell but it won't appear otherwise. 

    Regards

    ------------------------------
    Kiran Passumarthi
    www.linkedin.com/in/kiranpassumarthi
    ------------------------------

    Original Message


  • 3.  RE: Cognos BI report download converting alphanumeric ##E### in csv to exponential numbers

    Posted Fri May 08, 2020 09:20 AM

    Hi Chuck,
    Just to clarify. When you say 'it changes an alphanumeric text to exponential number', do you mean Cognos does or Excel does? If you open the report in Notepad or Notepad++ do you see that value as 5.4E+34 or is it just how Excel displays the value?

    Also Excel will only show you the first 15 digits since Excel follows the IEEE 754 specification, even if you modify the cells in Excel to be Number. If you need to see all of the numbers I think treating it as a Character string as Kiran suggests (as does Microsoft) is your best option.

    Cheers,
    Trevor



    ------------------------------
    TREVOR COMEAU
    ------------------------------

    Original Message


  • 4.  RE: Cognos BI report download converting alphanumeric ##E### in csv to exponential numbers

    Posted Fri May 08, 2020 10:45 AM
    Thanks Kiran and Trevor for your response. I was about to mention that when I searched for more solution online, I came across a comment that excel csv does the conversion and there was a suggestion to open the csv with notepad. I tried and opened it with notepad and truly, the data was not been converted to exponential or scientific number. So it was excel csv that was converting it. I should have mentioned this late yesterday. Works fine with excel workbook. It is just excel csv that is the issue. So yeah, Trevor it is from the excel csv. It is not from Cognos. I will try Kiran's suggestion in the expression and update you folks as soon as I am done. Thanks.

    ------------------------------
    Chuck Egboka
    ------------------------------

    Original Message