View Issue Details

IDProjectCategoryView StatusLast Update
16723Bug reportsResponse browsingpublic2021-03-08 19:38
ReporterSelcal Assigned Toc_schmitz  
PrioritynoneSeveritypartial_block 
Status closedResolutionfixed 
Product Version4.3.19 
Fixed in Version4.4.12 
Summary16723: SPSS data export does not include any delimiters after lastpage field
DescriptionWhen exporting response data to SPSS (v16 or newer, all responses), the SPSS_data_file does not correctly export the delimiters.
In the attached sample, both v4.3.19 and v4.3.18 stop inserting delimiters after the lastpage field.

I also have a slightly more complex survey, where the delimiters are included for more fields, but not trailing delimeters (ie, when the last questions on the export line are empty, nothing is exported leading to insufficient fields in the export). I can't include that example as it contains sensitive data.

The attached simple sample demonstrated the problem well enough though. It already includes test answers.
Steps To Reproduce-Open attached survey (two attached, one English, one Dutch, just to see if it was regional).
-Export to SPSS, V16 or newer, all responses, limit/offset/no answer fields empty
-Open data file, find delimiters missing.
Additional Information- I've attached a screenshot of file comparison between V4.3.3 (working -- middle file), and V4.3.18, V4.3.19 (left and right panes).
- I've attached the SPSS exports, from V433 (correct), V4318 & V4319 (incorrect).
TagsNo tags attached.
Complete LimeSurvey version number (& build)Version 4.3.19+201005
I will donate to the project if issue is resolvedNo
Browser
Database & DB-VersionMariaDB, 10.3.23
Server OS (if known)Linux
Webserver software & version (if known)Apache
PHP Version7.4.9

Activities

Selcal

Selcal

2020-10-07 10:57

reporter  

spss_delimiters.jpg (211,759 bytes)
survey_V4319_668368_SPSS_data_file(4).dat (240 bytes)   
,,,,,,,,,1,'07-10-2020 09:57:15',1'nl''202616330''J63iJu1zErrCRLF''07-10-2020 09:57:05''07-10-2020 09:57:15''1''2''3''4'
,,,,,,,,,2,'07-10-2020 09:58:29',1'nl''1670669681''FJr538zX90txM8Y''07-10-2020 09:58:25''07-10-2020 09:58:29''1''2'
survey_V4318_668368_SPSS_data_file(1).dat (240 bytes)   
,,,,,,,,,1,'07-10-2020 09:57:15',1'nl''202616330''J63iJu1zErrCRLF''07-10-2020 09:57:05''07-10-2020 09:57:15''1''2''3''4'
,,,,,,,,,2,'07-10-2020 09:58:29',1'nl''1670669681''FJr538zX90txM8Y''07-10-2020 09:58:25''07-10-2020 09:58:29''1''2'
survey_V433_668368_SPSS_data_file.dat (268 bytes)   
,,,,,,,,,'1','07-10-2020 09:57:15','1','nl','202616330','J63iJu1zErrCRLF','07-10-2020 09:57:05','07-10-2020 09:57:15','1','2','3','4',
,,,,,,,,,'2','07-10-2020 09:58:29','1','nl','1670669681','FJr538zX90txM8Y','07-10-2020 09:58:25','07-10-2020 09:58:29','1','2',,,
Selcal

Selcal

2020-11-05 11:56

reporter   ~60557

I have now downgraded to 4.3.15 (prior to the updates to the SPSS export), and can confirm the export works properly on this version.
c_schmitz

c_schmitz

2020-11-05 17:02

administrator   ~60568

This should be fixed in the latest version. Can check with that please?
Selcal

Selcal

2020-11-06 10:51

reporter   ~60579

I've just run a check with 4.3.25, but unfortunately the export still isn't working properly.

The issue seems to be with trailing string fields on each row (when no more numeric occurs on the row): no delimiters are inserted for those.

To illustrate, the pattern I see, is if the fields to be exported are like this example 12 column row structure. (Calling numbers int, text str for simplicity):
1(int),2(str),3(int),4(int),5(int),6(int),7(str),8(int),9(str),10(str),11(str),12(str)

Then delimiters work up to field 8. However, the strings from position 9 (inclusive) have no delimiters anymore (and so if those fields are empty, nothing is shown at all).
 Ie:
123,'foo2',345,456,567,678,'bar7',890'foo9''foo10''foo11''foo12'
(Or, if field 11 and 12 are empty:)
123,'foo2',345,456,567,678,'bar7',890'foo9''foo10'

I can't publish the actual data from this survey, but I could fairly easily reproduce it before. I'll try to get an example up but that may be after the weekend.
Selcal

Selcal

2020-11-06 11:09

reporter   ~60580

Couldn't help myself, so I set up a test now. The above is not correct, but I really can't find the pattern.

I've attached an 2 example survey archives with answers (one with 2 groups, one with 1), and the SPSS export. Note the missing delimiters. In this case, it's for all the fields... I can't find why on some production surveys they partially come up.
survey_841825_SPSS_data_file.dat (289 bytes)   
,,,,,,,,,1,'06-11-2020 10:59:02',1,'nl','1178055172','jZT2XaO7VQAeK6n','06-11-2020 10:58:36','06-11-2020 10:59:02''foo''bar''foo''bar'111'foo'222'bar''foo'
,,,,,,,,,2,'06-11-2020 10:59:32',1,'nl','617589883','3WInDFdTu0CHBXZ','06-11-2020 10:59:14','06-11-2020 10:59:32''foo'111'bar'222
survey_814171_SPSS_data_file.dat (292 bytes)   
,,,,,,,,,1,'06-11-2020 11:03:53',1,'nl','1932795011','hqV7jjuAtVf8cYj','06-11-2020 11:03:34','06-11-2020 11:03:53''foo''bar''foo''bar'111'foo'
,,,,,,,,,2,'06-11-2020 11:04:35',2,'nl','1290995659','YvotzLHiViJRfR3','06-11-2020 11:04:11','06-11-2020 11:04:35''foo''bar'111'foo'222'foo''bar'
Selcal

