Description: Windows 10: Version: 6.4.1.2 (x64) In Calc, when pasting dates from imported .csv file, cell format changes from previously-chosen "date" format to "text" format. Dates then acquire a leading apostrophe: '03/11/2020, making it impossible to change the text formatting without manually editing the cells to remove the leading apostrophe. Converting dates to text strings can cause dates to be sorted incorrectly even though the dates are displayed correctly. This happens even if cells have been previously formatted as "date" format. Steps to Reproduce: 1. Import list of dates from .csv file formatted as 03/10/2020 2. Note that dates now have a leading apostrophe. 3. Choose Data->Sort Ascending or Data->Sort Descending Actual Results: See above. Expected Results: Dates should retain date format and be correctly sorted. Reproducible: Always User Profile Reset: No Additional Info: Dates should retain date format and be correctly sorted.
Please, attach file CSV for test.
Created attachment 158724 [details] Sample .csv file
I can't reproduce bug. Version: 6.4.1.2 Build ID: 4d224e95b98b138af42a64d84056446d09082932 CPU threads: 8; OS: Linux 5.5; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded
Created attachment 158725 [details] Tes csv bug File CSV is import correctly.
Using English USA language for import it's fine for me, I don't need select the date column type.
Created attachment 158732 [details] DateBug.xls file Attached .xls file shows mis-sorted dates. Dates were copied from a .csv file and pasted into the .xls file. You can see the dates are not properly sorted.
Because are not properly imported, have been the import done with LibreOffice?
The .csv file is opened with Libre Office and is copied/pasted into the .xls file, also using Libre Office.
I try... 1) Open your file XLS 2) Import your file CSV 3) Copy and paste from CSV to XLS Work fine, please, verify your process for import CSV.
Created attachment 158754 [details] Test for this bug
<< 1) Open your file XLS << 2) Import your file CSV << 3) Copy and paste from CSV to XLS That's exactly what I've been doing. I tested it before filing this bug report. I will try it again to make sure, but there is something wrong that is causing the dates in the xls I attached to be sorted incorrectly. Even though you can't reproduce it it is definitely a problem for me. Look at the years. I flagged the mis-sorted rows in the xls file I attached.
Look my GIF file, I recorded process.
Follow these steps: File -> New -> Spreadsheet to make new destination spreadsheet. Double click on .csv file to open it. Click "OK" on text import window. Copy and paste cells into new spreadsheet, including "Date" column. Select column A. Click on Format -> cells. Note that column is formatted as "Number". Select "Date" and choose any date format. Program inserts apostrophes ahead of all date values. See formula bar to see this as the apostrophe is not visible in cells. Cells have now been reformatted as text due to leading apostrophe ahead of date value. Selecting "Format -> Cells", the cells are formatted as "Date" but are actually text due to apostrophe as first character. As text the dates cannot be correctly sorted. Note also that selected date format is not applied when cell values are converted to text. Selecting "Friday, December 31, 1999" has no effect. Cells are retaining original date format. Remember, cells were originally formatted as "Number", not "Text". Reformatting cells as "Number" does not remove apostrophes, leaving cells as Text. Apostrophes must be removed manually, cell by cell. The crux of the problem is that cells are being reformatted as text, not date.
Created attachment 158804 [details] Screenshot about how to impor properly With the screenshot another configuration of the import window, that works fine. Sorry but I can't find a bug here, only needs to set up adecuactly the import configuration.
All works will if the dates are typed in manually rather than imported. Please describe in words the proper way to import the .csv file rather than making us figure it out from a screen shot.
You can see my file in video: https://bug-attachments.documentfoundation.org/attachment.cgi?id=158754
To put it into words, all you have to do is check "Detect special numbers" when importing — nothing else — and it works OK.
[Automated Action] NeedInfo-To-Unconfirmed
(In reply to c319chris from comment #17) > To put it into words, all you have to do is check "Detect special numbers" > when importing — nothing else — and it works OK. Glad you found a solution. Closing as RESOLVED NOTABUG