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

English
,
#1

This happens EVERY TIME I open a .csv file with a datetime in it like 2019-08-15 14:53:18. I have to double-click the column separator to get it to auto-expand to show the full datetime, and it’s only off by several pixels. This is obnoxious dumb behavior by an editor that should have some base level of intelligence with this.

Calc already is smart with many other columns, auto-expanding them to fit not only the column header but also the value. This is actually smarter than many Microsoft products which will collapse a column header if the value is shorter. I don’t understand why Calc has this counter-intuitive behavior with datetimes.

I’m going to try to attach images showing my experience. It’s almost like the editor is playing a prank on users with this.

image
image324×594 44 KB

#2

Your date-time values seem to be read-in using the option Detect special numbers and were therefore converted from the textual representation contained in the csv to their numeric representation.
The ISO-conforming format was preserved, but an automatic adaption of the column width isn’t included with the process, and under the standard width the formatted values don’t fit.
The csv doesn’t contain related information.
If you think automatic “Optimal width” should be included for columns containing recognized numbers, you can file an enhancement request under bugs.documentfoundation.org.

Currently (V 7.3.2.3) the column width is automatically adapted (roughly) to the wiidth of text contained in the topmost cell of the column, and supposed to be a header / field-label.

1 Like
#3

I think it is a reported bug, which I can’t find now.
At opening csv files, for dates, column width is adapted to the date number without take care of their date format.

#4

were therefore converted from the textual representation contained in the csv to their numeric representation

They weren’t converted to numeric… they were converted to three hash characters: ###

Only after double-clicking the column separator to auto-size the column width did it expand by several pixels and show the datetime. That’s very apparent in my screenshot, for both columns.

ISO-conforming format was preserved

That’s good, but I need the values to display in the CSV file as-is, not changed for display or whatever. I need the literal text in the file to show the same way in the column values. If disabling Detect special numbers makes that happen, great. Note that I haven’t enabled that feature, supposedly it’s enabled by default.

The CSVs I view with Calc contain text, keyboard characters including spaces, newlines, and sometimes tabs. I don’t need anything specially formatted, I just need to see the text as it is in the CSV. I’d also like the columns to auto-size, at least up to a min/max width, though I’m not sure how to make all that happen. I just want it to work intuitively with CSV data; it’s not an ODT after all, I thought that’s where special formatting happens, since CSV is just purely comma-separated values, not formatting like with XLS.

Again my double-newlines (from hitting the Mac return key twice) are reduced to single newlines… very frustrating. I’m using the normal WYSIWYG. I don’t know why it displays properly on the right-side preview but doesn’t in the actual post. I’m sorry if this looks like one giant paragraph of text. It looks like everyone else’s double-newlines are reduced as well… so frustrating.

#5

CSV isn’t a precisely defined standard, but leaves a lot to the user. This starts with “comma separated” what actually isn’t the best idea because the majority of locales use the comma as their decimal separator.
Well, assuming you want it like Prof. Higgins -that is: exactly as you want- you need to tell the software what you want.
If you make sure that your csv files are made from sheets containing date-time-stamps and whatever data generally being treated as numbers in spreadsheets exclusively as texts now, there is no problem. Save to csv with option “Quote all text cells” enabled. You then only need to make sure that the “labels” in the first row are just as wide as you want to have the readily filled columns. Nothing will be “re-formatted” then or “recognized as special number”.
A feature like “Do What I Want Instead Of What I Tell” isn’t implemented yet.
So frustrating.

BTW: Ctrl+A (Select All) and then a Doubleclick on one column separator will make all columns Optimal Width (maximum = 58 cm, default width for empty columns).

#6

Simply don’t use a spreadsheet program. Use a database application or a text editor. There are specialized text editors for csv such as https://csved.sjfrancke.nl/.
csv is a database exchange format in plain text. It has absolutely nothing to do with spreadsheets. It contains raw data in plain text. It does not contain any formatting instructions or column widths.There is not even a binding standard for csv. It’s a kludge for database data when there is no way to connect to the source database. “Working with csv intuitively” is a contradiction in itself.
P.S. ##### is not an error value. The cells are fully accessible. You can easily sum up the time values, get minimum, maximum, averages etc. and create charts without problems.

#7

