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?
1 Like
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).
This is one area where DevonThink gets things horribly wrong in my opinion - and it differs from the behaviour in numbers.
If I import my bank statements in CSV format and and the date column contains rows with, for example, the following date values:
28/02/2023
11/03/2023
When first displayed in DevonThink, as a file of kind “comma-separated values” these dates appear as:
28/02/2023
11/03/2023
i.e. in descending character order which is correct, because I haven’t told DevonThink that they are dates.
If I then tell DevonThink that the column is of type: date and format: date, DevonThink then displays the two dates as:
28 Feb 2023
3 Nov 2023
Clearly this is badly wrong. Behind the scenes DevonThink has rewritten the CSV file to have a column header of:
“Date#date{format:date}”
And the dates in each of the two rows are now stored as:
“28/03/2023”
“11/03/2023”
Which is fine, and is just the way in which you would normally indicate in a CSV file that a value really is a string.
Weirdly DevonThink is still sorting the column of dates using the underlying string representation because, when ordered in descending order, “28 Feb 2023” comes above “3 Nov 2023”.
The serious error is that the value of the date has been changed to an incorrect one. It looks like the interpretation of the date format to use (dd/mm/yyyy vs mm/dd/yyyy) is performed on a line by line basis rather than for the date column as a whole.
Changing the format of a column doesn’t affect its current data, only how it’s handled/displayed. In the end CSV is a text-based format and the current representation of the dates is ambiguous in some cases.
I’d class this as quite a serious bug. Because, although the CSV format doesn’t define a date representation, DevonThink is confidently and inconsistently performing a conversion from string to date without warning the user that there might be a problem. My locale information is available to it to use from Mac OS. But I would prefer it to refuse to make the conversion to date format rather than be inconsistent with a guarantee that some of the dates that it is displaying are incorrect.
And what’s the date format of your system?
The option to “Set time and date automatically” is on its default setting of “On” in the General → Date & Time Mac OS “System Settings”.
Which gives a date and time format of “22/05/2023 14:41:33”.
Which is what I would expect being in the UK.
Relying on the locale information isn’t necessarily the right thing to do either (although perhaps in most cases it is better than nothing).
But the problem that DevonThink has is that, without asking the user what they want to do, it has no way to know what a column containing, for example, the following dates actually means: 1/5/2023, 2/5/2023, 12/11/2023.
There is a chance that it will be either completely right or an equal chance that it will be completely wrong. I think that the other possibility, that the column contains dates in mixed formats, can be ignored because it is reasonable to assume that any tabular input (such as CSV) ought to have a consistent format in any column in which we are to assume that there exists a “type” - otherwise even a human reader would be unable to know what the column contained.
I want to hang on the topic because I have a similar problem.
The sort order in the csv table is strange.
My date format is yyyymmdd in macOS. The column is formatted as date. See example, The first line is imho out of the right order.
Is the sheet actually sorted by this column? There’s no indicator visible on the screenshot and by default the order is unsorted.
An column formatted as text looks this way:
At least this seems to be the right sorting, any chance that you could send us the document? Thanks!