Excel and CSV export randomly empty

We are experiencing this exact same issue as described. It seems completely arbitrary why we can sometimes successfully download a file with data, and other times it downloads as zero bytes. When the .xls export is failing, some people on the team are able to successfully download a .tsv file and convert it to an .xls, while others are not, which also seems arbitrary.

Hi,

I'm experiencing a very similar problem with downloading excel files. I can download delimited text files just fine but excel & excel (old versions) files download as zero byte files. I can see that webform is creating the temp files in the tmp directory and if I grab one of those files and add the xlsx extension they open in excel, with the data, perfectly.

If I move this site to my localhost everything works fine and my guess is that this process requires some fairly common php extension that just isn't installed on my server.

Is there something, or things, from the compile command below that I'm missing:

Loaded Modules:
core mod_authn_file mod_authn_default mod_authz_host mod_authz_groupfile mod_authz_user mod_authz_default mod_auth_basic mod_include mod_filter mod_log_config mod_env mod_headers mod_setenvif mod_version mod_ssl prefork http_core mod_mime mod_status mod_autoindex mod_asis mod_cgi mod_negotiation mod_dir mod_actions mod_userdir mod_alias mod_so mod_rewrite mod_php5

Thanks,
Ken Colwell

The developers can't reproduce this, so one of you will have to figure out what's happening. It is probably some server-related issue -- caching, perhaps. Please re-open with more information. There just isn't any information here that we can use to diagnose it.

Absent that, you can post details here and perhaps correlate configurations or situations with others who are experiencing the issue.

Please re-open with more information.

As a workaround, you can use the drush wfx command to export data.

Closing pending reproduction instructions.

I had this same issue and found out that IIS did not have permission to write files in the temporary folder.

Check the permission of the temporary folder and make sure that your site has the ability to write to that folder.
you can find the location of the folder in question at Administration > Configuration > Media > File System

We had the issue, and it turned out that it was a balanced server, so the temporary file was empty on one of the servers and had data on the other.

The workaround we used for now is to set the webform_export_path variable to some path within the sane/consistent file dir, such as public://webform_results/

It seems that the load-balanced server issue is a real thing. On our Pantheon production site we have five balanced endpoints and anything other than a "public://" prefixed export path will fail with empty files.

Wanted to chime in and say that setting "webform_export_path" to a public:// path fixed the issue for me. We're on Acquia enterprise hosting with 4x load-balanced servers. Before setting this, our exports would be blank; configuring this variable allowed us to export our webform data, finally. Great tip!

Hi @torgosPizza,

Where did you change the "webform_export_path" setting?
Could you please elaborate more your solution for those that are not too familiar?

Thanks.

@yalegria:

It's currently a variable you would set in your {variables} table. You can do this easily with drush:

drush vset webform_export_path "public://your_path_here"

