Drupal (6) Views Data Export multi value fields separation (Table / XLS)

The ‎Views data export‎ does not output multivalue fields cleanly in .xls and .csv files. Mostly these values are separated into several lines or appended without any separator.

A final fix in the module is not available (at least for Drupal 6) in the current state. So here's a simple workaroud providing a lot of flexibility:

Simply export as .xls (Excel) - you can easily export as .csv via Excel later on. Group multiple values using the checkbox in the field (for example taxonomy value).
Rename the exported .xls file to .html - it contains simple HTML.
Open the file in your Browser (in my case Firefox) and add jQuery (I'm using the FireQuery Extensions "jQueryfi" functionality as one-click solution).
Simply execute the following jQuery script in the browsers console. If you'd like to use a different separator simply change the variable value.
Copy the resulting table into excel. Now you can save it as .csv or whatever you'd like to :)

  1. var valSeparator = '|';
  2. jQuery('tr').each(function(){
  3.   var $row = $(this);
  4.   jQuery('td').each(function(){
  5.     var $rowcol = $(this);
  6.     var $content = $(this).find('div.field-item');
  7.     if($(this).find('div.field-item').length > 0){
  8.       $content.each(function(){
  9.         var fieldtext = $(this).text();
  10.         $rowcol.append(fieldtext + valSeparator);
  11.         $(this).remove();
  12.       });
  13.       // Remove ending separatorts
  14.       if($rowcol.text().substr(-1,1)==valSeparator){
  15.         $rowcol.text($rowcol.text().substr(0,$rowcol.text().length - 1));
  16.       }
  17.     }
  18.   });
  19. });

Happy coding!

PS: If you want to remove all HTML elements, this snippet may help:

  1. var valSeparator = '|';
  2. jQuery('tr').each(function(){
  3.   var $row = $(this);
  4.   jQuery('td').each(function(){
  5.     var $rowcol = $(this);
  6.     var $content = $(this).find('div.field-item');
  7.     if($content.find('div.field-item').length > 0){
  8.       $content.each(function(){
  9.         var fieldtext = $(this).text();
  10.         $rowcol.append(fieldtext + valSeparator);
  11.         $(this).remove();
  12.       });
  13.       // Remove ending separatorts
  14.       if($rowcol.text().substr(-1,1)==valSeparator){
  15.         $rowcol.text($rowcol.text().substr(0,$rowcol.text().length - 1));
  16.       }
  17.     }
  18.    
  19.     var $paragraph = $(this).find('*');
  20.     if($paragraph.find('*').length > 0){
  21.       $paragraph.each(function(){
  22.         var fieldtext = $(this).text();
  23.         $rowcol.append(fieldtext + valSeparator);
  24.         $(this).remove();
  25.       });
  26.       // Remove ending separatorts
  27.       if($rowcol.text().substr(-1,1)==valSeparator){
  28.         $rowcol.text($rowcol.text().substr(0,$rowcol.text().length - 1));
  29.       }
  30.     }
  31.    
  32.     $(this).text($(this).text().replace(/(\r\n|\n|\r)/gm,"").trim());
  33.   });
  34. });

Kommentare

Kommentar hinzufügen

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt. Wenn Sie ein zulässiges Avatar mit Ihrer E-Mail-Adresse verknüpft haben, wird dieses als Avatar verwendet.

Weitere Informationen über Formatierungsoptionen

Aktualisieren Geben Sie die Zeichen ein, die Sie im Bild sehen. Geben Sie die im Bild dargestellten Zeichen ein; wenn Sie diese nicht lesen können, senden Sie das Formular ohne Eingabe ab, um ein neues Bild zu generieren. Groß-/Kleinschreibung wird nicht beachtet.  Switch to audio verification.