View Issue Details

IDProjectCategoryView StatusLast Update
01024User patchesImport / Exportpublic2012-06-21 14:17
Reporteradamzammit Assigned To 
PrioritynormalSeveritytrivial 
Status confirmedResolutionopen 
Product Version 
Target VersionFixed in Version 
Summary01024: export to fixed width file feature
Description

This file will export the data from a survey to a fixed width ASCII file.

This is useful when using the export to queXML feature. queXML files can be converted to DDI files which document the data from a questionnaire.

see http://quexml.sourceforge.net for more information.

TagsNo tags attached.
Complete LimeSurvey version number (& build)

Activities

c_schmitz

c_schmitz

2010-03-14 00:13

administrator   ~11337

Hi Adam,

does it make sense to port this file to a more actual version of LimeSurvey and integrate it into the core or does the queXML suite already have some tool by now to do that?

adamzammit

adamzammit

2010-03-16 04:34

developer  

fixedwidth.tar.gz (3,977 bytes)
adamzammit

adamzammit

2010-03-16 04:34

developer   ~11385

Dear Carsten,

It does make sense to include this when using queXML and DDI version 2.0.

I have attached a file containing:

  1. an updated export_data_fixedwidth.php
  2. patches for common.php and admin.php to add a link to the menu.

Regards,
Adam Zammit

c_schmitz

c_schmitz

2010-04-26 18:08

administrator   ~11660

Hello Adam,

sorry, but it took me some time to take a look at it.
I'd rather not have a separate menu icon for this but include it in the general export responses screen. Also the patch should be based on the latest dev version in https://limesurvey.svn.sourceforge.net/svnroot/limesurvey/source/limesurvey

If you could do that, that would be amazing and I will be happy to include it for next 1.90 release.

c_schmitz

c_schmitz

2010-05-01 14:17

administrator   ~11679

adamzammit?

adamzammit

adamzammit

2010-05-03 05:48

developer   ~11688

I will do that. I shall post a patch against the repository shortly

adamzammit

adamzammit

2010-05-03 08:07

developer  

fixedwidthpatch_8669.patch (15,076 bytes)
Index: common.php
===================================================================
--- common.php	(revision 8669)
+++ common.php	(working copy)
@@ -2332,7 +2332,7 @@
         return $globalfieldmap[$surveyid][$style][$clang->langcode];
     }
 
-    $fieldmap["submitdate"]=array("fieldname"=>"submitdate", 'type'=>"submitdate", 'sid'=>$surveyid, "gid"=>"", "qid"=>"", "aid"=>"");
+    $fieldmap["submitdate"]=array("fieldname"=>"submitdate", 'type'=>"submitdate", 'sid'=>$surveyid, "gid"=>"", "qid"=>"", "aid"=>"", "width"=>"19");
 	if ($style == "full")
     {
         $fieldmap["submitdate"]['title']="";
@@ -2340,7 +2340,10 @@
         $fieldmap["submitdate"]['group_name']="";
     }
 
-    $fieldmap["id"]=array("fieldname"=>"id", 'sid'=>$surveyid, 'type'=>"id", "gid"=>"", "qid"=>"", "aid"=>"");
+    $query = "SELECT MAX(LENGTH(id)) FROM ".db_table_name("survey_$surveyid");
+    $width=$connect->getOne($query);
+
+    $fieldmap["id"]=array("fieldname"=>"id", 'sid'=>$surveyid, 'type'=>"id", "gid"=>"", "qid"=>"", "aid"=>"", "width"=>$width);
     if ($style == "full")
     {
         $fieldmap["id"]['title']="";
@@ -2348,7 +2351,10 @@
         $fieldmap["id"]['group_name']="";
     }
 
-    $fieldmap["lastpage"]=array("fieldname"=>"lastpage", 'sid'=>$surveyid, 'type'=>"lastpage", "gid"=>"", "qid"=>"", "aid"=>"");
+    $query = "SELECT MAX(LENGTH(lastpage)) FROM ".db_table_name("survey_$surveyid");
+    $width=$connect->getOne($query);
+
+    $fieldmap["lastpage"]=array("fieldname"=>"lastpage", 'sid'=>$surveyid, 'type'=>"lastpage", "gid"=>"", "qid"=>"", "aid"=>"", "width"=>$width);
     if ($style == "full")
     {
         $fieldmap["lastpage"]['title']="";
@@ -2356,7 +2362,10 @@
         $fieldmap["lastpage"]['group_name']="";
     }
 
-    $fieldmap["startlanguage"]=array("fieldname"=>"startlanguage", 'sid'=>$surveyid, 'type'=>"startlanguage", "gid"=>"", "qid"=>"", "aid"=>"");
+    $query = "SELECT MAX(LENGTH(startlanguage)) FROM ".db_table_name("survey_$surveyid");
+    $width=$connect->getOne($query);
+
+   $fieldmap["startlanguage"]=array("fieldname"=>"startlanguage", 'sid'=>$surveyid, 'type'=>"startlanguage", "gid"=>"", "qid"=>"", "aid"=>"", "width"=>$width);
     if ($style == "full")
     {
         $fieldmap["startlanguage"]['title']="";
@@ -2372,7 +2381,11 @@
     {
         if ($prow['private'] == "N")
         {
-            $fieldmap["token"]=array("fieldname"=>"token", 'sid'=>$surveyid, 'type'=>"token", "gid"=>"", "qid"=>"", "aid"=>"");
+ 	    $query = "SELECT MAX(LENGTH(token)) FROM ".db_table_name("survey_$surveyid");
+	    $width=$connect->getOne($query);
+	    if (empty($width)) $width = 0;
+
+            $fieldmap["token"]=array("fieldname"=>"token", 'sid'=>$surveyid, 'type'=>"token", "gid"=>"", "qid"=>"", "aid"=>"", "width"=>$width);
             if ($style == "full")
             {
                 $fieldmap["token"]['title']="";
@@ -2387,7 +2400,8 @@
                                     'sid'=>$surveyid,
                                     "gid"=>"",
                                     "qid"=>"", 
-                                    "aid"=>"");
+                                    "aid"=>"",
+				    "width"=>"19");
             if ($style == "full")
             {
                 $fieldmap["datestamp"]['title']="";
@@ -2399,7 +2413,8 @@
                                      'sid'=>$surveyid, 
                                      "gid"=>"", 
                                      "qid"=>"", 
-                                     "aid"=>"");
+                                     "aid"=>"",
+				     "width"=>"19");
             if ($style == "full")
             {
                 $fieldmap["startdate"]['title']="";
@@ -2415,7 +2430,8 @@
                                     'sid'=>$surveyid,  
                                     "gid"=>"", 
                                     "qid"=>"", 
-                                    "aid"=>"");
+                                    "aid"=>"",
+				    "width"=>"15");
             if ($style == "full")
             {
                 $fieldmap["ipaddr"]['title']="";
@@ -2426,7 +2442,10 @@
         // Add 'refurl' to fieldmap.
         if ($prow['refurl'] == "Y")
         {
-            $fieldmap["refurl"]=array("fieldname"=>"refurl", 'type'=>"url", 'sid'=>$surveyid, "gid"=>"", "qid"=>"", "aid"=>"");
+	    $query = "SELECT MAX(LENGTH(refurl)) FROM ".db_table_name("survey_$surveyid");
+	    $width=$connect->getOne($query);
+
+            $fieldmap["refurl"]=array("fieldname"=>"refurl", 'type'=>"url", 'sid'=>$surveyid, "gid"=>"", "qid"=>"", "aid"=>"", "width"=>$width);
             if ($style == "full")
             {
                 $fieldmap["refurl"]['title']="";
@@ -2488,6 +2507,9 @@
         {
             $fieldname="{$arow['sid']}X{$arow['gid']}X{$arow['qid']}";
             $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>"{$arow['type']}", 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>"");
+	    $query = "SELECT value FROM ".db_table_name("question_attributes") . " WHERE attribute = 'maximum_chars' AND qid = '{$arow['qid']}'";
+	    $width=$connect->getOne($query);
+
             if ($style == "full")
             {
                 $fieldmap[$fieldname]['title']=$arow['title'];
@@ -2501,6 +2523,15 @@
             }
             switch($arow['type'])
             {
+		case "S":
+		    if (empty($width)) $width = 240;
+		    break;
+ 		case "T":
+		    if (empty($width)) $width = 1024;
+		    break;
+ 		case "U":
+		    if (empty($width)) $width = 2048;
+		    break;
                 case "L":  //RADIO LIST
                 case "!":  //DROPDOWN LIST
                     if ($arow['other'] == "Y")
@@ -2547,6 +2578,8 @@
                     }
                     break;
             }
+	    if (empty($width)) $width = 1;
+	    $fieldmap[$fieldname]['width'] = $width;
         }
         // For Multi flexi question types
         elseif ($qtypes[$arow['type']]['subquestions']==2 && $qtypes[$arow['type']]['answerscales']==0)
@@ -2587,7 +2620,8 @@
                                     "gid"=>$arow['gid'], 
                                     "qid"=>$arow['qid'], 
                                     "aid"=>$abrow['title']."_".$answer['title'],
-                                    "sqid"=>$abrow['qid']);
+                                    "sqid"=>$abrow['qid'],
+				    "width"=>"MF1");
                     if ($abrow['other']=="Y") {$alsoother="Y";}
                     if ($style == "full")
                     {
@@ -2629,7 +2663,7 @@
                 if ($abmultiscaleresultrow=$abmultiscaleresult->FetchRow())
                 {
                     $fieldname="{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}#0";
-                    $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>$abrow['title'], "scale_id"=>0);
+                    $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>$abrow['title'], "scale_id"=>0, "width" => "MF2");
                     if ($style == "full")
                     {
                         $fieldmap[$fieldname]['title']=$arow['title'];
@@ -2656,7 +2690,7 @@
                 if ($abmultiscaleresultrow=$abmultiscaleresult->FetchRow())
                 {
                     $fieldname="{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}#1";
-                    $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>$abrow['title'], "scale_id"=>1);
+                    $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>$abrow['title'], "scale_id"=>1, "width" => "MF3");
                     if ($style == "full")
                     {
                         $fieldmap[$fieldname]['title']=$arow['title'];
@@ -2688,7 +2722,7 @@
             for ($i=1; $i<=$slots; $i++)
             {
                 $fieldname="{$arow['sid']}X{$arow['gid']}X{$arow['qid']}$i";
-                $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>$i);
+                $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>$i, "width" => "MF4");
                 if ($style == "full")
                 {
                     $fieldmap[$fieldname]['title']=$arow['title'];
@@ -2714,8 +2748,11 @@
             $abresult=db_execute_assoc($abquery) or safe_die ("Couldn't get list of answers in createFieldMap function (case M/A/B/C/E/F/H/P)<br />$abquery<br />".$connect->ErrorMsg());  //Checked
             while ($abrow=$abresult->FetchRow())
             {
+	 	$query = "SELECT MAX(LENGTH(code)) FROM ". db_table_name('answers') . " WHERE qid = '{$arow['qid']}'";
+		$width=$connect->getOne($query);
+
                 $fieldname="{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}";
-                $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>$abrow['title']);
+                $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>$abrow['title'], "width"=>$width);
                 if ($abrow['other']=="Y") {$alsoother="Y";}
                 if ($style == "full")
                 {
@@ -2731,7 +2768,7 @@
                 if ($arow['type'] == "P")
                 {
                     $fieldname="{$arow['sid']}X{$arow['gid']}X{$arow['qid']}{$abrow['title']}comment";
-                    $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>"comment");
+                    $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>"comment", "width"=>"MF6");
                     if ($style == "full")
                     {
                         $fieldmap[$fieldname]['title']=$arow['title'];
@@ -2747,7 +2784,7 @@
             if ((isset($alsoother) && $alsoother=="Y") && ($arow['type']=="M" || $arow['type']=="P"))
             {
                 $fieldname="{$arow['sid']}X{$arow['gid']}X{$arow['qid']}other";
-                $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>"other");
+                $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>"other", "width"=>"MF7");
                 if ($style == "full")
                 {
                     $fieldmap[$fieldname]['title']=$arow['title'];
@@ -2761,7 +2798,7 @@
                 if ($arow['type']=="P")
                 {
                     $fieldname="{$arow['sid']}X{$arow['gid']}X{$arow['qid']}othercomment";
-                    $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>"othercomment");
+                    $fieldmap[$fieldname]=array("fieldname"=>$fieldname, 'type'=>$arow['type'], 'sid'=>$surveyid, "gid"=>$arow['gid'], "qid"=>$arow['qid'], "aid"=>"othercomment","width"=>"MF8");
                     if ($style == "full")
                     {
                         $fieldmap[$fieldname]['title']=$arow['title'];
@@ -7616,4 +7653,4 @@
 }
 
 
-// Closing PHP tag intentionally left out - yes, it is okay       
\ No newline at end of file
+// Closing PHP tag intentionally left out - yes, it is okay       
Index: admin/exportresults.php
===================================================================
--- admin/exportresults.php	(revision 8669)
+++ admin/exportresults.php	(working copy)
@@ -160,6 +160,14 @@
     $exportoutput.=" onclick='document.getElementById(\"ansabbrev\").disabled=false;' />"
     ."<label for='csvdoc'>"
     .$clang->gT("CSV File (All charsets)")."</label></li>\n";
+    $exportoutput .="\t<li><input type='radio' class='radiobtn' name='type' value='fixedwidth' id='fixedwidthdoc'";
+    if (!function_exists('iconv'))
+    {
+        $exportoutput.=' checked="checked" ';
+    }
+    $exportoutput.=" onclick='document.getElementById(\"ansabbrev\").disabled=false;' />"
+    ."<label for='fixedwidthdoc'>"
+    .$clang->gT("Fixed Width Text File (all charsets)")."</label></li>\n";
     if(isset($usepdfexport) && $usepdfexport == 1)
     {
         $exportoutput .= "\t<li><input type='radio' class='radiobtn' name='type' value='pdf' id='pdfdoc' onclick='document.getElementById(\"ansabbrev\").disabled=false;' />"
@@ -305,6 +313,11 @@
         $sheet->setInputEncoding('utf-8');
         $separator="~|";
         break;
+    case "fixedwidth":
+        header("Content-Disposition: attachment; filename=results-survey".$surveyid.".txt");
+        header("Content-type: text; charset=UTF-8");
+	$separator="";
+        break;
     case "csv":
         header("Content-Disposition: attachment; filename=results-survey".$surveyid.".csv");
         header("Content-type: text/comma-separated-values; charset=UTF-8");
@@ -357,6 +370,7 @@
     $outmap[$fieldentry['fieldname']]['gid']= $fieldentry['gid'];
     $outmap[$fieldentry['fieldname']]['qid']= $fieldentry['qid'];
     $outmap[$fieldentry['fieldname']]['aid']= $fieldentry['aid'];
+    $outmap[$fieldentry['fieldname']]['width']= $fieldentry['width'];
     if ($fieldentry['qid']!='')
     {
         $qq = "SELECT other FROM {$dbprefix}questions WHERE qid={$fieldentry['qid']} and language='$surveybaselang'";
@@ -719,6 +733,8 @@
     //print_r($fieldmap);
 }
 else
+if ($type =="fixedwidth") { } //No header for fixed width file
+else
 {
     $exportoutput .= $firstline; //Sending the header row
 }
@@ -866,6 +882,19 @@
             }
             $pdf->intopdf($pdfstring);
         }