Selcal

2020-11-25 00:01

reporter   ~60753

Ran a test update to 4.3.28, unfortunately still missing delimiters:
,,,,,,,,,1,'06-11-2020 11:03:53',1,'nl','1932795011','hqV7jjuAtVf8cYj','06-11-2020 11:03:34','06-11-2020 11:03:53''foo''bar''foo''bar'111'foo'

So far, 4.3.15 is the last to work correctly for me.
Selcal

Selcal

2020-11-25 00:51

reporter   ~60754

Actually I think this is to do with the setting of $num_fields in application/helpers/export_helper.php

Line 137:
            $num_fields = safecount($row);
Change to:
            $num_fields = safecount($fields);

Seems, at first glance, to fix the problem.
Counting $row does not actually count all the $fields, and thus once we get to line 260/261, no delimiter is added anymore.

It seems to work here, but I do notice that the newer version exports numerics within quotes. This may be correct; it's rather late now but I'll try to get my SPSS users to check. The number of delimiters, at least, is correct.
Selcal

Selcal

2020-11-25 10:51

reporter   ~60755

I just received user feedback; with the above fix the issue seems fixed.
Obviously I'm not well versed enough in LimeSurvey coding to see if this fix is the correct way to do it, and whether there is no collateral damage. There at least is none that I could see J.
Selcal

Selcal

2020-12-18 12:38

reporter   ~60998

I have applied the above fix to the current 4.3.31 update, and have used it in a live environment. There seem to be no adverse effects, and the SPSS export results are now correct.
The fix seems to make sense to me, looking just at the export_helper.php file. Obviously, my knowledge of the entire package is too limited to guarantee no side effects. But users here have not noticed any.
Selcal

Selcal

2021-02-11 12:59

reporter   ~62157

Update: Now running 4.4.6, this issue is still present and the above fix still works for me without issue.
c_schmitz

c_schmitz

2021-03-07 21:24

administrator   ~62825

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=31282
c_schmitz

c_schmitz

2021-03-07 21:25

administrator   ~62826

Thank you for your continuous feedback and not giving up on us. We have now patched this in LS3 and LS4 according to your suggestion.
c_schmitz

c_schmitz

2021-03-08 09:28

administrator   ~62868

Fix committed to 3.x-LTS branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=31283

Related Changesets

LimeSurvey: master 644abe69

2021-03-07 21:24:24

c_schmitz

Details Diff
Fixed issue 16723: SPSS data export does not include any delimiters after lastpage field Affected Issues
16723
mod - application/helpers/export_helper.php Diff File

LimeSurvey: 3.x-LTS b40ffbd1

2021-03-07 21:24:40

c_schmitz

Details Diff
Fixed issue 16723: SPSS data export does not include any delimiters after lastpage field Affected Issues
16723
mod - application/helpers/export_helper.php Diff File

Issue History

Date Modified Username Field Change
2020-10-07 10:57 Selcal New Issue
2020-10-07 10:57 Selcal File Added: spss_delimiters.jpg
2020-10-07 10:57 Selcal File Added: survey_archive_668368.lsa
2020-10-07 10:57 Selcal File Added: survey_archive_246634.lsa
2020-10-07 10:57 Selcal File Added: survey_V4319_668368_SPSS_data_file(4).dat
2020-10-07 10:57 Selcal File Added: survey_V4318_668368_SPSS_data_file(1).dat
2020-10-07 10:57 Selcal File Added: survey_V433_668368_SPSS_data_file.dat
2020-10-07 18:07 cdorin Assigned To => c_schmitz
2020-10-07 18:07 cdorin Status new => assigned
2020-11-05 11:56 Selcal Note Added: 60557
2020-11-05 17:02 c_schmitz Status assigned => feedback
2020-11-05 17:02 c_schmitz Note Added: 60568
2020-11-06 10:51 Selcal Note Added: 60579
2020-11-06 10:51 Selcal Status feedback => assigned
2020-11-06 11:09 Selcal Note Added: 60580
2020-11-06 11:09 Selcal File Added: survey_841825_SPSS_data_file.dat
2020-11-06 11:09 Selcal File Added: survey_archive_841825.lsa
2020-11-06 11:09 Selcal File Added: survey_814171_SPSS_data_file.dat
2020-11-06 11:09 Selcal File Added: survey_archive_814171.lsa
2020-11-25 00:01 Selcal Note Added: 60753
2020-11-25 00:51 Selcal Note Added: 60754
2020-11-25 10:51 Selcal Note Added: 60755
2020-12-18 12:38 Selcal Note Added: 60998
2021-02-11 12:59 Selcal Note Added: 62157
2021-03-07 21:24 c_schmitz Changeset attached => LimeSurvey master 644abe69
2021-03-07 21:24 c_schmitz Note Added: 62825
2021-03-07 21:24 c_schmitz Resolution open => fixed
2021-03-07 21:25 c_schmitz Status assigned => resolved
2021-03-07 21:25 c_schmitz Note Added: 62826
2021-03-08 09:28 c_schmitz Changeset attached => LimeSurvey 3.x-LTS b40ffbd1
2021-03-08 09:28 c_schmitz Note Added: 62868
2021-03-08 19:31 c_schmitz Fixed in Version => 4.4.12
2021-03-08 19:38 c_schmitz Status resolved => closed