Moodle
  1. Moodle
  2. MDL-19864

Database: Excel export of URL field produces file with garbled text

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 1.9.5, 2.0.3
    • Fix Version/s: 2.1
    • Labels:
      None
    • Testing Instructions:
      Hide

      Start with MDLQA-964 - if you are adventurous try exporting the Modules and Plugins database as an xls and see what happens. Actually I tried exporting as CSV and ODS and also had problems with both of those. Peace - Anthony

      Show
      Start with MDLQA-964 - if you are adventurous try exporting the Modules and Plugins database as an xls and see what happens. Actually I tried exporting as CSV and ODS and also had problems with both of those. Peace - Anthony
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE
    • Pull Master Branch:
      MDL-19864_database_excel_export
    • Rank:
      2433

      Description

      I'm not sure what Excel needs to have done in order to export the URL. It would be good to have both the URL and the text associated with it if autolink is selected. Currently the export function (defined in /mod/data/field/url/field.class.php) returns the text of the URL concatenated with a space and then the text of the field. If an introductory space is added then things work fine in Excel however it is not an active hyper link in the excel file at that point. I'll ask in the developer forum to see if anyone else has any insights and then work toward a patch. Peace - Anthony

      1. grep for excel in codebase.txt
        17 kB
        Michael de Raadt
      1. MDL-19864-orig.png
        194 kB
      2. MDL-19864-withspace.xls.png
        250 kB

        Issue Links

          Activity

          Hide
          Anthony Borrow added a comment -

          I am attaching a couple of screenshots to show the error (MDL-19864-orig.png) and then to show a temporary workaround that adds an initial space to the line which at least produces readable data as the function was defined to output originally. I wonder if we might be better served by putting the link in one column and the text in another. The down side to that approach is that some users may think that they have more fields. So as I think about this I prefer the idea of keeping it in one field. I also noticed the html entity produced in the text field and will create a separate issue to address that. Peace - Anthony

          Show
          Anthony Borrow added a comment - I am attaching a couple of screenshots to show the error ( MDL-19864 -orig.png) and then to show a temporary workaround that adds an initial space to the line which at least produces readable data as the function was defined to output originally. I wonder if we might be better served by putting the link in one column and the text in another. The down side to that approach is that some users may think that they have more fields. So as I think about this I prefer the idea of keeping it in one field. I also noticed the html entity produced in the text field and will create a separate issue to address that. Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          p.s. - Just an fyi, even though I opened the xls file in OpenOffice, I have seen this behavior purely in Windows and in Microsoft Excel. which is how I originally discovered it as I was trying to demonstrate to a user how easy it is to export data from a database.

          Show
          Anthony Borrow added a comment - p.s. - Just an fyi, even though I opened the xls file in OpenOffice, I have seen this behavior purely in Windows and in Microsoft Excel. which is how I originally discovered it as I was trying to demonstrate to a user how easy it is to export data from a database.
          Hide
          Anthony Borrow added a comment -

          I was looking a little at the Excel writer and noticed there is a special function for writing URLs. I suspect the initial space is causing the field to be treated like a text string rather than a URL so the problem may lie there (or perhaps something dealing with encoding - I recall something with Excel encoding issues with the gradebook at one point). Peace - Anthony

          Show
          Anthony Borrow added a comment - I was looking a little at the Excel writer and noticed there is a special function for writing URLs. I suspect the initial space is causing the field to be treated like a text string rather than a URL so the problem may lie there (or perhaps something dealing with encoding - I recall something with Excel encoding issues with the gradebook at one point). Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          I recently came across a discussion in the forums (http://moodle.org/mod/forum/discuss.php?d=128565) about a problem with exporting users into Excel that also seems to be related to the URL problem. I suspect that this may be related to the excel library problem mentioned above. Peace - Anthony

          Show
          Anthony Borrow added a comment - I recently came across a discussion in the forums ( http://moodle.org/mod/forum/discuss.php?d=128565 ) about a problem with exporting users into Excel that also seems to be related to the URL problem. I suspect that this may be related to the excel library problem mentioned above. Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          reassigning to Robert since he had volunteered to to help out with the database activity module

          Show
          Anthony Borrow added a comment - reassigning to Robert since he had volunteered to to help out with the database activity module
          Hide
          Anthony Borrow added a comment -

          Re-assigning to moodle.com Peace - Anthony

          Show
          Anthony Borrow added a comment - Re-assigning to moodle.com Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          I was trying to do an export of the Modules and Plugins database and had difficulty when including the long description field. I was able to eventually get most of the data for Michael Blake by using ODS; however, anything for excel with a URL in it seems to mess things up. I'm not exactly sure what needs to be done so that they get handled better especially in the textarea field type but I suppose it could be any field. URL field type seemed to behave OK but that was because it has its own export_text_value function defined in /mod/data/field/url/field.class.php. Peace - Anthony

          Show
          Anthony Borrow added a comment - I was trying to do an export of the Modules and Plugins database and had difficulty when including the long description field. I was able to eventually get most of the data for Michael Blake by using ODS; however, anything for excel with a URL in it seems to mess things up. I'm not exactly sure what needs to be done so that they get handled better especially in the textarea field type but I suppose it could be any field. URL field type seemed to behave OK but that was because it has its own export_text_value function defined in /mod/data/field/url/field.class.php. Peace - Anthony
          Hide
          moodle.com added a comment -

          Promoting as this relates to a QA test.

          Show
          moodle.com added a comment - Promoting as this relates to a QA test.
          Hide
          Andrew Davis added a comment - - edited

          I think the problem lies within the Excel writer. Possibly in lib/pear/Spreadsheet/Excel/Writer/Worksheet.php

          We're currently using 0.9.1 of http://pear.php.net/package/Spreadsheet_Excel_Writer/redirected 0.9.2 is currently available. I downloaded it and copied it over our version. It did not help the problem.

          I tried to upload the Excel spreadsheet generated to google docs but it complained "The uploaded spreadsheet format is from an unsupported version." In lib/excel/BIFFwriter.php there is this

          var $_BIFF_version = 0x0500;
          

          According to this page (http://www.foo.be/docs/tpj/issues/vol5_3/tpj0503-0004.html) that means Excel 5.0 which was released in 1994. That probably explains why Google docs won't accept it.

          Note that the problem seems to occur when exporting in Excel format if the data contains a URL anywhere. If you put a URL as an entry's caption the problem occurs there to.

          I dont have a quick solution to this. For the moment all I can recommend is to use the Open Office version even if you're actually using Excel.

          Show
          Andrew Davis added a comment - - edited I think the problem lies within the Excel writer. Possibly in lib/pear/Spreadsheet/Excel/Writer/Worksheet.php We're currently using 0.9.1 of http://pear.php.net/package/Spreadsheet_Excel_Writer/redirected 0.9.2 is currently available. I downloaded it and copied it over our version. It did not help the problem. I tried to upload the Excel spreadsheet generated to google docs but it complained "The uploaded spreadsheet format is from an unsupported version." In lib/excel/BIFFwriter.php there is this var $_BIFF_version = 0x0500; According to this page ( http://www.foo.be/docs/tpj/issues/vol5_3/tpj0503-0004.html ) that means Excel 5.0 which was released in 1994. That probably explains why Google docs won't accept it. Note that the problem seems to occur when exporting in Excel format if the data contains a URL anywhere. If you put a URL as an entry's caption the problem occurs there to. I dont have a quick solution to this. For the moment all I can recommend is to use the Open Office version even if you're actually using Excel.
          Hide
          Andrew Davis added a comment - - edited

          Update: Michael de Raadt tried to load an exported Excel spreadsheet containing URLs into Excel. I use Open Office. Excel wasn't happy opening the file. It complained of corruption in the file and offered to convert it, but it was not successful in doing so.

          Show
          Andrew Davis added a comment - - edited Update: Michael de Raadt tried to load an exported Excel spreadsheet containing URLs into Excel. I use Open Office. Excel wasn't happy opening the file. It complained of corruption in the file and offered to convert it, but it was not successful in doing so.
          Hide
          Andrew Davis added a comment - - edited

          Turning down the priority and changing the fix version as this is no longer holding up a QA issue.

          Show
          Andrew Davis added a comment - - edited Turning down the priority and changing the fix version as this is no longer holding up a QA issue.
          Hide
          Michael de Raadt added a comment -

          The other alternative solution to this problem would be to drop the Excel output option. It is superfluous if we have a working CSV option, which Excel will happily open. The data we are talking about is a static matrix of data without formulae. If we can't find a more up-to-date package to replace the Excel writer, that would be my suggestion.

          Michael;

          Show
          Michael de Raadt added a comment - The other alternative solution to this problem would be to drop the Excel output option. It is superfluous if we have a working CSV option, which Excel will happily open. The data we are talking about is a static matrix of data without formulae. If we can't find a more up-to-date package to replace the Excel writer, that would be my suggestion. Michael;
          Hide
          Andrew Davis added a comment -

          After some discussion with Michael de Raadt I'm going to remove the Excel option. We can reinstate it once we have a viable means to produce Excel spreadsheets. For the time being Excel users can use csv or odf documents.

          Show
          Andrew Davis added a comment - After some discussion with Michael de Raadt I'm going to remove the Excel option. We can reinstate it once we have a viable means to produce Excel spreadsheets. For the time being Excel users can use csv or odf documents.
          Hide
          Andrew Davis added a comment - - edited

          I have commented out the Excel export option in the UI.

          Once MDL-28070 is done we can put it back in.

          Show
          Andrew Davis added a comment - - edited I have commented out the Excel export option in the UI. Once MDL-28070 is done we can put it back in.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          This has been integrated, thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - This has been integrated, thanks!
          Hide
          Rajesh Taneja added a comment -

          Work's Great
          Thanks for providing the fix Andrew.

          Show
          Rajesh Taneja added a comment - Work's Great Thanks for providing the fix Andrew.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          drop, drop, drop! This is now part of the best Moodle release ever: 2.1 yay! Thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - drop, drop, drop! This is now part of the best Moodle release ever: 2.1 yay! Thanks!
          Hide
          Camilo Rivera added a comment -

          I see this been marked as fixed but in the current source code of Moodle 2.5 the line regarding XLS export is still commented with this note:

          • temporarily commenting out Excel export option. See MDL-19864

          Why is this still commented if the issue has been resolved? I also did a few tests and couldn't recreate the problem with URL fields.

          Show
          Camilo Rivera added a comment - I see this been marked as fixed but in the current source code of Moodle 2.5 the line regarding XLS export is still commented with this note: temporarily commenting out Excel export option. See MDL-19864 Why is this still commented if the issue has been resolved? I also did a few tests and couldn't recreate the problem with URL fields.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Andrew, Camilo,

          please, create a new issue about that, making a reference to this issue. If I'm not wrong we have changed of excel libraries and surely this could be re-enabled and implemented properly.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Andrew, Camilo, please, create a new issue about that, making a reference to this issue. If I'm not wrong we have changed of excel libraries and surely this could be re-enabled and implemented properly. Ciao
          Hide
          Camilo Rivera added a comment -

          Done, I just created a new issue for it:

          Show
          Camilo Rivera added a comment - Done, I just created a new issue for it: MDL-42841
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Thanks!

            People

            • Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: