Bug 158622

Summary:	CSV which contains #N/A, cannot read as #N/A(NaN, Not a Number)
Product:	LibreOffice	Reporter:	polyester <polyester>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	NEEDINFO ---
Severity:	normal
Priority:	medium
Version:	7.6.4.1 release
Hardware:	x86-64 (AMD64)
OS:	Windows (All)
Whiteboard:
Crash report or crash signature:		Regression By:

Description polyester 2023-12-10 03:39:32 UTC

Description:
CSV file which contains #N/A, cannot read as #N/A(NaN, Not a Number).

Steps to Reproduce:
1.Generate spreadsheet which contains #N/A in a cell. "=NA()" works fine.
2.Save the spreadsheet as CSV.
3.Check the CSV file by text editor(Vim, Emacs, ...).
  We can look #N/A in the file.
4.Read the CSV file using Calc.
5.Check the cell(containg #N/A) using ISNA() function.

Actual Results:
#N/A interpreted as string, so ISNA() return FALSE.

Expected Results:
#N/A in CSV should interpret as #N/A(NaN, Not A Number).


Reproducible: Always


User Profile Reset: No

Additional Info:
Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1
CPU threads: 4; OS: Windows 10.0 Build 22631; UI render: Skia/Vulkan; VCL: win
Locale: ja-JP (ja_JP); UI: ja-JP
Calc: threaded

Comment 1 ady 2023-12-10 06:29:01 UTC

When saving as CSV:

* Edit filter settings"
** Save cell formulas instead of calculated values

When opening the CSV file with Calc, set the "Evaluate formulas" check box ON.


Are you using those settings?

If not, please specify which steps/settings you are using when saving the CSV and which ones when opening it. It may be helpful to "Add an attachment" with a sample csv file to your next post/comment in the bug report page.

For me, no repro.

Setting to NEEDINFO.

Comment 2 Mike Kaganski 2023-12-10 09:08:42 UTC

Note that #N/A! (e.g., returned by NA()) is *not* "NaN" ("not a number"), but "not available" (see https://help.libreoffice.org/latest/en-US/text/scalc/05/02140000.html?DbPAR=CALC ).

Also note that indeed, a literal text "#N/A!" in a CSV is not any kind of error - CSV has *no* notion of data type, and it can't store errors at all. Unless you follow comment 1, and store formulas instead of values, this is NOTABUG. File format limitations must be taken into account; one can't similarly expect to store font formatting and images in TXT files.

Comment 3 polyester 2023-12-12 21:42:50 UTC

And... Microsoft Excel can reproduce #N/A from CSV in these forms(case insensitive):
=NA(),=na(),=Na(),=nA(),#N/A,#n/a,#N/a,#n/a

So, for compatibility, it's better to interpret #N/A in CSV as #N/A(not string).

Comment 4 polyester 2023-12-12 21:49:40 UTC

Sorry(typo) :
- =NA(),=na(),=Na(),=nA(),#N/A,#n/a,#N/a,#n/a
+ =NA(),=na(),=Na(),=nA(),#N/A,#n/a,#N/a,#n/A

Comment 5 ady 2023-12-12 21:57:41 UTC

@polyester,

You don't seem to answer the prior comments / questions. Am I missing something?

Comment 6 Mike Kaganski 2023-12-13 05:44:25 UTC

(In reply to polyester from comment #3)

MS Excel is not an authority in CSV (if it was XLSX, this argument would be sound).

Comment 7 QA Administrators 2024-06-11 03:13:55 UTC

Dear polyester,

This bug has been in NEEDINFO status with no change for at least
6 months. Please provide the requested information as soon as
possible and mark the bug as UNCONFIRMED. Due to regular bug
tracker maintenance, if the bug is still in NEEDINFO status with
no change in 30 days the QA team will close the bug as INSUFFICIENTDATA
due to lack of needed information.

For more information about our NEEDINFO policy please read the
wiki located here:
https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO

If you have already provided the requested information, please
mark the bug as UNCONFIRMED so that the QA team knows that the
bug is ready to be confirmed.
 
Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-NeedInfo-Ping