### just indicates that the column is too narrow to display the numeric value. Widen it.

#8

Imo, the question is that if importing csv widens its columns to accommodate all the content automatically (often even making columns wider than screen), then failings this for some kind of content is a (small) bug.

#9

I tested with this file (117 Bytes, after download, delete.odt and leave .csv), and get no problem.
imagen

Would you share to test the first two lines of your file? Please, add your LibreOffice version, operating system, and zoom level. Thanks.
LibreOffice 7.2.7.2 on Windows 6.1.

#10

Because your time stamps are text actually. Always check “Detect special numbers”

1 Like
#11

Thanks @Villeroy.
Now I see that the columns width are the width of the real numbers (no date-formatted), not the date. I just cleared formatting (Ctrl+M) for A3:B3.
imagen
a.csv.ods (117 Bytes)

#12

Imported with option “detect special numbers” and column widths adjusted:
Bildschirmfoto von 2022-07-01 22-28-56
Calc interpretes dates as dates in the context of the specified import locale when that idiotic option is set. It is idiotic because you always want it set and by default it is unset.
In this particular case we are dealing with ISO time stamps. ISO time stamps import correctly with any locale.
2019-08-15 14:53:18 ISO
15.8.2019 14:53:18 same value, but requires German locale
8/15/2019 14:53:18 requires English(USA)
15/8/2019 14:53:18 requires English(non US)

When you remove the formatting, you get the true decimal cell value.
German 43.692,6203472222 is the same value as
English 43,692.6203472222
2019-08-15 14:53:18
15/8/2019 14:53:18
$43,692.62
43.692,62 €
All these values are the same numeric value with different formattings and all formulas processing this same value will always return the same result. The weekday of $43,692.62 is 5 which stands for Thursday because day #43692 was Thursday, the 15 of August 2019. The important thing is that you do not import dates, times, currencies etc. as literal text because this is a spreadsheet program and spreadsheet programs deal with numbers in the first place. Date strings and currency strings are useless. Phone “numbers”, zip codes and part “numbers” are identifiers and should be imported as strings by marking their columns as strings in the import dialog, otherwise you may get wrong identifiers with leading zeroes cut off.

#13

For you it may be idiotic, for others it is not. The background is that importing anything else than numbers as numeric content and then formatting it somehow according to whatever rules will transform the data, and resaving to CSV it may end up as different data. Hence Calc does that only if requested.

And no, we’re not dealing with ISO time stamps here, because ISO requires the literal T time designator between date and time, and the data here is just another date+time format that nearly resembles ISO but is not. And indeed, if the data was 2019-08-15T14:53:18 then it would be imported as date+time accordingly formatted, regardless of how the Import special numbers option was set.
Since tdf#88359 for 7.3.

1 Like
#15

A few days ago, I helped someone on IRC with a CSV import; and when they realized that the import from that textual format could convert some data to numbers, they asked why that dangerous option is not off by default, to guarantee safety by default, and to require explicit configuration for any conversion. Please avoid declaring any PoV as the only one possible and sane. There are others with equally extreme but opposite PoVs. Thank you.

1 Like
Auto Sum won't display a negative currency total
#16

I frequently use dates in csv files and although I haven’t used excel for a while I don’t recall having problems with dates. I suspect excel uses the locale setting because I was even blissfully ignorant of commas being used as decimal separators in some countries.
.
Using calc I’m certainly aware that importing dates is always a problem and it doesn’t seem to be easy to fix after importing it. Sometimes I end up with a mix of text dates and numbers formatted as dates.
.
You can’t even use calc to reformat data and save it “as shown”: https://bugs.documentfoundation.org/show_bug.cgi?id=142553

Fair enough, they are not dates or times. After data is entered it is validated and stored.
.
If it works reasonably in excel then why not use the same approach (whatever it is). Excel wasn’t perfect, I recall using DMS (trigonometry) as the best option for hours and minutes to avoid rounding errors.

#27

Because “hidden from view” is not “as shown” by display formats, but that’s in the bug’s comments somewhere.

#32

That’s exactly why LibreOffice shouldn’t be trying to format the values as anything but text :laughing: 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”:

image
image676×739 132 KB

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]”?

#35

imagen
imagen525×583 30.1 KB

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

1 Like
#36

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

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

2 Likes
#37

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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.