Just a quick question: is everyone getting the output files from Kobo, when XLS is choosen as output, the numbers stored as text (at least integer types)?
Of course in Excel it’s not hard to remedy, however when you have a large form and potentially not very technical users the options to deal with it aren’t great either. I really wish Excel would have some default way of converting all the errors it flags as suspcious text to numbers all at once.
One can also export as CSV, but then when opening in Excel empty values shows up as n/a instead of just blank cells. SO again no ideal options it seems to me, unless someone has found aquick & dirty fix?
Then you could use VBA, but having to deal with macro-enabled macros just for that seems like an overkill…
What I usually do in cases like this in Excel is the following:
Hit shift on your keyboard while you select the first cell in the first row of the dataset where a number is stored as text
Find the last cell in the last row where a number is stored as text and click again
Scroll back to the cell you select in step 1, you should have the yellow exclamation mark context menu where you can choose to ‘convert to number’. Based on the size of your dataset and your computer’s processing power this will take a few seconds.
Just a quick question: is everyone getting the output files from Kobo, when XLS is choosen as output, the numbers stored as text (at least integer types)?
Of course in Excel it’s not hard to remedy, however when you have a large form and potentially not very technical users the options to deal with it aren’t great either. I really wish Excel would have some default way of converting all the errors it flags as suspcious text to numbers all at once.
One can also export as CSV, but then when opening in Excel empty values shows up as n/a instead of just blank cells. SO again no ideal options it seems to me, unless someone has found aquick & dirty fix?
Then you could use VBA, but having to deal with macro-enabled macros just for that seems like an overkill…
Francis
–
You received this message because you are subscribed to the Google Groups “Kobo Users” group.
Yes there are a couple workaround this. If I’m setting some sort of Excel analysis form, I also sometimes refer to the ranges with numbers stored as text as =INT(), which forces a conversion to integer (if that makes sense for that prompt of course).
My main concern is mostly about times when I won’t be handling the data myself, and may not even be in contact with those who will handle those data - of course one can pass along the instructions but depending on users…
Also the other problem with this is that with a long form, when you integer columns aren’t contiguous, you must do that for every block of integer - Excel (at least my version, but its 2013 so fairly recent) isn’t smart enough to let you do the whole thing at once, you must proceed block by block which is annoying…
Unless I missed something in Excel, there doesn’t seemed to be a way convert all those flags at once - the closest to it would be what you mentioned…
Francis
···
On Thursday, July 2, 2015 at 3:28:30 PM UTC-4, Francis Vachon wrote:
Hi there,
Just a quick question: is everyone getting the output files from Kobo, when XLS is choosen as output, the numbers stored as text (at least integer types)?
Of course in Excel it’s not hard to remedy, however when you have a large form and potentially not very technical users the options to deal with it aren’t great either. I really wish Excel would have some default way of converting all the errors it flags as suspcious text to numbers all at once.
One can also export as CSV, but then when opening in Excel empty values shows up as n/a instead of just blank cells. SO again no ideal options it seems to me, unless someone has found aquick & dirty fix?
Then you could use VBA, but having to deal with macro-enabled macros just for that seems like an overkill…
Hi Francis,
I’m using Excel 2013 which allows me to do all values in one go the way I had described, even if you have text in between integer columns. Excel is just not consistent with where it displays the context menu for converting numbers stored as text. When you select the entire range using the mouse (dragging a rectangle essentially) the context menu appears on the screen where the last bottom-right cell was selected. When using the keyboard (which is faster) the context menu only shows when scrolling back to the top-left corner of the selected range.
The important aspect is that the first cell selected has to be a number stored as text, otherwise it won’t work. That’s why CTRL+A or selecting columns or rows doesn’t work for converting numbers to text.
Yes there are a couple workaround this. If I’m setting some sort of Excel analysis form, I also sometimes refer to the ranges with numbers stored as text as =INT(), which forces a conversion to integer (if that makes sense for that prompt of course).
My main concern is mostly about times when I won’t be handling the data myself, and may not even be in contact with those who will handle those data - of course one can pass along the instructions but depending on users…
Also the other problem with this is that with a long form, when you integer columns aren’t contiguous, you must do that for every block of integer - Excel (at least my version, but its 2013 so fairly recent) isn’t smart enough to let you do the whole thing at once, you must proceed block by block which is annoying…
Unless I missed something in Excel, there doesn’t seemed to be a way convert all those flags at once - the closest to it would be what you mentioned…
Francis
On Thursday, July 2, 2015 at 3:28:30 PM UTC-4, Francis Vachon wrote:
Hi there,
Just a quick question: is everyone getting the output files from Kobo, when XLS is choosen as output, the numbers stored as text (at least integer types)?
Of course in Excel it’s not hard to remedy, however when you have a large form and potentially not very technical users the options to deal with it aren’t great either. I really wish Excel would have some default way of converting all the errors it flags as suspcious text to numbers all at once.
One can also export as CSV, but then when opening in Excel empty values shows up as n/a instead of just blank cells. SO again no ideal options it seems to me, unless someone has found aquick & dirty fix?
Then you could use VBA, but having to deal with macro-enabled macros just for that seems like an overkill…
Francis
–
You received this message because you are subscribed to the Google Groups “Kobo Users” group.
Hi @rahhal
Do you mind explaining in detail what exactly is the bug that you would like us to resolve. Please provide as much information as possible within your current context.
Stephane
Dear stephanealoo,
In Kobo form, we are using the column as a number but when we download data into Excel so all numbers behave as text …so if we have a large number so very difficult to change manually and same like with data formate so …please guide me
Welcome to the community! Kindly please be informed when you download the data in the xls format the numbers are set as text and you will need to change them to numbers as outlined in post 4.
Hi Kaleem.
This issue has been noted by most of the users. I can confirm that this is not a problem with the KoBoToolBox system but rather it is an Excel-related element on how it handles various data types. Just to explain, excel uses the first few rows to determine which data type to allocate to a column; regardless of the source of data. When this happens, any presence of a single cell that mimics a text will automatically make Excel convert that entire column into a text. The main issue being text-based columns are the only ones that would keep all data types without deleting content e.g. if you had text in a numbers column, the text would be deprecated if you moved it to more advanced data analysis platforms such as SPSS, Stata, etc. You can read more about this on the following third-party link.
The workaround is to do what @Kal_Lam has highlighted which you can even automate using macros in excel for cases where you do this on a routine basis.
Hello @Kal_Lam and @tinok
It’s an old topic, but still relevant.
Could you help, please - maybe we missed something, but it doesn’t work with our Office Pro 2013, incl. Excel 2013. Even if we select 2 cells only.
Side-note: Why does KoBo generate in download (XLSX) all variables defined as integer (or decimal) in the form also as text type? But you generate numeric type for internal variables in the download, e.g. _id and _index.
Unfortunately, there is no automatic conversion of those numeric “text” variables on SPSS import.
