Copy time from csv to ods not working correct

English
#1

I have a csv file with a time value in a cell.
When copy as text into a existing ods file the value will not be correct.
In the csv, when reading it as text, the value is “14:30:00”.
I neet to copy as text since I want to keep the formatting in the receiving ods.

After pasting it into the ods file it is “0,604166666666667” when formatting as Number/standard.
This is the same as other time values in the ods representing 14:30.
But when subtracting on from the other and multiplying with 100 I get “-59,8125”.
So there is a difference between those two values both representing 14:30.

This is a real problem when sorting my sheet.
How to resolve?

On win10 and
Version: 24.8.1.2 (X86_64) / LibreOffice Community
Build ID: 87fa9aec1a63e70835390b81c40bb8993f1d4ff6

#2

Try with parentheses, =(A2-B2)*100 otherwise with =A2-B2*100 you are multiplying B2 by 100 and subtracting that result from A2, that is =0,604166666666667*-99

Use the Import dialogue to import the CSV and make sure that Detect special numbers box is ticked, then Calc will detect that they are in a time format.

ImportTimeDetectSpecialNumbers
ImportTimeDetectSpecialNumbers449×605 11.1 KB

1 Like
#3

I corrected the formula, it still gives a difference.
=(C258-C281)*100000000 equals: -0,000002314815

May be I’m missing something here, but everytime I open a CSV I have to go through the Text Import dialogue.
Yes, I have the Detect special numbers checked.
I also tried to experiment with the Column type, no help.

And still, I have to copy the data from the opened csv to my ods sheet.
I do have a variance in the import dialogue, but they should be OK.
Untitled

#4

Some more info, this seems to only happen with timestamps ending on .33333 and .666667.
So may be some rounding problem?

#5

That’s two micro seconds difference over a hundred million days (274000 years) and to do with precision; nothing related to what your question was about at all.

See this Wikipedia article, Numeric precision in Microsoft Excel - Wikipedia

1 Like
#6

True, but they are not equal and will not sort in the correct order when they are like sort criteria 2 and the important result of the sort is in sort criteria 6.
And they will not be removed during “remove duplicates”.
So this really is a severe problem in my use.

#8

Once more a question of the kind was asked without attaching example file(s) showing a realistic case.
Here best:

  1. An .ods containing an image showing the used settings.
  2. The .csv created this way.
  3. The .ods you got importing the .csv .
    Should be enriched with an image of the import dialog with actual settings.

You can cause a lot of time wasting and long chains of comments this way.

I’m tired of reading such threads and of guessing what was meant exactly in the comments.

#7

Generally:

  • If you want to get date and/or time exactly as shown in the source .ods represented in a csv, the export should be done with the respective setting.
  • The import should NOT use “Detect special numbers”.
    The imported contents can easily be converted later.
    You can also calculate directly with the imported Date/Time texts in Calc based on automatic conversion, at least if ISO 8601(delimited) was used.

See examples:
exampleCSVexport.ods (45.2 KB)
exampleCSVexport.csv.fake.ods (554 Bytes)
exampleCSVexportReimported.ods (54.3 KB)

Uploads of .csv are not supported. I had to use a fake extension. Remove the .fake.ods after the download.
Regard that the exported .ods still contains formunlas. You will there not find the values present at expot time.

#9

My situation is that I have a external csv file where I want to add the data in the csv into my master ods sheet.
I have to rely on datetime values are equal, when they should be equal, as they are involved in sorting and comparing. So even the slightest difference in two values will have them to be not equal. Even if they both look the same and seems the same when looking av the float point value of them.

Your input helped me a bit, if I open/import (what is the difference?) the csv and not using “Detect special number”, then copy/pase the data from the csv into the ods as values, I get the datetime as text.
But then I can use “Text to colums” with Date(YMD).
This converts the text to datetime with the same internal value as the rest of the same timestamps in my ods.

No help in setting the datetime colum to Date(YMD) during the import of the csv.

So for me it still seems like a opend csv file with a column set to Date(YMD) during import is not equal to the same visual datetime in a ods file.

As mentioned before, this seems to only be related to datetime ending on 33333 and 66667.

#10

No sample CSV that shows the problem = no problem :wink:

#11

Here are samples.
But now the values seems to be equal, but still sort wrong.
So not sure what the problem might be anymore.

ods.ods (531.2 KB)
csv.csv.ods (248 Bytes)

Open the csv as described before, copy the rows and past them into the bottom of the ods file.
Sort on A, B and C.
The sorting is wrong for time 14:30.
The sorting gets correct if I copy an existing 14:30 value over the pasted value.

#12

They aren’t equal: RAWSUBTRACT gives around 1.11E-16 for both 14:30 and 16:15. And it’s the value in ODS that isn’t equal to TIME(14;30;0)

#13

All the times in the ods is acquired the same way. With a copy/past from a csv file.

#14

This is a bug. A time value written in ODF as PT14H30M00S is loaded as a number different from the number created by e.g. =TIME(14;30;0) or typing the time directly. Indeed, on save it will be stored to ODS, and then when reloaded, the values will be identical.

14-30.fods.ods (777 Bytes)

tdf#163344

2 Likes
#15

Thank you for verifying and clarifying.
I’m new to this forum so not sure what the proper way to respond now is, like if I should set this to the solution or give kudos any other way :slight_smile:

#16

I have fixed it. It will be “solution” when it gets released in a next version.

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