ORACLE APEX - Download to .CSV (null values shown as - are missing) problem

user_12163202 0
Viewed 614

I recently upgraded from APEX 5.0.3 to APEX 20.2 and since then I have an issue when downloading a table as a .CSV file

The tables have many columns and each column contains either text, (decimal) numbers or Null values that are shown in APEX as - (dash) with the APEX functionality (Attributes => Appearance => Show Null Values AS - )

Col6, Col8 and Col9 show - (dashes)

The table below is just an example with 10 columns (separated with | (pipe)) to give you an idea.

Example table:

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10
Max Muster 1500 USD 2.25 - 13.02.2021 - - A
Felix Muster 2300 CHF 3.25 1 26.07.2021 IN CN D
Moritz Max 4500 EUR 4.20 - 16.08.2021 - - C

CSV Output:

Max,Muster,1500,USD,2.25,,13.02.2021,,,A

Felix,Muster,2300,CHF,3.25,1,26.07.2021,IN,CN,D

Moritz,Max,4500,EUR,4.20,,16.08.2021,,,C

Standard download in APEX 20.2 loses the dashes!! I wonder why.

I would expect the following result in the .CSV file:

Max,Muster,1500,USD,2.25,**-**,13.02.2021,**-**,**-**,A

Felix,Muster,2300,CHF,3.25,1,26.07.2021,IN,CN,D

Moritz,Max,4500,EUR,4.20,**-**,16.08.2021,**-**,**-**,C

Does anyone have a solution for it. We have many reports with up to a 100 columns and we need the dashes in the file. I do not want to add the dash afterwards in the .CSV file nor would I replace the - with any other (special) character.

Best regards,

Thomas

oracle-apex csv null oracle
1 Answers

In my opinion, displaying a dash is exactly that: displaying a dash. It is not contained in the table (column value is NULL).

If you wanted to export a dash, then you'd have to modify report's query and literally type what you want to export instead (using the NVL function), e.g.

select empno, 
       ename,
       nvl(to_char(comm), '-') comm   --> this
from emp

However: note that - just as my example shows - you might need to apply another function, such as TO_CHAR because dash is a string while comm column (in Scott's sample EMP table) is a NUMBER.

I've just tried it, and yes - it works (dashes are exported).

Furthermore, pay attention to what you do. If you export numbers along with dashes, you won't be able to perform any calculations (e.g. if you open the CSV file in MS Excel).

In your case, it would be

select col1,
       col2, 
       nvl(to_char(col6), '-') col6,   --> NVL + TO_CHAR, as col6 contains numbers 
       nvl(col8, '-') col8             --> just NVL, as col8 contains strings
from your_table
user_90979060
Related