- Posts: 6
- Thank you received: 0
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
Allow leading zeros in the exported excel file
- nunochambellima
- Topic Author
- Offline
- New Member
Less
More
1 year 9 months ago #230037
by nunochambellima
Allow leading zeros in the exported excel file was created by nunochambellima
Please help us help you and fill where relevant:
Your LimeSurvey version: Version 2.05+ Build 140915
Own server or LimeSurvey hosting: Own Server
Survey theme/template: Customized
==================
Hi,
I'm having a problem exporting the values entered in a short text box type of question.
I need a type of question that allows the users to enter, if it is the case, one leading zero.
Numerical type of questions don't allow that, inasmuch as they "eat" the zero.
In the text box I used a regular expression for the validation of the data: /^[0][1-9][0-9][0-9][0-9]|[1-9][0-9][0-9][0-9][0-9]/
The data entered should always have 5 digits, should have only one leading zero, if it is the case. To meet these requirements, besides using the mentioned Regex, I've also setted the field to admit up to 5 characters only.
It all worked fine in the tests. But when I exported the results to an excel file, the leading zeros introduced per each user disapeard.
How can I solve this?
Thank you very much!
Your LimeSurvey version: Version 2.05+ Build 140915
Own server or LimeSurvey hosting: Own Server
Survey theme/template: Customized
==================
Hi,
I'm having a problem exporting the values entered in a short text box type of question.
I need a type of question that allows the users to enter, if it is the case, one leading zero.
Numerical type of questions don't allow that, inasmuch as they "eat" the zero.
In the text box I used a regular expression for the validation of the data: /^[0][1-9][0-9][0-9][0-9]|[1-9][0-9][0-9][0-9][0-9]/
The data entered should always have 5 digits, should have only one leading zero, if it is the case. To meet these requirements, besides using the mentioned Regex, I've also setted the field to admit up to 5 characters only.
It all worked fine in the tests. But when I exported the results to an excel file, the leading zeros introduced per each user disapeard.
How can I solve this?
Thank you very much!
Please Log in to join the conversation.
- Joffm
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 12943
- Thank you received: 3979
1 year 9 months ago - 1 year 9 months ago #230043
by Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Replied by Joffm on topic Allow leading zeros in the exported excel file
This is the behaviour of Excel
Excel recognises a number
Usually there are no leading zeros in a number.
Therefore Excel removes them.
You have to set a special format in these Excel columns.
Joffm
And you will get only little help for your nearly 8 years old version.
Nearly nobody will have an installation of it.
Excel recognises a number
Usually there are no leading zeros in a number.
Therefore Excel removes them.
You have to set a special format in these Excel columns.
Joffm
And you will get only little help for your nearly 8 years old version.
Nearly nobody will have an installation of it.
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Last edit: 1 year 9 months ago by Joffm.
Please Log in to join the conversation.
- nunochambellima
- Topic Author
- Offline
- New Member
Less
More
- Posts: 6
- Thank you received: 0
1 year 9 months ago #230058
by nunochambellima
Replied by nunochambellima on topic Allow leading zeros in the exported excel file
Thank you very much.
The problem, though, is previous to setting the excel column into a format type allowing leading zeros. It happens during the exportation. When I get to the excel file generated in the process it's already too late: the zeros just aren't there.
I know my company's LS version is totally outdated, but it is the one I have to work with.
Is this not happening in recent versions?
And isn't there a way of solving my problem in the older version?
Thank you very much for your help.
Nuno
The problem, though, is previous to setting the excel column into a format type allowing leading zeros. It happens during the exportation. When I get to the excel file generated in the process it's already too late: the zeros just aren't there.
I know my company's LS version is totally outdated, but it is the one I have to work with.
Is this not happening in recent versions?
And isn't there a way of solving my problem in the older version?
Thank you very much for your help.
Nuno
Please Log in to join the conversation.
- Joffm
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 12943
- Thank you received: 3979
1 year 9 months ago - 1 year 9 months ago #230059
by Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Replied by Joffm on topic Allow leading zeros in the exported excel file
Of course it happens, as it is not LimeSurvey related.
You know that Microsoft always thinks to know better what you want to do.
The same: When I enter "23/7" Excel "knows" that it is a date and changes to "23.Jul"
Or you create a macro and run it each time.
Another way:
I think you know how to force a string in Excel by prepending a "'".
So, as you enter your number in a "short text" you may use Expression Manager to prepend this string with "'".
Was Expression Manager already implemented in 2.05?
Joffm
BTW: Did you export to csv and had a look at that file?
You know that Microsoft always thinks to know better what you want to do.
The same: When I enter "23/7" Excel "knows" that it is a date and changes to "23.Jul"
And? So what?Just change the fomat of these columns. Work of 5 minutes.When I get to the excel file generated in the process it's already too late: the zeros just aren't there.
Or you create a macro and run it each time.
Another way:
I think you know how to force a string in Excel by prepending a "'".
So, as you enter your number in a "short text" you may use Expression Manager to prepend this string with "'".
Was Expression Manager already implemented in 2.05?
Joffm
BTW: Did you export to csv and had a look at that file?
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Last edit: 1 year 9 months ago by Joffm.
Please Log in to join the conversation.
- holch
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 11661
- Thank you received: 2742
1 year 9 months ago - 1 year 9 months ago #230083
by holch
I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.
Replied by holch on topic Allow leading zeros in the exported excel file
Yeah, my first idea was also exporting to CSV. But I bet MS will do the same thing as soon as you import the CSV into your excel.
So probably the best way to solve this issue with leading zeros (beside MS getting their shit together, which is probably not an option... ) is Joffm's idea with adding a ' to the beginning of the string. This should force Excel to think it is text rather than a number.
But just like Joffm said: Not a Limesurvey problem, but a MS Excel problem. I am sure in the CSV and SPSS exports the leading zeros will show up.
So probably the best way to solve this issue with leading zeros (beside MS getting their shit together, which is probably not an option... ) is Joffm's idea with adding a ' to the beginning of the string. This should force Excel to think it is text rather than a number.
But just like Joffm said: Not a Limesurvey problem, but a MS Excel problem. I am sure in the CSV and SPSS exports the leading zeros will show up.
I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.
Last edit: 1 year 9 months ago by holch.
Please Log in to join the conversation.