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

The views_data_export.module 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