+	else if ($type == "fixedwidth")
+	{
+	    foreach($drow as $key=>$dr) {
+                $fielddata=arraySearchByKey($key, $fieldmap, "fieldname", 1);
+
+		if (isset($fielddata['width']))
+			$width = $fielddata['width'];
+		else
+			$width = 1;
+
+		$exportoutput .= str_pad(substr($dr,0,$width), $width, " ", STR_PAD_LEFT);
+            }
+	}
         else
         {
             $exportoutput .= implode($separator, str_replace("\r\n", " ", $drow)) . "\n"; //create dump from each row
@@ -1196,6 +1225,7 @@
                         if($type == "pdf"){$pdf->intopdf($labelscache[$flid.'|'.$explang.'|'.$drow[$i]]);}
                     }
                     break;
+
                 case "1": //dual scale
                     $flid=$fielddata['lid'];
                     $flid1=$fielddata['lid1'];
adamzammit

adamzammit

2010-05-03 08:11

developer   ~11689

Dear Carsten,

As you have requested, I have created a patch for the latest SVN version (8669) - that integrates with the general export responses screen.

It only makes sense to export to fixed width using "Answer Codes" not "Full Answers" so it has only been implemented for "Answer Codes" (i.e when "short" selected)

This patch requires changes to common.php createFieldMap function (adds a new field called "width") There are a few cases which I was unable to test due to a lack of my questionnaire data in the latest dev version of limesurvey - in these cases I have set the "width" to MF1,MF2,MF3... to aid in locating the correct part of the createFieldMap function when exporting to fixed width.

