ABAP Export to Excel Results in Lesser Rows when Double Quotes Exist

[former_member195167]

Hello folks,

I have an internal table that comprises of multiple columns, one of which is 'Description'. In this char column, I am providing random text. However, whenever there is a single instance of double quote, the rows in the internal table move to a single cell of the exported excel until the next closing double quote is found.

Here's an example

Column1 Column2 ColumnDescription

ABC 123 Hello"Random

DEF 456 TestRandom

GHI 789 LorumIpsum

JKL 123 Hello2"Random

MNO 456 HelloTest

In this scenariom, rows 1 to 4 would be merged into a single cell in the exported excel; and thereby, the exported excel would have just 2 rows instead of 5.

In the code, I move all columns to a string separated by horizontal tab. Then I convert the string to xstring using SCMS_STRING_TO_XSTRING. Post that, I call cl_wd_runtime_services=>attach_file_to_response.

I came across an old post that describes my issue very clearly; however, no solution has been provided.

https://answers.sap.com/questions/804934/double-quotes-when-downloading-to-excel.html

Any suggestions/SAP notes?

Thanks,
Saud.

[IN]

Hello,

Can you afford to replace such a string with any specific string that does not exists in the Description field before getting value into internal table and prepare data for download? This will override the problem in downloading. This is not an ideal solution (far far away 🙂 ), but will not mess with the table structure.

Regards,

Igor

[former_member195167]

Thank you very much Igor for your suggestion 🙂

This does work, and is the temporary solution I've pushed to the users for now.

Replacing the single instance of double quote with two single quotes works fine as well.

Since this does not solve the issue, I am trying to hunt for some SAP note that could resolve the issue as it has occurred previously for others.

[ArthurParisius]

I think the problem isn't specifically a SAP issue. It's more an Excel issue. Using (double) quotes in Excel is usually a way to ensure the field is treated like a text value.

[former_member195167]

Hi Arthur,

Could be - however, it works well in standard when if you are exporting an ALV to spreadsheet which contains data similar to what I've provided above.

[Tomas_Buryanek]

First you need to understand how CSV (and also tab delimited) files works. There are rules which need to be followed.

For example here is nice blog about it.

TLDR: one of the rules is, that if value (column) contains delimiter (doublequote) you need to escape it with another delimiter. Also if value contains delimiter, CRLF or separator, then you need to wrap it in between delimiters.

Example:
BEFORE: Hello2"Random

AFTER: "Hello2""Random"

You do not need to program these "CSV rules" yourself. There are solutions to do it already (methods, functions...). Check the linked blog or search.

[former_member195167]

Hi Tomas,

Thank you for your response and for sharing the blog!

From your post, I understand that all delimiters must be escaped with another delimiter.

However, the funny thing is that this does not have be done for data maintained in transparent tables and exported to excel spreadsheets. It works perfectly fine when we export ALV tables!

[Tomas_Buryanek]

Which exactly ALV export do you mean? If for example ALV build in (SAP standard) to XLSX export, then yes, of course XLSX is very different Excel format than CSV (and tab delimited) format.

And please use comments if you are answering to somebody. This looks like you are answering to yourself (your original question).