Not seeing how to get Calc to display datetime values as anything but ### upon CSV file open

[vinidi]

@Lupp

CSV isn’t a precisely defined standard

That’s exactly why LibreOffice shouldn’t be trying to format the values as anything but text or at least give me the option upon opening to have it not do that. I mean, I thought the reason Calc shows a pre-opening pop-up box to set the CSV formatting is so that we can set our standard for that file…

I don’t see an option to “just show values as text”:

Sure there’s an option to “Format quoted fields as text”, which is fine if the fields are quoted… but most fields in CSV that I’ve worked with aren’t quoted.

So… did I just miss the option “display all values as-is text, don’t format them as ### or [some ridiculous E+# number, and other dumb stuff only ODT files should try to do]”?

[LeroyG]

@vinidi

I just miss the option

Select all columns (click the empty rectangle over the row 1), and choose Text.

[erAck]

@vinidi

shouldn’t be trying to format the values as anything but text

If it did that the questions would all be “why can’t I import my values” instead.

@vinidi

I don’t see an option to “just show values as text”

You missed that each field’s column type can be set, either select and set individually or just select all and set to Text.

[Lupp]

The purist may see any character codes contained in the “csv” file - even LF (or any traditional control characters) as data.
She’s welcome. There are simple means (in StarBasic e.g.) to read a file bytewise, and to interpret it at will.
The csv concept isn’t defined restrictive, but gives a lot of room for implementations to exert it in a way offering users efficient means to do/order what is supposed to be often needed.
In case of lacking consistency concerning the pair of source/generator at the one end and the interpreter/consumer at the other end you will get errors misunderstandings and even great disaster.
Compared with what may happen if some of the mentioned pairs refuse their duty concerning reliability, a missing automatism for the adaption of column widths (what was the original issue) is extremely banal. A click (top left corner: Select All) and a doubleclick ( on any one of the column separators) solve the problem . So does the one-line-code in Basic:
ThisComponent.CurrentController.ActiveSheet.Columns.OptimalWidth = True

[Villeroy]

It has been introduced because too many users complained about 12/3 or 2:3 or 3-1 being interpreted as dates or times. Declaring these strings as text was too much for them. This was a problem for some users.
OpenOffice 3 introduced this new option and turned it off by default. SInce 12 years numeric strings imported from csv is the most frequently reported problem with Calc because this option is turned off and nobody understands what a special number is.
The solution to this problem would be: Import pairs of numbers separated by date delimiters as string. A date consist of 3 numbers. Handle keyboard input differently from text import.

[Lupp]

The better -and the only good and safe- way to get rid of the mess would be to exclusively communicate (including via any variant of csv) dates and date-time values obeying the existing standards given by ISO 8601.
A conversion to a numeric representation as preferred for spreadsheets can then be done (accepting the arbitrary “null-date” of the version) by the receiving person if she is knowing what he does. This can be done during import or later using formulas or whatever.

Calc even allows to calculate with ISO-near textual representations without any explicit conversion.

See attached little rexample. disask77899calculationWithDateTime.ods (13.4 KB)

[sokol92]

Implicit conversions should be used with great care.
For example, guess without the help of Calc the result of the calculation of the formula:

=LEN(-"1:")

[LeroyG]

@sokol92

=LEN(-"1:")

"1:" is an hour. Why it don’t work without the minus sign?

[sokol92]

@LeroyG

"1:" is an hour.

Only when converted to a number.

@LeroyG

Why it don’t work without the minus sign?

Because the Len parameter must be text.

[Lupp]

The given example =LEN(-"1:") does no longer work under V 7.3.3.2. It still worked “as expected” (by sokol92) under V 6.4.5. Now you need (e.g.) =LEN(-"1:0") to get the string accepted as a time and autocoverted respectively.

However, I wasn’t talking of absurd examples but of purposeful ones, and if you make sure to pass (basically) ISO 8601, the conversion is reliable (Zone info currently excluded)…
If the purpose, however, is to prove the absurities of the spreadsheet-Do, you can easily get any formula result (except an error message) to be shown as “I am wrong!”.

Only opinions now:
Far beyond the examples, I generally am doubting automatic conversion if not strictly and restrictively specified for any kind of a propgramming system. The shortest way to give a specification of the kind is to exclude strings generally, and to allow number conversion only “upward” to the more general type, only if there is a single chain of types, and only if the conversion always is done in one step by native means. Good old Pascal offers the usage of the type name as the name of a function converting to that target type.

