Spreadsheet CSV export of empty cells contain forumla

Desktop editors
1

OS version: Sequoia 15.4.1
App version: 9.0.3

I have cells with formulas that conditionally output nothing, like:

=IF(LEN($A1),"Yeah: " & $A1,"")

When outputting to CSV, all cells that should be empty, will output the formula as a string instead.
So I mill have a CSV with something like:

Foo,Yeah: Foo
,"=IF(LEN($A1),""Yeah: "" & $A1,"""")"
Bar,Yeah: Bar

I want and would expect:

Foo,Yeah Foo
,
Bar,Yeah Bar

To work around this, I now have to copy the whole sheet to a new one and paste as “Paste only values”. And export that instead. Not really workable!

2

G’day @petervanwesten

It is an interesting issue that you have discovered, and it would appear that formulas on a row that reference the blank cell do not render a value during the export. Even a formula as basic as;

=CONCAT(A2,"")

yielded the same result;

Foo,Foo
,"=_xlfn.CONCAT(A2,"""")"
Bar,Bar

Which appears to be the result of a null field.
If the presence of a formula generated null is removed, then the result I could get is;

Foo,Yeah: Foo
, 
Bar,Yeah: Bar

which looks what what you need, but because the formula is;

=IF(LEN($A1),"Yeah: "&$A1," ")

which is placing a space character after the comma, instead of null.

Maybe not the most ideal solution, but may suffice until support can allow a null result from a formula during CSV export.

3

Unfortunately, no, I cannot use that work-around.
The place I use the CSV in checks for empty values. And a space is not empty.

4

By the way, you can test this with the most simple formula:

=""

This outputs as:

"="""""
5

Admittedly, I have never done

=""

before. Never had needed to do so.
But I was looking for a formula to yield null that didn’t keel-over during CSV export, which appears an exercise in futility in hindsight.

So, it would appear that you are stuck until Support can get OO to return a null from a formula during CSV export.

… I guess the ZWSP would not help either.
( Zero Width SPace → UNICHAR(8203) )
And, unfortunately, OO does not like UNICHAR(0) either.

6

Now the hope OO support will actually pick this up and fix it!

7

Hello,
If you don’t mind I will join this thread as well.
@petervanwesten could you please share a test file where the issue occurs, along with the original file that’s being output to CSV?

8

Really simple to reproduce!
Onlyoffice on nextcloud setup.

  1. Just create a new spreadsheet file.
  2. In the first cell place: =""
  3. Download file as CSV
  4. Open file in a text editor.

Result:

"="""""
1 Like
9

testing-CSV.csv (13 Bytes)

1 Like
11

Thank you! We’re checking the situation.

13

Hello @petervanwesten
We confirm that the current behavior is a bug. We have added it to internal tracksystem andd we have started working on it.
Thank you for the valuable data!

14

Hi, having the same issue. i have a lot of data with blank formula and it is all in the csv export and is not usable. what is current status and ETA for the fix?

15

Hello @dora
Unfortunately, we’re still working on the mentioned scenario. I have added your request to internal tracksystem, we will update this thread once we have something to share.