Ask Your Question
0

Date import and sort not working [SOLVED] [closed]

asked 2021-04-12 04:55:10 +0200

MongrelShark gravatar image

updated 2021-04-12 07:02:11 +0200

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.

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

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

{Started googling}

3. 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.

4. 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

5. 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.

C:\fakepath\date sort example raw.ods

C:\fakepath\example date sort detect special.ods

Update:

Found part of the problem.

If I click on ... (plus)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by PKG
close date 2021-04-12 07:05:00.422579

Comments

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.

MongrelShark gravatar imageMongrelShark ( 2021-04-12 05:43:24 +0200 )edit

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. :)

AdmFubar gravatar imageAdmFubar ( 2021-04-12 05:53:31 +0200 )edit

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.

robleyd gravatar imagerobleyd ( 2021-04-12 06:01:34 +0200 )edit

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

MongrelShark gravatar imageMongrelShark ( 2021-04-12 06:16:19 +0200 )edit

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

MongrelShark gravatar imageMongrelShark ( 2021-04-12 06:19:09 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2021-04-12 06:56:45 +0200

MongrelShark gravatar image

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.

image description

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

image description

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 ... (plus)

edit flag offensive delete link more

Comments

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.

Wanderer gravatar imageWanderer ( 2021-04-12 09:30:07 +0200 )edit

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.

erAck gravatar imageerAck ( 2021-04-13 16:17:31 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2021-04-12 04:55:10 +0200

Seen: 28 times

Last updated: Apr 12