CSV Output field formatting

Can we get a better date format than the current one used in the CSV export.

Excel doesn’t really know what 2019-02-07T09:33:50Z is, and I’m not even sure why there is a T and Z in there.

Can we get it to be just 2019-02-07 09:33:50 (or some other agreed format that is recognised by other apps?)

1 Like

Although not ideal, there is a workaround you can use for now:

3 Likes

Ahhh thanks for that.

Dare I say it? Another example of ‘technical implementation’ over ‘user friendliness’?

Cheers for the links.

Another option is to first open the CSV file in your text editor of choice and run a grep find/replace to replace the T with a , and remove the Z on all lines:

replace:
^([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])T([0-9][0-9]:[0-9][0-9]:[0-9][0-9])Z$

with: \1,\2

You could also do this directly on the command-line if you are familiar with Unix tools (I’m not familiar enough to come up with the right incantation without a bit of work!). It’s an extra step, but doesn’t take very long, especially after the first time.

1 Like

Yup could do that.

Again - tech vs ‘average joe’ user … this isn’t really a good solution.

But thanks for the suggestion.

1 Like

Late to the thread, but I was researching this for myself. I had to figure out a way of converting the ISO8601 date/time value to something Apple Numbers can understand. I thought I would share my solution in case someone else is looking for it. I added a column to the table with this formula on every cell:

IFERROR(DATEVALUE(SUBSTITUTE(SUBSTITUTE($created,"Z",""),"T"," "))+TIMEVALUE(SUBSTITUTE(SUBSTITUTE($created,"Z",""),"T"," ")),"")

I’m sure there is a more efficient way to do it, but I hope this helps someone regardless.

3 Likes

+1 to this one! :point_up:

I would also like to see a better solution directly added to the CSV file rather than having to deal with fiddly workarounds which make it painfully slow and requires avoidable manual work. I hope to see this improved sometime soon – especially as I am a Monzo Business user and have a lot of bookkeeping to do :slight_smile:

FYI. I’m interested in finding a solution to this as I use Wave, which doesn’t accept the CSV file as it is because of the date format. Please consider one of these formats instead:

The date format might be in a format that is not recognized. Make sure you remove any future dates from your column. The day, month and year must be presented in one of the formats listed below:

  • YYYY/MM/DD (Example: 2013/07/21)
  • YYYY/DD/MM (Example: 2013/21/07)
  • DD/MM/YYYY (Example: 21/07/2013)
  • MM/DD/YYYY (Example: 07/21/2013)
  • Full month names and 3-letter month names (Example: Jan-24-2012 or January-24-2012)
  • ‘st’,‘nd’,‘rd’ or ‘th’ after day numbers (Example: 2012/January/5th)
  • DDMMYY (Example: 050212)
  • YYMMDD (Example: 120105)
  • YYDDMM (Example: 120501)
  • YYYYDDMM (Example: 20120501)

This is a bug in Wave not a Monzo issue at all. The have chosen a worldwide standard for very good reason.

Please Monzo do not move away from ISO to some of the ridiculous options Wave prefers.

2 Likes

@jph – thanks for sharing your point of view.

Based on your input I’m going to suggest to Wave that they start supporting the ISO 8601 standard (combined date and time representations). Hopefully doing that can lead to a solution :slight_smile:

I must say though that I’m not sure that agree with you on this. Well, I do understand that you prefer the ISO standard and why it’s generally good to stick to standards. However, in this case, there are clearly a lot of people that are struggling with this chosen date format as a lot of other software doesn’t seem to play well with it for whatever reason (ie. Excel, Numbers, Google Sheets, Wave and more). So although it’s good to stick to a standard, I wonder if it’s counterproductive in this case and if the standard is the right one…? (for example; a unix timestamp is another very common and very compatible standard/format).

Alternatively, I wonder if Monzo would consider both including the current ISO standard date format as well as one of the many suggested date formats that Wave have put forward [see list above] – just to make life easier for the many non-techy Monzo users :slight_smile:

Hope you can see where I am coming from, and again, thanks for sharing your point of view!

2 Likes

Agree with you fully here. It is a bit bemusing that you need VB to make excel play nicely with this format but at least its simple and very quick to do yourself.

To be honest they should be including a time zone too as there are hundreds of edge cases where just a date and time is ambiguous and can cause major bugs.

2 Likes

FYI. Tide offers different CSV formats for different use:

-perhaps Monzo could consider doing the same – just a thought! :thinking:

2 Likes

Good idea!

Even better, why not just do custom csv? You choose the fields that are included, the order and the format of each. No more hassle for Monzo and infinite power and flexibility for users.

2 Likes

YES! I’m 100% with you on this one! That would be excellent!

I just don’t dare to hope that Monzo will actually do this :upside_down_face:

UPDATE:

Unfortunately, it doesn’t look like there will be a solution to this from Wave’s side anytime soon – although they were happy to hear my suggestion:

“Thanks for putting this on our radar. To my knowledge, we’re not looking at changing the formats accepted under the date for .csv file uploads, but it’s good to get a thread going to see if there is any other interest from our users for this.”

Here is where I posted it:

1 Like

You do not need VB to get this to work in Excel.

At most you need the tex to columns button.

Which takes a few seconds at most to sort.

1 Like

If you are responding to my original post, I’m well aware of HOW to sort the formatting myself.

My point is, that it’s still a ‘techie’ task that many people won’t know or think to do.

If you are not responding to my original post… ehhh… ok! :smiley:

1 Like

Yes 100%, needing to import and manipulate it isn’t what a standard user would do.

I think the whole CSV export needs updating, time format, some of the merchant/payment details aren’t there

Also, I’d love to be able to automatically schedule their export (ideally upload to a cloud filestorage like Google drive / Dropbox etc.) But lacking this email it to me.

The budgeting feature in monzo is really poor and actually gives wrong information so I’ve had to create my own budgeting spreadsheet that keeps things up to date. But requires me to constantly download and upload the files

Timezone needs to be there though as transactions can occur in multiple time zones.

2 Likes