In the survey table in my database the values of the variable are normal (3.2, 1.7 etc.), however when I export the results to SPSS or Excel there are a lot of decimals added... maybe this is causing the problem?

I think I found the problem. It is in /application/helpers/export_helper.php
(https://github.com/LimeSurvey/LimeSurvey/blob/6f2d32c2a47665c41ff81bfe1eb1af43cd03ffb1/application/helpers/export_helper.php)
in the function 'numericSize' starting from line 2213. This function returns $maxLen.".".$decimalMaxLen; Which is 20.17 for the variable where the import goes wrong.

My $sColumn = 197558X162X1469SQ001_SQ001
To find the $maxInteger this query is executed: SELECT MAX(197558X162X1469SQ001_SQ001) FROM xxxxxxxx.lime_survey_197558;
Which results in '90' (this is strange, because one respondent entered 160.3 which should be the MAX, but maybe this is caused by the column type which is text, so the outcome of MAX is not right).
However, with '90' the $integerMaxLen becomes '2' --> $integerMaxLen = strlen(intval(90));

The $minInteger is empty , so $maxIntegerLen =2 --> $maxIntegerLen = max([$integerMaxLen, $integerMinLen]);

To find the $maxDecimal, this query is executed
SELECT MAX(REVERSE(CAST(ABS(197558X162X1469SQ001_SQ001) - FLOOR(ABS(197558X162X1469SQ001_SQ001)) as CHAR))) FROM xxxxx.lime_survey_197558;
This gives: 81000000000000002.0

One respondent entered 3.2 in the array, and somehow ABS(197558X162X1469SQ001_SQ001) - FLOOR(ABS(197558X162X1469SQ001_SQ001)) gives '0.20000000000000018' for this respondent. I expected an outcome of 0.2 because 3.2 - 3 = 0.2, but somehow a lot of extra decimals and an extra '18' are added to the end.
CAST() as CHAR and REVERSE() makes this '81000000000000002.0'. and now the $decimalMaxLen becomes 17
--> $decimalMaxLen = strlen(intval($maxDecimal)); --> $decimalMaxLen = strlen(intval(81000000000000002.0)); --> = 17

The function returns $maxLen.".".$decimalMaxLen;

$maxLen = $maxIntegerLen + 1 + $decimalMaxLen;
$maxLen = 2 + 1 + 17; = 20

so this function returns 20.17.

So I found out where the problem is caused, but I don't understand why it is caused or how I can solve it. I don't understand enough PHP to see what is going on.

PS. The problem only exists in the export to SPSS. When exported to html, csv or xlsx the values are all correct. That is why I started looking for SPSS export functions. That led me to 'export_helper.php' and in SPSSGetValues() I found numericSize() where I think the problem is caused.

I've made a fiddle with a replication and possible solution of the problem: http://www.sqlfiddle.com/#!9/117c55/1

• The first column shows the values as they are stored in the databases (i.e. the values entered by the respondents).
• The second column shows what happens when you are trying to substract floating numbers with this commands: ABS(197558X162X1469SQ001_SQ001) - floor(ABS(197558X162X1469SQ001_SQ001). A lot of extra decimals and seemingly random numbers appear at the end. Those extra numbers create problems later on: using CAST() as CHAR, REVERSE() and MAX() to define $maxDecimal as in line 2250 of https://github.com/LimeSurvey/LimeSurvey/blob/6f2d32c2a47665c41ff81bfe1eb1af43cd03ffb1/application/helpers/export_helper.php#L2250 will make it '81000000000000002.0' and the $decimalMaxLen (line 2256) becomes 17 because strlen(intval(81000000000000002.0)) = 17.
• The third column shows a possible solution by casting the value to a decimal first. This is however problematic because we do not know a priori how many decimals the respondents entered.
• The fourth column shows my final solution by using SUBSTRING_INDEX(). This splits the number in front and after the dot ('.').

So maybe these lines:

$maxDecimal = Yii::app()->db
->createCommand("SELECT MAX(REVERSE(CAST(ABS($castedColumnString) - FLOOR(ABS($castedColumnString)) as CHAR))) FROM {{survey_".$iSurveyId."}}")
->queryScalar();

can be changed to

$maxDecimal = Yii::app()->db
->createCommand("SELECT MAX(CAST(SUBSTRINGINDEX($castedColumnString, '.', -1) as UNSIGNED) ) FROM {{survey".$iSurveyId."}}")
->queryScalar();

and now $decimalMaxLen becomes 4 because strlen(intval(2778)) = 4

Or maybe construct $decimalMaxLen directly with:

$decimalMaxLen = Yii::app()->db
->createCommand("SELECT MAX(LENGTH(SUBSTRINGINDEX($castedColumnString, '.', -1) ) ) FROM {{survey".$iSurveyId."}}")
->queryScalar();

Somehow SUBSTRING_INDEX changed to SUBSTRINGINDEX in the above... it should be SUBSTRING_INDEX (so with the underscore!)

http://www.sqlfiddle.com/#!9/117c55/15

Hello, @joost1982, sorry for the late reply!

Could you please confirm if everything is fine atm?

Everything is working fine now. See also: https://github.com/LimeSurvey/LimeSurvey/pull/1296 .

Perfect! Will mark it as solved :)