Date import and sort not working [SOLVED]

[MongrelShark]

Hi.

I have a largeish CSV I downloaded from bank, for my accountant. I need to be able to keep it sorted by date.

I have done significant hunting around the web to try and get this working. The common answer messes up my data really badly. In every variation I have been able to find.
I also see a lot of people saying this doesn’t work and no acceptable solutions in most of the threads!

I have tried the following.

Data> sort ascending or descending. Chose to extend at the pop up.
Result: Sorts by day only and ignores the month and year.

format>number>date
Sort as above, same result.

{Started googling}

data>text to data.
Set column as D/M/Y
Result: Immediately sorts column by day number only. Does not sort other columns at all. entire spreadsheet of data is ruined unless I ctrl+z
Attempting Data>sort makes no change. Months and years are ignored.

Import again with detect special characters.
this is nice because it changes from left to right justification from month to month. But its removed the half the year data on every right justified month. so 2021 becomes 21. But ts got the same sorting problems. Month and year ignored. No way to sort rows by date

Import and declare column as D/M/Y date format. with and without detect special characters.
Result. Imports the data with date column only arranged incorrectly. Dates are no longer correct for transactions. days only sorted. same as all above. This was by far the worst solution offered because it destroys all the data on import!!

Summery. After checking 20+ other help threads and confirming the above methods are the recommended way. Have found this does not work. every time it sorts by day only and always ignores the month and year. Furthermore. If you do it the import method. It will mess up your entire spreadsheet by moving dates only, and not moving the entire row the date is on. There is no way to undo this. You need to delete and start again. Hopefully you didn’t save!!!

Slightly off topic, is there a way to lock the rows? So if I do sort a column it will keep rows intact? For this spreadsheet I would not ever want to have any column sort without keeping rows intact.

I have attached 2 version of the file with all personal data removed. Its just a column of dates to sort. Added data in a few random cells as a form of checksum, to confirm rows are being sorted by date, and not just the dates being sorted.
one version is with special character detect, the other Raw version only had separate by comma on during import, which was required to get the columns to be detected.

date sort example raw.ods

example date sort detect special.ods

Update:

Found part of the problem.

If I click on a date cell. In the formula bar there is a ’ that does not show in the cell. Same as Problem with sorting dates

When importing I checked the other option and put ’ in the box. This imported the data correctly.

I couldn’t find a way to delete this character from all cells in the column. So a re-import is required. Hope you didn’t save over the original .csv… I did first time. Rookie mistake. Luckily I way able to re-download the raw text data.

However. If I try to declare the date column at import or after via text to data. It rearranges the format to m/d/y and sorts by month only ignoring the day and year. It also destroys the rows, then asks me if I’d like to extend the sort to other columns, but its too late. the spreadsheet is already corrupted due to rows not being maintained… It also changes 2021 to 21, which is not unworkable, but I don’t like that it just deletes my data without asking. I’d strongly prefer to keep year as 4 digits.

Possibly I have the date format set to M/D/Y in some other setting? This makes no sense as I always prefer D/M/Y as its the Australian standard format. Looking through my preferences while I wait for further help. will update next post.

Still no luck getting it to sort by whole date. Can only sort by whatever is before the first slash.

[MongrelShark]

went to Tools>options>language>set all appropriate locale details to Australia. Fixed my m/d/y problem. but still deleting the first 2 digits of the year. This is really frustrating as I need them for other documents, and might have to manually re-enter thousands of times!

Still cant sort by date.

[AdmFubar]

In that dialog you posted about the separator options, you really need the detect special numbers checked. This will import the dates formatted as dates and not as a string. This should sort you out, and your data.

[robleyd]

Can you provide an anonymised CSV sample leaving the dates unmodified? Half a dozen lines or so should be enough.

You can use a text editor, e.g. Notepad (for Win) or Kate (Linux) to do this.

Edit your question and use the paperclip to add the file.

[MongrelShark]

Tried adding a short and safe for public .csv, but its not an allowed file type.

