View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|14986||Bug reports||[All Projects] Import/Export||public||2019-06-17 15:17||2019-11-27 11:04|
|Target Version||Fixed in Version|
|Summary||14986: spss export fails on unrecognized or invalid variable format. (error 2265)|
I'm trying to export the responses of a survey to SPSS. When I run the exported syntax I get this error: "(2265) Unrecognized or invalid variable format. The format is invalid. For numeric formats, the width or decimals value may be invalid."
(V123 is an array (Numbers) question with text input)
Of course this fails because SPSS has a maximum width of 16 decimals... The easy fix is to change the line to 'F20.16', but why is it 'F20.17' in the first place?
In the 'export.php' from an older version (v2.57.x) of Limesurvey - where I never had this error - I see these lines:
//Now get the query string with all fields to export
This "adjustment of the size-check" is not in the export.php of the 3.15.x version of Limesurvey. Is this missing check the cause of the problem?
|Tags||No tags attached.|
|Complete LimeSurvey version number (& build)||3.15.8+190130|
|I will donate to the project if issue is resolved||No|
|Database & DB-Version||Mysql 5.7.22-22|
|Server OS (if known)|
|Webserver software & version (if known)|
|PHP Version||PHP 5.6|
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
My $sColumn = 197558X162X1469SQ001_SQ001
The $minInteger is empty , so $maxIntegerLen =2 --> $maxIntegerLen = max([$integerMaxLen, $integerMinLen]);
To find the $maxDecimal, this query is executed
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.
The function returns $maxLen.".".$decimalMaxLen;
$maxLen = $maxIntegerLen + 1 + $decimalMaxLen;
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
So maybe these lines:
$maxDecimal = Yii::app()->db
can be changed to
$maxDecimal = Yii::app()->db
and now $decimalMaxLen becomes 4 because strlen(intval(2778)) = 4
Or maybe construct $decimalMaxLen directly with:
$decimalMaxLen = Yii::app()->db
Somehow SUBSTRING_INDEX changed to SUBSTRINGINDEX in the above... it should be SUBSTRING_INDEX (so with the underscore!)
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 :)
|2019-06-17 15:17||joost1982||New Issue|
|2019-06-17 17:08||joost1982||Note Added: 52430|
|2019-06-17 23:10||joost1982||Note Added: 52434|
|2019-06-17 23:14||joost1982||Note Added: 52435|
|2019-06-18 07:33||DenisChenu||Relationship added||duplicate of 13942|
|2019-06-18 09:55||joost1982||Note Added: 52437|
|2019-06-18 09:57||joost1982||Note Added: 52438|
|2019-06-18 11:43||joost1982||Note Added: 52439|
|2019-11-26 18:00||cdorin||Note Added: 54805|
|2019-11-26 18:00||cdorin||Assigned To||=> cdorin|
|2019-11-26 18:00||cdorin||Status||new => feedback|
|2019-11-26 21:15||joost1982||Note Added: 54814|
|2019-11-26 21:15||joost1982||Status||feedback => assigned|
|2019-11-27 11:04||cdorin||Status||assigned => closed|
|2019-11-27 11:04||cdorin||Resolution||open => fixed|
|2019-11-27 11:04||cdorin||Note Added: 54816|