View Issue Details

This issue affects 1 person(s).
 6
IDProjectCategoryView StatusLast Update
04441Bug reportspublic2010-08-04 23:18
Reportermdekker Assigned Tomdekker  
PrioritynormalSeverityminor 
Status closedResolutionfixed 
Summary04441: Make token import accept CSV from Excel (Dutch language)
Description

CSV export from MS Excel uses the default fieldseparator set in the systemwide language settings. For Dutch this results in CSV export to have ; as separator. As the token import doesn't allow us to switch the separator, this leaves a lot of people helpless.

Additional Information

By looking @ the code I think it would be relatively easy to implement a switch for the field separator to use in import.

TagsNo tags attached.
Attached Files
import_tokens.jpg (45,015 bytes)   
import_tokens.jpg (45,015 bytes)   
import_tokens.diff (5,456 bytes)   
### Eclipse Workspace Patch 1.0
#P limesurvey
Index: admin/tokens.php
===================================================================
--- admin/tokens.php	(revision 8931)
+++ admin/tokens.php	(working copy)
@@ -2373,6 +2373,13 @@
         {
             $tokenoutput .= "<div class='warningheader'>".$clang->gT("Failed to open the uploaded file!")."</div><br />\n";
         }
+        if (!isset($_POST['filterduplicatefields']) || (isset($_POST['filterduplicatefields']) && count($_POST['filterduplicatefields'])==0))
+        {
+            $filterduplicatefields=array('firstname','lastname','email');
+        } else {
+            $filterduplicatefields=$_POST['filterduplicatefields'];
+        }
+        $separator = returnglobal('separator');
         foreach ($tokenlistarray as $buffer)
         {
             $buffer=@mb_convert_encoding($buffer,"UTF-8",$uploadcharset);
@@ -2383,7 +2390,20 @@
                 $buffer=removeBOM($buffer);
                 $allowedfieldnames=array('firstname','lastname','email','emailstatus','token','language', 'validfrom', 'validuntil');
                 $allowedfieldnames=array_merge($attrfieldnames,$allowedfieldnames);
-                $firstline = convertCSVRowToArray($buffer,',','"');
+                
+                switch ($separator) {
+                    case 'comma':
+                        $separator = ',';
+                        break;
+                    case 'semicolon':
+                        $separator = ';';
+                        break;
+                    default:
+                        $comma = substr_count($buffer,',');
+                        $semicolon = substr_count($buffer,';');
+                        if ($semicolon>$comma) $separator = ';'; else $separator = ',';
+                }
+                $firstline = convertCSVRowToArray($buffer,$separator,'"');
                 $firstline=array_map('trim',$firstline);
                 $ignoredcolumns=array();
                 //now check the first line for invalid fields
@@ -2407,7 +2427,7 @@
             else
             {
 
-                $line = convertCSVRowToArray($buffer,',','"');
+                $line = convertCSVRowToArray($buffer,$separator,'"');
 
                 if (count($firstline)!=count($line))
                 {
@@ -2427,14 +2447,6 @@
 
                 if ($filterduplicatetoken!=false)
                 {
-                    if (!isset($_POST['filterduplicatefields']) || (isset($_POST['filterduplicatefields']) && count($_POST['filterduplicatefields'])==0))
-                    {
-                        $filterduplicatefields=array('firstname','lastname','email');
-                    }
-                    else
-                    {
-                        $filterduplicatefields=$_POST['filterduplicatefields'];
-                    }
                     $dupquery = "SELECT tid from ".db_table_name("tokens_$surveyid")." where 1=1";
                     foreach($filterduplicatefields as $field)
                     {
@@ -2802,9 +2814,19 @@
         if ($charset=='auto') {$charsetsout.=" selected ='selected'";}
         $charsetsout.=">$title ($charset)</option>";
     }
+    $separator = returnglobal('separator');
+    if (empty($separator) || $separator == 'auto') $selected = " selected = 'selected'"; else $selected = ''; 
+    $separatorout = "<option value='auto'$selected>".$clang->gT("Auto detect")."</option>";
+    if ($separator == 'comma') $selected = " selected = 'selected'"; else $selected = ''; 
+    $separatorout .= "<option value='comma'$selected>".$clang->gT("Comma")."</option>";
+    if ($separator == 'semicolon') $selected = " selected = 'selected'"; else $selected = ''; 
+    $separatorout .= "<option value='semicolon'$selected>".$clang->gT("Semicolon")."</option>";
     $tokenoutput .= "<form id='tokenimport' enctype='multipart/form-data' action='$scriptname?action=tokens' method='post'><ul>\n"
     . "<li><label for='the_file'>".$clang->gT("Choose the CSV file to upload:")."</label><input type='file' id='the_file' name='the_file' size='35' /></li>\n"
     . "<li><label for='csvcharset'>".$clang->gT("Character set of the file:")."</label><select id='csvcharset' name='csvcharset' size='1'>$charsetsout</select></li>\n"
+    . "<li><label for='separator'>".$clang->gT("Separator used:")."</label><select id='separator' name='separator' size='1'>"
+    . $separatorout
+    . "</select></li>\n"
     . "<li><label for='filterblankemail'>".$clang->gT("Filter blank email addresses:")."</label><input type='checkbox' id='filterblankemail' name='filterblankemail' checked='checked' /></li>\n"
     . "<li><label for='filterduplicatetoken'>".$clang->gT("Filter duplicate records:")."</label><input type='checkbox' id='filterduplicatetoken' name='filterduplicatetoken' checked='checked' /></li>"
     . "<li id='lifilterduplicatefields'><label for='filterduplicatefields[]'>".$clang->gT("Duplicates are determined by:")."</label>"
Index: common.php
===================================================================
--- common.php	(revision 8931)
+++ common.php	(working copy)
@@ -5347,7 +5347,7 @@
 
 function convertCSVRowToArray($string, $seperator, $quotechar)
 {
-    $fields=preg_split('/,(?=([^"]*"[^"]*")*(?![^"]*"))/',trim($string));
+    $fields=preg_split('/' . $seperator . '(?=([^"]*"[^"]*")*(?![^"]*"))/',trim($string));
     $fields=array_map('CSVUnquote',$fields);
     return $fields;
 }
import_tokens.diff (5,456 bytes)   
Bug heat6
Complete LimeSurvey version number (& build)8865
I will donate to the project if issue is resolved
Browser
Database type & versiondna
Server OS (if known)dna
Webserver software & version (if known)dna
PHP Versiondna
Git branch / Pull Request URL

Users monitoring this issue

There are no users monitoring this issue.

Activities

c_schmitz

c_schmitz

2010-07-01 22:05

administrator   ~12330

Menno, please go for it!

Mazi

Mazi

2010-07-13 02:10

updater   ~12394

Maybe we have to edit the documentation about token import afterwards to state that other separators are possible and how it can be done.

mdekker

mdekker

2010-07-13 09:14

reporter   ~12411

I was thinking to add a select box defaulting to 'automatic' that just counts the number of , or ; in the first line. The highest number wins.
And then add options to choose a specific format.

I'll keep in mind to update the documentation, thanks for the reminder Mazi!

mdekker

mdekker

2010-07-13 11:06

reporter   ~12412

I added a new option and default it to auto detect. It works fine for my default csv export from excel. The change is minor but involves some new translations. If the docs need to be updated it should probably be done with the release or when it is planned (version wise, so we can add: available from 1.91+ or something similar).

I did the patch against 1.90rc2, as I didn't do a checkout out for the dev yet. I'll commit to dev later.

mdekker

mdekker

2010-07-14 10:23

reporter   ~12424

Committed to dev branch in 8934

Issue History

Date Modified Username Field Change
2010-06-23 11:31 mdekker New Issue
2010-06-23 11:31 mdekker Status new => assigned
2010-06-23 11:31 mdekker Assigned To => user372
2010-07-01 22:05 c_schmitz Assigned To user372 => mdekker
2010-07-01 22:05 c_schmitz Note Added: 12330
2010-07-01 22:05 c_schmitz Project Bug reports => @10@
2010-07-13 02:10 Mazi Note Added: 12394
2010-07-13 09:14 mdekker Note Added: 12411
2010-07-13 11:01 mdekker File Added: import_tokens.jpg
2010-07-13 11:01 mdekker File Added: import_tokens.diff
2010-07-13 11:06 mdekker Note Added: 12412
2010-07-13 11:06 mdekker Assigned To mdekker => c_schmitz
2010-07-13 11:06 mdekker Status assigned => feedback
2010-07-14 10:23 mdekker Note Added: 12424
2010-07-14 10:23 mdekker Status feedback => assigned
2010-07-14 10:24 mdekker Product Version 1.90RC2 =>
2010-07-14 13:09 mdekker Status assigned => resolved
2010-07-14 13:09 mdekker Resolution open => fixed
2010-07-14 13:09 mdekker Assigned To c_schmitz => mdekker
2010-08-04 23:18 c_schmitz Status resolved => closed
2026-11-20 10:33 c_schmitz Category Tokens => (No Category)