CSV reading with annoying date format

I am trying to read a csv into my obsidian but I am struggling with the date format. My default date format is YYYY-MM-DD in obsidian and my windows format is DD/MM/YYYY. However, the csv comes as MM/DD/YYYY HH:MM and whilst reading the csv, the dates come through as strings making it impossible to do things like filter between 2 dates.

I tried using “csvtable” (3rd party plugin) and also “dataview”

Is there a way of parsing the column with the dates telling obsidian on the way in the string is a date with the format MM/DD/YYYY HH:MM ?

Any help would be much appreciated for this new Obsidian user (less than 1 week)

What I’m trying to do

Some background. I have a long csv that looks like this

921961bf-1e44-44fd-8c98-ef55b4b878d0,01/01/2017 00:00:00,New Year’s Day,NONE,USD
a42ea17b-1756-4837-a64b-e97eff2f846c,01/02/2017 00:00:00,New Year’s Day (Observed),NONE,USD
3dfd4aa2-d937-4751-be42-efe7e2833feb,01/03/2017 14:45:00,S&P Global Manufacturing PMI,MEDIUM,USD
ebcbd94b-1ff0-49c3-ba40-db5a0802b305,01/03/2017 15:00:00,Construction Spending (MoM),MEDIUM,USD
d1f248e0-cb0a-400b-9e82-e72dd62294ca,01/03/2017 15:00:00,ISM Manufacturing PMI,HIGH,USD
8823790e-8ef9-4b04-9e89-5a9eb3f488a8,01/03/2017 15:00:00,ISM Manufacturing Prices Paid,HIGH,USD
60253963-e755-47a2-a7d9-ca364fc9195d,01/03/2017 16:30:00,3-Month Bill Auction,LOW,USD
0f70d5ca-22c8-4348-9086-a6fba0d1aa5e,01/03/2017 16:30:00,6-Month Bill Auction,LOW,USD
5a63c920-62ae-4e26-9c0a-6c78a6d7fde3,01/03/2017 18:00:00,4-Week Bill Auction,LOW,USD
3cf9e78c-50f0-4657-9016-57337cf30308,01/03/2017 18:00:00,52-Week Bill Auction,LOW,USD
bccd4126-9d62-4b2a-be58-cec0815bf02e,01/04/2017 12:00:00,MBA Mortgage Applications,LOW,USD
0de2e021-1e73-42bd-bbe3-d07d10b7cd45,01/04/2017 13:55:00,Redbook Index (MoM),LOW,USD

and I am trying to make sure I can pull out the relevant entries for my journal day.

Things I have tried

I would first process the file in a text editor that can use regular expressions in search-replace. You can use that to reformat the dates.

If you are still making new CSVs, I’d look into changing settings to produce the correct date format.

I copied some of your lines into a MyCSV.md file, and in another file I had this query:


const result = await dv.io.csv(dv.current().file.folder + "/MyCSV.md")
result.mutate(row => row.sdate = dv.func.date(row.start, "dd/MM/yyyy HH:mm:ss"))

dv.table(["Id", "start", "Name", "Impact", "Currency"],
  result.map(row => [row.id, row.sdate.toFormat("yyyy-MM-dd HH:mm:ss"), row.name, row.impact, row["currency-"]]))

And this produced the following output:

Here I’ve used the date(text, format) to reformat the date string into a proper date. A similar approach could also be done using moment(), if one would rather like to work with moment()-dates.

I’m not sure if it was my copying, but the last column was named "Currency " or "currency-" after sanitasion, due to an extra space at the end.

Hope this helps you move forward. Note that the dv.func.date() is the same as using the date() function within an ordinary DQL query of dataview.