Regards,
Adam Zammit

c_schmitz

c_schmitz

2010-07-01 16:34

administrator   ~12324

Hello Adam,.

thank you - this looks much better!

I did an in-depth look at the patch.

You 'misused' Createfielmap to implement a width function. The problem is that it will fail miserable for any survey that is not active and has no result table.
Generally I don't think the max field length shouldn't be determined in that function in general since it is already a very expensive function and the width data is only used for your very special export feature.
So the best would be to determine field lenght right before you export in a separate function. Also beware that someone who uses fixed file width wouldn't want the field with to change all the time. You will therefore have to define fixed width for every question types - you could add that as a property to the getqtypelist() array.

If you have more questions don't hesitate to pick my brain.

c_schmitz

c_schmitz

2010-10-11 17:50

administrator   ~13076

Adam, do you still intend to work this out or would you rather forget about this? :-)

adamzammit

adamzammit

2010-10-12 00:19

developer   ~13104

Hi Carsten,

I think I will leave this. If someone else would like this feature, they could re-open the bug and that might motivate me to get it finished!

Adam

user11151

2010-12-13 15:12

  ~13771

Last edited: 2010-12-13 15:12

View 2 revisions

Hallo Adam,
is it possible to use the patch (to export fixed ascii) with the actual version of limesurvey?
I look into the two patch-files (admin.php.diff and common.php.diff), but I'm not really sure that it is right to replace 273-6times and 1792-6times with the content in the files... (@@ -273,6 +273,11 @@) and (@@ -1792,6 +1792,14 @@ )
Or do i make a mistake by understanding the patch-idea?
Tommi

