Sorting rows of sheets by date in format "ddmmyy

I have received a sheet from my bank showing the different bookings. My problem is that the date field is in the German format dd.mm.yy. I would like to sort by date, but as an outcome I get records ordered like 01.02.21, 02.01.21, 03.04.21, … whereas I would like to see 02.01.21, 01.02.21, 03.04.21.
Is there any way to fix this in DT via automation by adding a new field which would receive the date in a format like yyyymmdd?

In what format? CSV, XLS, PDF, on paper and then scanned by you?

They offer the following formats:
CSV-CAMT-Format
CAMT-Format (booked actions)
CSV-MT940-Format
MT940-Format
I have tried them all. They are shown in DT3 as CSV Documents.
There is no difference between them regarding the date field.

What works even though it is a little bit cumbersome. To open it in Numbers, reformat the date field so that Numbers shows the date as yyyymmdd, and sort then.
As I don’t have to do it every day that would be a viable option for me.

I use an applescript to import the .csv data
This allows me to manipulate data like the date

edit: added sample script

tell application id "DNtp" ------------------------------------------Extract the content of the .csv file
	set theBankcsvFile to content record
	set theBankcsvName to name of the theBankcsvFile
	set theBankcsvText to plain text of theBankcsvFile
end tell

set oldDelims to AppleScript's text item delimiters --------------------------- Parse the file content by rows
set AppleScript's text item delimiters to {linefeed}
set theBankcsvRows to every text item of (theBankcsvText)
set AppleScript's text item delimiters to oldDelims

repeat with theBankcsvRow in theBankcsvRows --------------------------- Process each row 
	if theBankcsvRow > {} then
		set theNoteTitle to Process_Columns(theBankcsvRow) -------------- Parse the row content by columns
		tell application id "DNtp" ----------------------------------------------- Create Note
			set theNewRecord to create record with {name:theNoteTitle, type:txt, content:theBankcsvRow, tags:theNoteTags} in theFilingGroup
		end tell
	end if
end repeat

on Process_Columns(theBankcsvRow) ---------------------------------- Process Clolumns
             <<<<<  insert code to process content
end Process_Columns

This is what I’m looking for. Would you like to share it?

Numbers should be able to sort by date without reformatting it.

Maybe I used the wrong word. I told numbers that the Format is dd.mm.yy. That did the trick.

1 Like

Thank you. I’ll try it out.

Right. Basically, CSV data is string-only. So when Number or any other program imports them, they don’t know if a column contains dates or (for example) a peculiar marker according to a certain scheme (like in a library). So the safe way is to assume everything is just text. Then, if you assign a “format” (Number speak: it’s more like a data type in reality) to a column, the program knows how to sort them.

Now, if you import the sheet into DT (i.e. the CSV from your bank), DT will create a record that’ll appear exactly as it does in Numbers. You can right click on one date entry and then select “Spalten bearbeiten” in the context menu. Then you can specify that this column contains a date. Just like in numbers, but all in DT itself.

After that, clicking on the date column will sort the table (again, just like in numbers).

Is that what you were asking for? I’m wondering, because usually acount statements should be ordered by date already (at least mine are).