Keep line breaks when exporting to CSV/XLSX when using carriage returns via Oracle function LISTAGG

Diwanshu Khatri shared this idea 3 years ago
Idea Logged

Hello Team,

We would like to know how Yellowfin BI in the excel export can understand <br /> as a carriage return? When it's viewed in the UI the behaviour is how you would expect, however, when exporting to CSV and XLSX the formatting does not carry across.

With the Text Replacement function, it doesn't work for our customers. It seems they are using LISTAGG oracle function to create these fields.

When exporting to CSV or XLSX the line break is lost.

Best Regards,

Diwanshu K

/e4c626cbb679b790623255af0623b40e


/91cb03369bb3d9d8b20e6d8f5b3d6701

Replies (11)

photo
1

LISTAGG oracle function is used to create these fields.


For Linked Requests

(SELECT Request_ID02,

LISTAGG(Request_Description01,'<br />') WITHIN GROUP (ORDER BY Request_ID02) AS description

from ( select Request_ID02, Request_ID01, Request_Description01,

CAST (DBMS_LOB.SUBSTR(Request_Description01,1500,1) AS VARCHAR2 (3900 BYTE))as Description

from WOI_Associations where Request_Type01 in ( 9000, 12000, 22500, 33000))GROUP BY Request_ID02)


For Request Activity Log

(SELECT Request_Number,

LISTAGG(to_char(EIB_UNIX_TS_TO_DATE(WorkInfoSubmitDate),'DD/MM/YYYY') || ' - ' || coalesce(Full_Name, ' ') || ' - ' || coalesce(Notes, ' '),'<br />') WITHIN GROUP (ORDER BY Request_Number) AS description

from ( select Request_Number,Full_Name, CAST (DBMS_LOB.SUBSTR(Notes,1500,1) AS VARCHAR2 (3900 BYTE))as Notes, WorkInfoSubmitDate from SRM_WorkInfo where View_Access = 1)


GROUP BY Request_Number)


How Yellowfin in the excel export can understand <br /> as a carriage return ?


Thanks,

Diwanshu

photo
1

Hello Team,

Any update on this please?

As per our understanding, the export functionality is as per design of Microsoft office format. Yellowfin sends HTML rendered data to MS office. However, MS office resets some of the formatting values and so the sizing is not retained.

Having said that, multiple lines data too works as per the underlying export functionality of MS Office.

Appreciate your quick response!


Thanks,

Diwanshu K

photo
1

HI DIwanshu,

Thanks for reaching out to support.

The mentioned task has been completed and is set to be included in 8.0.10 and 9.6 releases.

However it sounds like you are requesting an additional option to CR and LF, in this case <br / >, is that correct?

And the desired output is to MS Excel as opposed to CSV?

If so I can create a developer task to look into potentially including this option in a future release.

Thanks,

Eric

photo
1

Hello Eric,

Thanks much for your update.

Please be infirmed that we were able to achieve this from application console using LISTAGG function as mentioned above and yellow displays the expected data on console, however the issue is while exporting the same to Excel.

I would request you to review the email attached to this ticket earliest.

We just need to understand how Yellowfin BI in the excel export can understand <br /> as a carriage return ?

Please feel free to contact me in case of any concern. Thank you!


Best Regards,

Diwanshu K

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hi Diwanshu,

There is no email attached to this ticket, could you upload to our ftp https://ftp.yellowfin.bi using the "send files" option?

Are you able to provide a source DB with dummy data for this query to work? Or something similar?

Thanks, Eric

photo
1

Hello Eric,

I can see that a zip file attached to the case wherin you have the attached email from end Customer, I am not sure why you were not able to see it-

Also, I have uploaded the zip file on the FTP site- (xxx.7z)


Are you able to provide a source DB with dummy data for this query to work? Or something similar?-- Sorry but we won't be able to provide source DB with dummy data, but attached files has email and excel wherein you will get more details about the issue and use-case.

Please feel free to contact in case of any query.


Thanks,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hello Eric,

We would just like to know how Yellowfin BI in the excel export can understand <br /> as a carriage return ?

Its working fine using LISTAGG function on application console, the problem is export the data to Excel.

Please refer attachments uploaded to FTP and update us at the earliest.


Thanks,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
1

Hello Eric,

Any updates on this topic please. Its just a confirmation in regards to the export functionality with respect to carriage return ?

We would just like to know how Yellowfin BI in the excel export can understand <br /> as a carriage return ? Its working fine using LISTAGG function on application console, the problem is export the data to Excel.


Thanks,

Diwanshu

photo
1

Hello Eric,

Any further updates on this topic please?

Customer mentioned that this issue is observed in CSV and in PDF as well. We are still awaiting for your feedback here.


If possible, please assign this to someone working as per our working hours- 12.30 PM - 9.30 PM IST.

Thanks,

Diwanshu

photo
photo
1

Hi Diwanshu,

I have created a Developer task to look into this issue. Updates to the task will be provided as they are available.

Thanks, Eric

photo
1

Hello Eric,

Thanks much for creatig a developer task. May I please request you o share the details or Taskid for reference.


Best Regards,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

photo
photo
1

Hi Diwanshu,

I will also pass this to the appropriate region for a call.

Thanks,

Eric

photo
1

Hello Team

Any news on this please. May I please request you o share the details or Taskid for reference.


Best Regards,

Diwanshu

Working Hours- 12.30 PM - 9.30 PM IST

Leave a Comment
 
Attach a file