Moodle

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

Details

  • 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 from Repository:
  • Pull Master Branch:
    MDL-19864_database_excel_export
  • Pull Master Diff URL:

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

Issue Links

This issue will help resolve:
MDLQA-964 CLONE - A teacher can export database activity entries in a choice of formats Passed
This issue will be resolved by:
MDL-28069 Evaluate use of Excel exports throughout Moodle Minor Closed
This issue has been marked as being related by:
MDL-28070 Review Excel writer package Major Open

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
Eloy Lafuente (stronk7) added a comment -

This has been integrated, thanks!

Show
Eloy Lafuente (stronk7) added a comment - This has been integrated, thanks!
Hide
Permalink
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
Permalink
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!

Dates

  • Created:
    Updated:
    Resolved:
    Integration date:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.