We also have partly a mix-up of automatic conversion and of overloading operators or functions/routines/methods concerning their parameter types. When I studied programming a bit more seriously nearly 60 yeras ago there was no overloading and rarely auto-conversion. I didn’t miss them. But there was a starting uglyness pretending to be due to efficiency, ruled by FORTRAN, and still present in …

[sokol92]

@Lupp

It still worked “as expected” (by sokol92) under V 6.4.5

I expected and see 19 (length of "-0,0416666666666667"). By the way, in Excel you get the same result.

Version: 7.3.4.2 (x64) / LibreOffice Community
Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5
CPU threads: 6; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded

Version: 7.3.3.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.3~rc2-0ubuntu0.20.04.1~lo1
Calc: threaded

The main problem with implicit conversions in Calc (and Excel) formulas is that the conversion rules are locale dependent (as you yourself have pointed out many times)…

[Lupp]

Suprise. I actually tested =LEN(-"1:") in V 7.3.3.2 (Win 64) and got #VALUE!.
ISO 8601 is not locale dependent, and therefore what I recommend to exclusively use everywhere.
From my point of view the first main problem is bad teaching. To give an example: There is an officious specification in Germany by DIN 5008 for business letters, containing as a norm the ISO 8601 date-format (DIN 5008 – Wikipedia). Trained typists simply don’t know it, and regard its usage a crackpot idea. If I need to verify my credit card data via phone, I’m forced to use an idiotic 6-digit-format for my birthday: DDMMJJ (JJ in place of YY). Being 100+ years: How to?
The second main problem concerning spreadsheets specifically is the “compatibility with Excel”. I simply can’t be compatible with the results. It would require to be “as crazy as MS likes me to be”.

[sokol92]

@Lupp

Being 100+ years: How to?

I wish this was our problem!

[Villeroy]

Trying to get back on topic: When Calc opens the csv with timestamps as text, it widens the column from 2,26 cm to 3,63 cm which is wide enough. When Calc imports the correct values, it widens the column to 3,28 cm which is not wide enough. The displayed cell string is the same for both types text and formatted number. My default font is Liberation Sans 10pt.

[erAck]

@Lupp

Calc even allows to calculate with ISO-near textual representations without any explicit conversion.

Depends on Tools → Options → Calc → Formula, Detailed Calculation Settings, if Custom Details Conversion from text to number is set to Generate #VALUE! error it will result in exactly that, a #VALUE! error in E2:E3. Which is the only sane option, but too convoluted for Excel users and documents, because Excel even does the worst of all, the locale dependent conversion.

[erAck]

@LeroyG

"1:" is an hour.

The expression -"1:" depends on Calculation Settings, Conversion from text to number.

@sokol92

Because the Len parameter must be text.

Numeric is implicitly converted to text. =LEN(3/2) results in 3 (length of text 1.5).

[Lupp]

Good to now your informed opinion. My setting there is Convert only if unambiguos, and I thought it was safe (sane) enough. Of course this depends on how the “unambiguous” is judged. What are your main concerns?
A background of my own considerations is my strong preference for ISO date and time strings - and ISO 8601 definitely specifies a textual representation of date-time. This said, I would prefer it over any representation of dates by numbers because it may be (next to) locale independent, but surely depends on an arbitrary null-date.
To recommend textual date-time representation in sheets, on the other hand may be too unhandy if explicit conversion is needed in every case of intended calculations. (There isn’t a function DATETIMEVALUE() afaik, and i need to use two functions for one conversion of a date-time-stamp.)

[erAck]

@Lupp

What are your main concerns?

That any implicit conversion, even unambiguous, may hide conceptual or data errors, where for example number sequence arguments like SUM() and its family members expect ignore all text content cells but operator + calculations implicitly convert such operand.

[flywire]

What is a typist?

@Lupp

I’m forced to use an idiotic 6-digit-format for my birthday: DDMMJJ (JJ in place of YY). Being 100+ years: How to?

No, it’s for validation of birthday. 100+ is no problem, calculating birth year is.

@Lupp

The second main problem concerning spreadsheets specifically is the “compatibility with Excel”.

In the real world, yes.

[vinidi]

@Villeroy

It has been introduced because too many users complained about 12/3 or 2:3 or 3-1 being interpreted as dates or times. Declaring these strings as text was too much for them. This was a problem for some users.

Um, maybe I’m misreading what you’re saying, but users complaining about 12/3 or 2:3 or 3-1 being interpreted as dates or times would be addressed by the values just showing as text; the users asking for them to just be displayed as text doesn’t seem like the problem, it looks like the solution.