View Issue Details

IDProjectCategoryView StatusLast Update
14986Bug reports[All Projects] Import/Exportpublic2019-06-18 11:43
Reporterjoost1982Assigned To 
PrioritynoneSeverityminor 
Status newResolutionopen 
Product Version3.15.x 
Target VersionFixed in Version 
Summary14986: spss export fails on unrecognized or invalid variable format. (error 2265)
Description

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."
In the syntax there is this line:

/V123 F20.17

(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:

<begin lines>

//Now get the query string with all fields to export
$query = SPSSGetQuery($iSurveyID, 500, 0); // Sample first 500 responses for adjusting fieldmap
$result = $query->queryAll();

        $num_fields = 0;
        //Now we check if we need to adjust the size of the field or the type of the field
        foreach ( $result as $row )
        {

            foreach ( $fields as $iIndex=>$aField )
            {
                //Performance improvement, don't recheck fields that have valuelabels
                if ( ! isset($aField['answers']) )
                {
                    $strTmp = mb_substr(stripTagsFull($row[$aField['sql_name']]), 0, $iLength);
                    $len = mb_strlen($strTmp);

                    if ( $len > $fields[$iIndex]['size'] ) $fields[$iIndex]['size'] = $len;

                    if ( trim($strTmp) != '' )
                    {
                        if ( $fields[$iIndex]['SPSStype'] == 'F' && (isNumericExtended($strTmp) === FALSE || $fields[$iIndex]['size'] > 16) )
                        {
                            $fields[$iIndex]['SPSStype'] = 'A';
                        }

<end lines>

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?

TagsNo tags attached.
Complete LimeSurvey version number (& build)3.15.8+190130
I will donate to the project if issue is resolvedNo
Browser
Database & DB-VersionMysql 5.7.22-22
Server OS (if known)
Webserver software & version (if known)
PHP VersionPHP 5.6

Relationships

duplicate of 13942 resolvedc_schmitz R syntax file export appends 'unknown type' line to the end of the syntax file 

Activities

joost1982

joost1982

2019-06-17 17:08

reporter   ~52430

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?

joost1982

joost1982

2019-06-17 23:10

reporter   ~52434

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.

joost1982

joost1982

2019-06-17 23:14

reporter   ~52435

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.

joost1982

joost1982

2019-06-18 09:55

reporter   ~52437

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();

joost1982

joost1982

2019-06-18 09:57

reporter   ~52438

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

joost1982

joost1982

2019-06-18 11:43

reporter   ~52439

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

Issue History

Date Modified Username Field Change
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