CSV export

Hi,

Would it be possible to get a CSV export with the QA and CP corrections removed from the QA/CP column and put in their own separate columns?
Currently when data is exported, any reading with a correction is non-numeric and therefore can't be tallied/manipulated as numeric values.

Thanks

Doubt this is any easy fix in the export as I'm guessing due to the nature of corrections QA/BI/CP will just be text/String/VARCHAR in the system.

It would need a bit of parsing / regex to extract the correction, but as you could type anything what would you parse on? I know the norm is to put 12+1 or 12-1, but what if someone put 12 - 1 or hit a typo and put 12=1 (- and = are next to each other, = and + same key).

I'm guessing a whiz with excel might have more joy?

EDIT: Just done a quite export of my results to check what I said wasn't rubbish, excel trys to be too clever which doesn't help, I had a QA of 4-1, it had this as 04-Jan which confused me for a bit!

Phil

Hi, Phil is correct I'm not sure it would be simple to process the field as part of the export because of the 'free form' entry of the data. However as you say manipulation in excel is possible. One tip that will stop excel 'interpreting' the file is to rename it so it ends in '.txt' rather than '.csv', why ? well as Phil noticed excel tries to be clever because it thinks it understands csv files, if you rename the file as a txt file and then open it in excel you can then tell it to interpret the file data as csv, however it also allows you to define what is in each field i.e tell it the QA is a text field NOT a date field or a value field. Once you have the QA field as text there are a number of text manipulation functions that allow you to split the data assuming you have been consistent in using no spaces between the '-' and '+' or have always used spaces between them. I will have a think about the best formulas to use in excel and post them here if I can come up with something that works

This is not necessarily a hard fix. The current dafne stats and graphs must have these readings separated to do the calculations.
I would hope that they are already held separately in the database. It would be bad database design to hold figures to be used in calculations in text fields with special characters.
If they are held in separate fields, it should be easy to add them to the export.

The following will handle the data returned in the csv file, with or without spaces around the + or - and return the total. You'll need to replace M7 with the cell containing the data which can be done by Replace function with "Look In" set to formula. There's probably a more concise way of doing this, but this does work.

PS. I like having the corrections with the data in a single cell as it makes it easy to handle it all in one string, but the I'm not looking to undertake calculations but instead to change the display to align breakfast, lunch, dinner and bedtime readings.

=IF(ISNUMBER(M7),M7,IF(ISERR(FIND("+",M7)),LEFT(M7,FIND("-",M7)-1)-RIGHT(M7,LEN(M7)-FIND("-",M7)),LEFT(M7,FIND("+",M7)-1)+RIGHT(M7,LEN(M7)-FIND("+",M7))))