The default for that variable (found in the function _webform_export_tempname() is a temporary location, and this is the root of the issue: /tmp directories shared across balanced webheads seem to have a problem with batch exports. Setting this value to something in public:// (or private://) instead of temporary:// appears to be the solution in this configuration.

It'd be great if this was something you could set in settings.php (e.g. $conf['webform_export_path']) but that would be a feature request.

@torgosPizza,

Thanks but this did not fix the issue downloading the results.

I get the following message when the download is done: "Excel cannot open this file. The file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".

The extension being downloaded is .xlsx. I tried to renamed it to .xlx and then open the file and only see "garbage" text.

Anybody?

@yalegria: Yours is a different issue than what's being discussed here.

However I have seen issues like yours, which I was able to fix by clearing all caches and retrying. It mainly would occur if we had updated the webform's fields, and then tried another export.

Also XLX is not an Excel format, but XLS is. If you're running a recent version of Excel the .xslx should open just fine.

I would also verify that your export is complete by exporting it as CSV first. (Excel can open CSV just fine.)

Thank you guys! This fixed our issue as well.

Our website is hosted on Acquia. On DEV and STAGE environments were working fine (no load balanced servers), but not on PRODUCTION.

Acquia does provide a module to a user request always reach the same server (https://www.drupal.org/project/acquia_cloud_sticky_sessions), but even with this module the xls was still corrupted.
Any clue why?

@torgosPizza - with reference to #11

It'd be great if this was something you could set in settings.php

The snippet you give should indeed work as you suggest if you add it to your settings.php file, eg

$conf['webform_export_path'] = 'private://webform_results/';

Adding to the $conf array in settings.php overrides those variables whose name matches the array key (with the value given as the array element's value).

See original issue for reasons why 'webform_export_path' was added to webform.

We had the exact same setup as #12 on Acquia Enterprise. Applying #11 fixed our issue. But it seems like https://www.drupal.org/project/alt_stream_wrappers might be an alternative solution too (Acquia deprecated sticky sessions in favor alt_stream). Haven't tested in production yet.

On pantheon, setting webform_export_path isn't enough. 4.12 is stripping everything from webform_export_path except for the parent directory. This just puts the files floating in the parent directory. In comment #15, the export will actually be created in private:// rather than private://webform_results. I patched 4.12 so that you can actually configure the directory like #15 suggests. This fixed my issue of frequent empty downloads on Pantheon.

My patch in #17 had the full path to my module in it. Here it is fixed.

I missed part of the path when cleaning it up. My apologies.

#7 worked for me.

Thank you, Franz!

If still running with same issue. please decrease the batch size from drush -

drush -l site_url vset webform_export_batch_size 350

#7 and #11 solved my issue. Thanks!

It sees the file permission issue during writing/exporting process. You can find the location of the folder in question at Administration > Configuration > Media > File System. Remove values from a temporary text field and save, then clear cache and try again to export. The problem should be resolved.

Thanks.

I've created a patch in #2918516: Create the webform_export_path directory if it does not exist so it can be written to that helps to create the directory if it doesn't exist, if you have set the webform_export_path, so it would be great if anyone running into this issue could test. Thanks!

The patch in #19 is similar to the patch in #2918516: Create the webform_export_path directory if it does not exist so it can be written to.

Perhaps this issue should have a patch which better handles the case when _webform_export_tempname() returns FALSE.

#11 worked for me.

I modified patch #19 a little:
1) change permissions
2) use Drupal function instead of mkdir()

This issue is already closed. Is there still a problem? If so, please provide steps to reproduce it.

Re-rolled patch agains 4.17

This fix still works and was needed for me using 4.19 on Pantheon.

@Liam Morland: I received a ticket today about empty broken webform result downloads. In re-reviewing my patch from #2918516: Create the webform_export_path directory if it does not exist so it can be written to, there seems to be a flaw due to this line in drupal_tempnam when setting webform_export_path to a subdirectory:

return $scheme . '://' . drupal_basename($filename);

I am setting the webform_export_path variable to private://tmp/webform_results, but the line above causes the path to always be output as private://filename, which causes empty broken file downloads. As a result, I'm reopening this issue to try to keep the conversation in one place. If you'd prefer I open a new issue, let me know and I will do so quickly to try to get this resolved. Thank you.

Here's a patch I believe fixes this once and for all. Instead of using drupal_tempnam, it constructs the path using the webform_export_path variable, and drupal_random_key, then runs it through file_stream_wrapper_uri_normalize to remove any extra slashes if a subdirectory is not being used.

Perhaps this is better seen as a core bug since drupal_tempnam() is not using the $directory parameter for anything but the scheme. There is an issue for this: #985384: FileSystem::tempnam() doesn't respect subdirectories for stream wrappers.

I hesitate to move away from drupal_tempnam() since that is supposed to be the central way to generate temporary filenames.

Even if the files are not going in the expected directory, why does that cause them to be empty?

@Liam Morland: our use case is on Pantheon. Here is what their docs say about modules that use /tmp:

Extensions that require the use of the /tmp directory are not supported. With multiple application containers, as exists on Live environments, it's assumed the /tmp directory will be on the same application container. However, as we run a distributed application container matrix, the /tmp directory is not shared.

The workaround is to set webform_export_path to something that is synched across application containers. We set it this way in settings.php: $conf['webform_export_path'] = 'private://tmp/webform_results';

Without my patch above, the download path generated by _webform_drupal_tempname() gets stripped down to something like private://webform_filename when it should look like private://tmp/webform_results/webform_filename. The file is being generated on one application container but ends up getting served from another application container where it doesn't exist, resulting in an empty file. The problem is intermittent based on what application container you are sent to for the download. You can simulate this behavior on a single application server by modifying $export_info['file_name'] to a non-existent path in webform_results_download().

I just posted a comment to #985384-45: FileSystem::tempnam() doesn't respect subdirectories for stream wrappers because that patch still seems to not return subdirectories. I hope this helps to clarify what is going on here, thanks!

Perhaps this should replicate what drupal_tempnam() does except have it respect the directory paths. This means continuing to use tempnam().

The patch works. I've got same error with an architecture behind load-balancer.
The fact is that drupal_tempnam will remove custom directory if we use a scheme (stream wrapper)

<?php
    if ($filename = tempnam($wrapper
      ->getDirectoryPath(), $prefix)) {
      return $scheme . '://' . drupal_basename($filename);
    }
?>

So we need this path or use absolute path in config...

I can confirm that #11 solved my issue. Thanks!

#34 fixed for me on Pantheon

settings.php: $conf['webform_export_path'] = 'private://tmp/webform_results';

Is anyone having this problem who is not on Pantheon?

Is anyone having this problem and the solution in #38 does not solve it?

Five years ago when I commented about this problem I was not on Pantheon.

@Liam Morland: setting $conf['webform_export_path'] = 'private://tmp/webform_results'; works in combination with my patch from #32 - I've been using it in production since March 2019.

yes, with the patch!

WORKED!!!
[Drupal 7 build on Pantheon]
The customer site affected would download the results of a webform only to open the downloaded spreadsheet to find it corrupted. I added the patch manually and the suggested code found at #15 above to "/sites/default/settings.php" and created two new folders "tmp" and "webform_results" at "sites/default/files/private/tmp/webform_results" to house the results. Thank you @autopoietic SO MUCH for this. This problem seemed to go on for so long only to eventually find such a simple fix.

Drupal 7 is no longer supported. If this applies to a supported version, please re-open.