Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Export to CSV - triming leading zeros

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
-
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
0 -
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.
0 -
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.
0 -
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.
0 -
Your formula must be wrong with the wrong number of quotes to get the single quote. Try:
''''||column
0 -
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?
0 -
@Sentil ,
Did you check if an idea for that support already exists? And if not, you can create one:
0 -
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
0