[MongrelShark]

After changing my locale settings. all seems to be working as it should. Possibly a date format conflict?
Writing detailed answer…

[MongrelShark]

I got it working.

Seems I had 2 problems.

First was the apostrophe. ’

Solved with import options shown in original post. Select other and put ’ in the box.

This got the text imported without the ’ at the start of each date.

I was however still unable to get sorting to work correctly until I changed my program settings for calc. via Tools>options>language setting>Language Set everything I could to English (Australia) this automatically changed date format from American M/D/Y;M/D to D/M/Y;D/M

I can only speculate here, but suspect conflict with dates entered in cells as D/M/Y vs what calc thinks is the right way. Even though it changed all my dates in cells to M/D/Y I couldn’t sort properly.
I think a lot of other posters have had this problem and no solve that I could find. Its an incredibly reoccurring topic if you search for sort by date, and lots of threads never solved!!

Once the date format was the same throughout Calc it all worked fine. However there seems to be some slightly bad advice on how to do this IMHO. So I’ll go through the steps again my recommended way: Highlighting the part that can be frustrating to novice users.

Upon import. While you might have success with selecting detect special characters. and or defining the column as a date type. If the calc settings at Tools>options are not the same date format. the import will mess up the rows. dates will be auto sorted on import, and other columns will not be included. Your data might be really hard to recover at this point.

So if in options>locale you have M/D/Y and you set import column to D/M/Y. It could mess up your data! Same for detect special.
I have changed my options back to default and the problem is repeatable. Even after a successful import with correct settings.
Date settings in options must match date format used or it breaks stuff. Change date settings back to match data and you can sort again.
I was not able to get the example documents I uploaded to work, because both still had apostrophes. although the detect special only had them every second month. So I got it half working on that document.

What I recommend is importing as text. without the apostrophe or other charecters that shouldn’t be there. Then selecting the date column and Data>text to data. set column to desired format. While this is exactly what you could do upon import, and seems like repeating a step. It lets you undo if your dates get messed up upon changing text to data. I find this a lot less frustrating that it getting imported all messed up. Too many times I had the date column get sorted incorrectly on import, and the other columns didn’t get sorted. This could create some really serious mistakes! I also found it frustrating to the point I almost gave up and hired someone to sort my data! Small steps are great for novice users that might like to use undo!.

Another thing worth noting is the apostrophe is not actually in the original .csv if I open it with text editor (I’m using Gedit). so make sure you click a cell with dates and check the formula toolbar at top of spreadsheet. Make sure text in formula matches what should be in the cell. If you have extra charecters. Adjust import settings to suit.

Finally. This took me a while to solve, and longer to try and create a solve that can help more people in future, upvotes would be very appreciated. I only have 1 Kudo at time of writing.

Thanks to those that commented on the original post. It really helped me figure out what was going on.

[Wanderer]

In the dialog to import the csv simply click on the column head “standard” and change the column type with the drop-down one row higher.

As this will import/convert your text string to a integer number your problems with sorting will vanish, if all rows are imported right.

The Apostroph ’ is a generated signal to show you the value you are reading is text, not a number, so it is sorted as letters. This is a result, if your column was not detected as date. Manual selection is better, as there are difficulties to differ bitween Dec, 4th and April, 12th without adfitional knowledge.

After import you may select the column and use any FORMAT you like. Just for education: Use a number format and you can see, what is really in your cell, but you may use DD/MM/YYYY as before.

[erAck]

Of course if in the import dialog you have language/locale English-US that uses M/D/Y dates but your data has D/M/Y dates then things don’t work as expected because 12/04/2021 is imported as 2021-12-04 not 2021-04-12 and 13/04/2021 would be imported as text because 2021-13-04 would be an invalid date. Simply changing the column type from Standard to Date (DMY) upon import should had solved the problem.

However, see also this FAQ that also explains why the ' apostrophe shows up in the Input Line but is not a cell content.

Adding the apostrophe to import field delimiters isn’t even related.