Export to CSV - triming leading zeros — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Export to CSV - triming leading zeros

Received Response
427
Views
8
Comments
aPsikus
aPsikus Rank 6 - Analytics Lead
edited Aug 13, 2024 5:49PM in Oracle Analytics Cloud and Server

Hi,

I have looked for similar issues on forum, but there is no working solution.

I have report with Postal code column. In RPD it is varchar, as in some of the countries postal code is alphanumeric.

We would like to share results with greater audience, and we are using Agent/IBot to send mails with attachment. Extract file is too big for Excel, and we need to use CSV format, but in this format values with leading zeros are treated as number and zeros are trimmed.

I haven't found any working solution which will avoid trimming leading zeros.

Do you have any suggestion how it can be solved?

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Hi @aPsikus

    does this solution work for you?

    Oracle Support Document 1929503.1 (OBIEE 11g CSV Export Drops Leading Zeros When Imported Into Excel) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1929503.1

    https://support.oracle.com/epmos/faces/DocumentDisplay?id=1929503.1

  • aPsikus
    aPsikus Rank 6 - Analytics Lead

    Hi Joel,

    Honestly saying what they propose is not a solution, it is workaround.

    • The CSV format used to export raw data and import to another system. 
    • If you have leading zeros in your data, you should use a formula either in your request or in the rpd to enclose the data in quotes if you want Excel to not treat these as numbers.  
    • Another option is to open CSV file in Notepad, save it, and then open it in Excel.

    When I download to Excel it is fine, but results are too wide to do it and there is export error.

    Problem is in CSV.

    And I can't send to user TXT/Plain text and ask for manual import to Excel.

    Any suggestion what formula do they mean in point two? CAST, CONCAT do not work, as you can't add single quote.

  • Michael Verzijl
    Michael Verzijl Rank 6 - Analytics Lead

    Well: the limit is not in Oracle-BI, but in EXCEL and how it opens CSV files.

    You can change this by including a ' in the column to prevent Excel from doing this. Otherwise do a manual import in Excel.

  • aPsikus
    aPsikus Rank 6 - Analytics Lead

    Hi,

    I understand, but the requirement is to find a solution on OBIEE side.

    How you would like to add single quote?

    If you will just add it in the formula, there is OBIEE error: End of the input has bean reached. Illegal syntax.

  • Joel
    Joel Rank 8 - Analytics Strategist

    Your formula must be wrong with the wrong number of quotes to get the single quote. Try:

    ''''||column

  • Sentil
    Sentil Rank 2 - Community Beginner

    This issue can be resolved if Oracle Fusion OTBI Analytics supports XLSX format. Currently, only XLS and CSV supported. XLS has a row limit of 65000 and CSV has issues for leading zeros.

    Why is Oracle Fusion OTBI Analytics not supporting XLSX export format?

  • Gianni Ceresa
    Gianni Ceresa Mod

    @Sentil ,

    Did you check if an idea for that support already exists? And if not, you can create one: https://community.oracle.com/products/oracleanalytics/categories/idealab-otbi

  • Sentil
    Sentil Rank 2 - Community Beginner
    edited Jul 25, 2025 11:50AM

    Hi @Gianni Ceresa - I believe these issues are already logged as ideas but Oracle have either Archived them as not-delivered or is in the open queue. These limitations have been there since the launch of Oracle Fusion applications - therefore Oracle development should be aware of them.

    Note: I opened several ideas in the past and they most are Archived by Oracle development or not delivered yet. Hence, there is no motivation to create new ideas anymore.

    Sadly, we have advised our customer of these limitations and they are seeking alternative solutions either within Oracle or using 3rd party reporting tools