adamzammit

adamzammit

2010-12-15 03:00

developer   ~13785

Hello Tommi,

I don't think either of these patches will work on the current version of Limesurvey without some tweaking. With some encouragement I might release a proper export to fixed width feature!

Regards,
Adam Zammit

Issue History

Date Modified Username Field Change
2007-06-27 09:38 adamzammit New Issue
2007-06-27 09:38 adamzammit File Added: fixedwidth.php
2007-07-15 03:26 c_schmitz Status new => acknowledged
2010-01-14 21:57 c_schmitz Status acknowledged => confirmed
2010-03-13 16:13 c_schmitz Status confirmed => acknowledged
2010-03-14 00:13 c_schmitz Note Added: 11337
2010-03-14 00:13 c_schmitz Assigned To => c_schmitz
2010-03-14 00:13 c_schmitz Status acknowledged => feedback
2010-03-16 04:34 adamzammit File Added: fixedwidth.tar.gz
2010-03-16 04:34 adamzammit Note Added: 11385
2010-03-16 04:34 adamzammit Status feedback => assigned
2010-04-26 18:08 c_schmitz Note Added: 11660
2010-04-26 18:09 c_schmitz File Deleted: fixedwidth.php
2010-04-26 18:09 c_schmitz Status assigned => feedback
2010-05-01 14:17 c_schmitz Severity feature => trivial
2010-05-01 14:17 c_schmitz Description Updated View Revisions
2010-05-01 14:17 c_schmitz Note Added: 11679
2010-05-03 05:48 adamzammit Note Added: 11688
2010-05-03 05:48 adamzammit Status feedback => assigned
2010-05-03 08:07 adamzammit File Added: fixedwidthpatch_8669.patch
2010-05-03 08:11 adamzammit Note Added: 11689
2010-07-01 16:34 c_schmitz Note Added: 12324
2010-07-01 16:34 c_schmitz Assigned To c_schmitz => adamzammit
2010-07-01 16:34 c_schmitz Status assigned => feedback
2010-10-11 17:50 c_schmitz Note Added: 13076
2010-10-12 00:19 adamzammit Note Added: 13104
2010-10-12 00:19 adamzammit Status feedback => assigned
2010-11-01 17:38 c_schmitz Assigned To adamzammit =>
2010-11-01 17:38 c_schmitz Assigned To => c_schmitz
2010-11-01 17:38 c_schmitz Status assigned => confirmed
2010-12-13 15:12 user11151 Note Added: 13771
2010-12-13 15:12 user11151 Note Edited: 13771 View Revisions
2010-12-15 03:00 adamzammit Note Added: 13785
2012-06-21 14:17 c_schmitz Assigned To c_schmitz =>