Exporting transactions to CSV
morag
Member Posts: 7
I have just exported my Account Transactions report to CSV because I wanted to make some graphs with the numbers. When I look at the CSV generated though, all the numbers are actually strings with the currency symbol on the front. Is there a way to export all my transaction data as a CSV with actual numbers or do I have to manually edit every transaction in the CSV in order to make use of the data?
in Using Wave
0
Comments
Hi @morag . Which program are you opening your file with? Can you try opening it with Google Sheets or Apple Numbers? This should solve your problem.
I am opening it with Excel. I don't have Google sheets or Apple Numbers. My question was about the export step. If I view the CSV raw data, the numbers are not numbers, the string "NZ$" is pre-pended on each number making it into a string. Many (but oddly not all) of them have double quotes round them too. It's great to hear that Google Sheets and Apple Numbers are able to interpret such strings as numbers where Excel is not able to, but shouldn't Wave be exporting this data as a number in the first instance? Why would anyone want to work with the data not as a number?
@morag Thanks for further clarifying! I was chatting with someone about this recently and I think what you are referring to is the fact that in one cell of the export to excel, you'll see the value is 234NZ$ rather than 234 in one cell and NZ$ in another. While I can't speak to why the export was created this way (although I'm curious myself and am happy to try to get a more firm reasoning), you can format the cells to split the amount and the currency apart by using a formula in excel. For a lack of better words, I'd call it the "Right Formula" - That's right like the direction! This article explains how to complete it in Excel and should be helpful in being able to split out the values and create tables and graphs using the values: https://exceljet.net/excel-functions/excel-right-function.
Actually the value is NZ$234 rather than the other way around, but yes, essentially. I have found an Excel formula that strips it off already by now, but the question still stands - why would Wave do this in the first instance. Clearly numbers are going to want to be manipulated in a spreadsheet as numbers. It would be an improvement to Wave to change it to export the actual numbers. It one of those little things that is just a tiny black mark. On it's own it is nothing much, but if you get too many it leaves a bad feeling. It would be very easy to fix, just as it was fairly easy for us to fix post-export, but that's not the point. The point is that we shouldn't have to. Wave is great in many places, and I'd like to see it get better in the other places.
Hey there @morag
Apologies that you're CSV formatting is a bit off. In my experience with downloading CSV's for clients or myself, they have always formatted in pretty well to the point where I can copy and paste from a regular text document into an excel doc quite seamlessly. I'm using a new macbook and Google Chrome.
When you download the CSV are you right clicking on it, and opening it in Excel? Or are you opening it up in a text document first? Or are you importing it from a blank excel document? I'd try a few different options and seeing whether this continues to happen.
Hi there @Barsin, I have done all of the above. Viewing the CSV in notepad shows the issue, that the values I expect to be numbers are in fact, for the most part, single quoted strings. Opening in Excel, either by double clicking (since the CSV file extension is linked to Excel on my machine) or by importing once Excel is open, has the same result (as you'd expect). Excels processing of this CSV file is forgivable given the contents of the CSV. It's the export to CSV that I question the validity of.
I mentioned this in another post:
https://community.waveapps.com/discussion/5097/google-sheets-integration#latest
That was about the google sheets integration rather than CSV export but looks like the same issue. It appears to work as numbers for USD and EUR but it seems to do that perhaps through number formatting as it is only symbol (other things may be ok too), if it is an alpha currency code, in this case a mix but has "NZ$" or in my case "AED" it appears as text/string. I think the best solution would be to show nothing in the cells and have currency code as column headings, you don't see published financial statements or most MIS with it in every cell.
BTW, this also seems to mess up negative numbers in some cases, or at least I am guessing the reason is associated.
The other thing I would note is this seems at least partially limited to reports. I would have to test further but pretty sure I downloaded invoices (mostly just to see what came out) and they were ok but can't remember what was actually contained.