Hard Return in Textarea Causes Havoc in Table Results Download

Hi,

I am tackling the same problem... Did you manage to resolve this issue?

Anyone?

Thanks.

Same here. To clarify:

I am trying to import a CSV/Tab Delimited/Excel file exported from Webform into Excel 2003.

Excel 2003 imports just fine, except for newlines. This is apparently an Excel bug, causing it to be incapable of importing newlines, and treating them as new rows instead.

I've tried all combinations of delimiters, and various tricks on the Excel side to no avail.

Does anyone have a work around for this?

Changing this to a category of "bug report" because this is what it actually is.

My users (and I) were astonished where free textarea text including new lines actually exports an Excel sheet with multiple line!

Until fixed, is there any hack?

#Shai: Your suggestion is not helpful for me because there are many fields of the said form and they are not displayed all in the table (not enough room).

Thank you all.

P.S. OpenOffice does not have this bug.

I can't reproduce this problem in newer versions of Excel (Mac versions, both 2008 and 2004). Apparently adding something like '\n' just breaks the download for new versions of Excel, even though it might fix things for older versions. I think generally this is a "won't fix", since it deals with Excel not importing the data correctly, rather than the export not being formatted properly.

We're still seeing this issue in CSV exports of textarea fields containing markup, in Open (Libre) Office as well as Excel. As we have little control over how spreadsheet process markup, I think it's a good idea to provide the option to run text through a configurable filter format.

The patch attached below does just that:
* advanced settings option to set default filter format (admin/settings/webform)
* option to select a filter format in export download form for each webform (node/NID/webform-results/download)
* if set, only text fields are run through check_markup against the selected filter format (in webform.export.inc webform_exporter_delimited->add_row)

I can then remove line breaks effectively, if I use this with a filter format that includes, in order:
# line break converter (convert line breaks to markup)
# html filter (strips all markup)

Thanks, David

Hi David, this would have been better to open a new issue, since your patch is a feature request, not fixing a bug. But since this is open again lets continue here.

First of all, I haven't been able to reproduce this problem in Excel any time recently. We also haven't gotten any reports of it recently. Breakage in Libre Office could potentially be an issue as I haven't tried importing it there. Which versions of Libre Office and Excel are you experiencing the problem in (OS and application versions)?

The solution you propose also doesn't seem natural to me. Text formats are intended to produce HTML output. CSV exports are not HTML, so it feels awkward that you would have such an option. I would think just a simple checkbox for "Remove linebreaks" would both be more natural and more commonly used than piping through the filter system.

Hi quicksketch,

Thanks for getting back to me. Sorry not to post this in the right way, this turned into a feature to resolve a bug.

First of all, I haven't been able to reproduce this problem in Excel any time recently. We also haven't gotten any reports of it recently. Breakage in Libre Office could potentially be an issue as I haven't tried importing it there. Which versions of Libre Office and Excel are you experiencing the problem in (OS and application versions)?

This is in Ubuntu 11.10 and Libre Office 3.4.4. Steps to reproduce:

1. create a simple webform with a couple of textarea fields
2. put in a couple of submissions, paste some sample text with line breaks (see attached) using the plain text editor
3. go to results > download
4. select "delimited", tab delimiter options
5. download
6. open in libre office - submissions data is broken across several rows at line breaks

Our client is also experiencing this in Windows 7 / Exel, though I'm not sure of the Excel version.

The solution you propose also doesn't seem natural to me. Text formats are intended to produce HTML output. CSV exports are not HTML, so it feels awkward that you would have such an option. I would think just a simple checkbox for "Remove linebreaks" would both be more natural and more commonly used than piping through the filter system.

As I see it, Input Formats are intended to filter content that is coming out of the database to some export format. Usually, yes, this is HTML, but Drupal can also output other forms of data, so it seems to make sense to use the same filter system outputting these other forms too.

This also gives the flexibility to apply different filters as spreadsheet requirements / formats change, without having to patch Webform every time. For instance, you can make use of the HTML fitler to strip out unsupported tags, and you could easily add filters to convert HTML entities into UTF, or to remove line breaks, for example. I appreciate this goes beyond the scope of this particular bug, but it seemed the most flexible and natural approach to me.

Thanks, David

The solution you propose is too complex for most users to follow. Especially considering the particular problem you are trying to work around, Drupal can't even remove line breaks out of the box. So in order to solve this problem, users would A) need to install a module that they need for exactly one purpose and B) add a text format that they only need to use in one place (webforms), which means C) they'll want to install Better Formats just to remove this text format from places that don't make sense.

Yes the solution is flexible, but it's too complicated for the typical user. I would prefer to solve this problem through one of these other approaches instead:
#298784: Run CSV downloads through theme functions
#1140026: Excel-native exporter, fixing new line and UTF-8 importing problems

I agree it's a bit convoluted, I take your point.

Passing through the theme layer would help open it up to use preg_replace filters in preprocess functions I suppose, or input filters again. It's not a very direct and simple approach to solve the basic problem, but could be good for special requirements.

I think a simple checkbox to remove the new lines built into the export functions would be a good start. It's best to build in the basic requirements for a clean export directly into webform, I would say. Would you be happy to commit something like that? Did you have any luck reproducing the issue?

Thanks for the fast responses - David.

I think the best solution here is #1140026: Excel-native exporter, fixing new line and UTF-8 importing problems. Since stripping out new lines only serves as a work-around to an inadequate export in the first place. Let's give the user a good export to begin with and they won't have to worry about new lines at all.