Is there anyway to sort by filename that has dates within the filename without renaming the file?
The problem is the filename has different date formats, such as.
mm-dd-yyyy
yyyy-mm-dd
m-d-yyyy
etc.
most filename dates are at the end of the file, but I do have some at the start of the file.
It could be done using a script that extracts the dates into a separate column which you then sort by. Provided it's actually possible with all the different date formats you have (i.e. if you have files with both d-m-y and m-d-y type formats then the script would have to guess what's the day and what's the month sometimes).
Please link your account if you need more detailed help.
All are in formats in month first or year first, as shown below. I'll link my account if you can help me after with a script/solution. I've partially figured it out with adding columns but can't figure out the regex scripting part. Thanks
mm-dd-yyyy
m-d-yy
yyyy-mm-dd
yyyy-m-d
Trying to make a column that outputs all dates in this format so I can sort - yyyy-mm-dd
I'm assuming you have purchased Opus, since you've been asking questions on the forum for a few years now. Please link your account if so. It only takes a few seconds.
Couldn't find my past ones, but I have bought a few over the years. I just bought another version and linked, which I will need anyway for this issue for my other computers.
Any help I would appreciate it. Thanks!
Try this as a script:
Extract Dates to Column.js.txt (1.5 KB)
It'll add a new column under the Script category, and the column will use your system date format and sort correctly.
// Called by Directory Opus to initialize the script
function OnInit(initData)
{
initData.name = "Extract Dates to Column";
initData.version = "1.0";
initData.copyright = "(c) 2021 Leo Davidson";
initData.url = "https://resource.dopus.com/t/sorting-by-filename-with-different-date-formats/38827";
initData.desc = "Extract M D Y and Y M D dates from filenames into a column you can sort by.";
initData.default_enable = true;
initData.min_version = "12.24";
}
// Called to add columns to Opus
function OnAddColumns(addColData)
{
var col = addColData.AddColumn();
col.name = "DateFromName";
col.method = "OnDateFromName";
col.label = "Date from Name";
col.type = "date";
col.justify = "right";
col.autogroup = true;
}
// Regex based on wowbagger's post: https://resource.dopus.com/t/changing-filename-date-format-to-yyyy-mm-dd-issues/29170/14
var reMDY = /(^|\D)([0-1]?\d)[-.]([0-3]?\d)[-.]((?:19|20)?\d{1,2})($|\D)/;
var reYMD = /(^|\D)((?:19|20)?\d{1,2})[-.]([0-1]?\d)[-.]([0-3]?\d)($|\D)/;
// Implement the DateFromName column
function OnDateFromName(scriptColData)
{
var name = scriptColData.item.name + "";
var matches = name.match(reMDY);
var d = DOpus.Create.Date();
if (matches)
{
d.day = matches[3];
d.month = matches[2];
d.year = matches[4];
}
else
{
matches = name.match(reYMD);
if (!matches) return;
d.day = matches[4];
d.month = matches[3];
d.year = matches[2];
}
scriptColData.value = d;
}
It is only reading the files with date formats as yyyy-mm-dd and mm-dd-yyyy, and outputting the new column in m-d-yyyy.
Can you make it, so it reads m-d-yy and yyyy-m-d as well and outputs the column in yyyy-mm-dd?
7/1/2021 11:11 AM Extract Dates to Column: Error at line 39, position 3
7/1/2021 11:11 AM Extract Dates to Column: Invalid procedure call or argument (0x800a0005)
7/1/2021 11:11 AM Extract Dates to Column: Error at line 39, position 3
7/1/2021 11:11 AM Extract Dates to Column: Invalid procedure call or argument (0x800a0005)
7/1/2021 11:11 AM Extract Dates to Column: Error at line 39, position 3
7/1/2021 11:11 AM Extract Dates to Column: Invalid procedure call or argument (0x800a0005)
It should work with both already. What are your example names that don't work?
The output will be in your system date format, but will still sort correctly in date order, since the column is set to date type, not string type. You don't need it to be in yyyy-mm-dd format to get correct sorting, although you can have that if you really want it.
test 4 file 06-04-2021
Test 2021-5-6
test - 2020-5-20
Test - 4-8-21 - Copy
Test 6-24-21
Test 4-8-21
test file 3 - 6-27-21
Try this. It'll assume two digit years <50 are from 20xx and >= 50 are from 19xx:
Extract Dates to Column.js.txt (1.7 KB)
// Called by Directory Opus to initialize the script
function OnInit(initData)
{
initData.name = "Extract Dates to Column";
initData.version = "1.2";
initData.copyright = "(c) 2021 Leo Davidson";
initData.url = "https://resource.dopus.com/t/sorting-by-filename-with-different-date-formats/38827";
initData.desc = "Extract M D Y and Y M D dates from filenames into a column you can sort by.";
initData.default_enable = true;
initData.min_version = "12.24";
}
// Called to add columns to Opus
function OnAddColumns(addColData)
{
var col = addColData.AddColumn();
col.name = "DateFromName";
col.method = "OnDateFromName";
col.label = "Date from Name";
col.type = "date";
col.justify = "right";
col.autogroup = true;
}
// Regex based on wowbagger's post: https://resource.dopus.com/t/changing-filename-date-format-to-yyyy-mm-dd-issues/29170/14
var reMDY = /(^|\D)([0-1]?\d)[-.]([0-3]?\d)[-.]((?:19|20)?\d{1,2})($|\D)/;
var reYMD = /(^|\D)((?:19|20)?\d{1,2})[-.]([0-1]?\d)[-.]([0-3]?\d)($|\D)/;
// Implement the DateFromName column
function OnDateFromName(scriptColData)
{
var name = scriptColData.item.name + "";
var matches = name.match(reMDY);
var d = 0;
var m = 0;
var y = 0;
if (matches)
{
d = parseInt(matches[3], 10);
m = parseInt(matches[2], 10);
y = parseInt(matches[4], 10);
}
else
{
matches = name.match(reYMD);
if (matches)
{
d = parseInt(matches[4], 10);
m = parseInt(matches[3], 10);
y = parseInt(matches[2], 10);
}
}
if (!d || !m || !y)
{
return;
}
if (y < 50)
{
y = y + 2000;
}
else if (y < 100)
{
y = y + 1900;
}
var dobj = DOpus.Create.Date();
dobj.day = d;
dobj.month = m;
dobj.year = y;
scriptColData.value = dobj;
}
(Edit: Updated to fix parseInt radix mentioned below.)
Thanks this work perfect. In regards to the column format, I guess m-d-y is fine since its dated and can sort, but for my OCD how would I pad it to mm-dd-yyyy instead of m-d-yyyy.
Other than that, thank you so much Leo
EDIT – actually scratch the padding. You've done enough for me. Thank you for the help.
Found a small error.
Anything that has an 08 or 09 within the month or day, it won't output.
example
2020-08-16
2020-09-12
2020-05-08
04-08-2021
I'm also getting an error.
7/1/2021 12:36 PM Extract Dates to Column: Error at line 48, position 3 7/1/2021 12:36 PM Extract Dates to Column: Invalid procedure call or argument (0x800a0005) 7/1/2021 12:36 PM Extract Dates to Column: Error at line 48, position 3 7/1/2021 12:36 PM Extract Dates to Column: Invalid procedure call or argument (0x800a0005)t
I guess base 10 is missing in parseInt, e.g. parseInt(matches[3], 10).
Thanks, that was it. I modified that for both the month and the day.
// Called by Directory Opus to initialize the script
function OnInit(initData)
{
initData.name = "Extract Dates to Column";
initData.version = "1.1";
initData.copyright = "(c) 2021 Leo Davidson";
initData.url = "https://resource.dopus.com/t/sorting-by-filename-with-different-date-formats/38827";
initData.desc = "Extract M D Y and Y M D dates from filenames into a column you can sort by.";
initData.default_enable = true;
initData.min_version = "12.24";
}
// Called to add columns to Opus
function OnAddColumns(addColData)
{
var col = addColData.AddColumn();
col.name = "DateFromName";
col.method = "OnDateFromName";
col.label = "fdate";
col.type = "date";
col.justify = "left";
col.autogroup = true;
}
// Regex based on wowbagger's post: https://resource.dopus.com/t/changing-filename-date-format-to-yyyy-mm-dd-issues/29170/14
var reMDY = /(^|\D)([0-1]?\d)[-.]([0-3]?\d)[-.]((?:19|20)?\d{1,2})($|\D)/;
var reYMD = /(^|\D)((?:19|20)?\d{1,2})[-.]([0-1]?\d)[-.]([0-3]?\d)($|\D)/;
// Implement the DateFromName column
function OnDateFromName(scriptColData)
{
var name = scriptColData.item.name + "";
var matches = name.match(reMDY);
var d = 0;
var m = 0;
var y = 0;
if (matches)
{
d = parseInt(matches[3]);
m = parseInt(matches[2]);
y = parseInt(matches[4]);
}
else
{
matches = name.match(reYMD);
if (matches)
{
d = parseInt(matches[4], 10);
m = parseInt(matches[3], 10);
y = parseInt(matches[2]);
}
}
if (!d || !m || !y)
{
return;
}
if (y < 50)
{
y = y + 2000;
}
else if (y < 100)
{
y = y + 1900;
}
var dobj = DOpus.Create.Date();
dobj.day = d;
dobj.month = m;
dobj.year = y;
scriptColData.value = dobj;
}
Thanks, good catch! (Seems newer versions of JavaScript don't have this problem, but JScript is stuck with an ancient version of the standard.)
You'll probably need the extra argument on all six parseInt calls, not just those two of them. Anything with a leading zero will cause the problem, which could include two-digit years, as well as the MDY case in the block above the one you've modified.
I've updated my version of the script above.