Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-39156

Microsoft Excel refuses to open CSV files

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Development in progress
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.4.3, 2.6.4, 2.7.1, 3.3.3
    • Fix Version/s: BACKEND
    • Component/s: Reports
    • Labels:
    • Testing Instructions:
      Hide

      (difficulty: easy, requires Excel to test it or to look at the header of the exported CSV file)

      Download the completion report as CSV file:

      • with Excel: no error about a SYLK file should appear ("SYLK: File format is not valid");
      • without Excel: open the file and look at the first header in the first row, you should read "UserID" and not "ID" when using English or "Id" in any other language (e.g.: Italian) which localized the id of report_completion as ID.
      Show
      (difficulty: easy, requires Excel to test it or to look at the header of the exported CSV file) Download the completion report as CSV file: with Excel: no error about a SYLK file should appear ("SYLK: File format is not valid"); without Excel: open the file and look at the first header in the first row, you should read "UserID" and not "ID" when using English or "Id" in any other language (e.g.: Italian) which localized the id of report_completion as ID .
    • Workaround:
      Hide

      Change the localization from ID to UserID in id from report_completion.

      Show
      Change the localization from ID to UserID in id from report_completion .
    • Affected Branches:
      MOODLE_24_STABLE, MOODLE_26_STABLE, MOODLE_27_STABLE, MOODLE_33_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      m28_MDL-39156_CSV_Wrongly_Read_As_SYmbolic_LinK_AKA_SYLK_file

      Description

      When Moodle exports data to CSV (e.g. course completion reports), it includes column headings in the first row. This is not a problem, except when the first column of the CSV file is an ID field. In this case, the CSV file starts like this:

      ID, col2, col3, col4

      Unfortunately, Microsoft Excel can't handle CSV files where the first two characters are 'ID'. It looks like Microsoft use a bat-**** insane method of detecting file formats. If you open a CSV in Excel and the first two characters are 'ID', Excel decides it's not a CSV, it's actually a SYLK file. It tries to open it as a SYLK file, and fails. Because it's not a SYLK file, it's a CSV.

      Now, this is obviously Excel's fault, not Moodle's. Microsoft do have a knowledgebase article for this issue, but their helpful answer is "we won't fix this, just make sure your CSV file doesn't start with 'ID'."

      The official answer to this is to enclose 'ID' in double quotes. A simple solution in Moodle would be to enclose everything in the column header row in "double quotes".

      Microsoft Knowledgebase article on this issue on Excel 2003 (we can confirm the issue is also present in 2007 and 2010): http://support.microsoft.com/kb/323626

      Helpful blog post on the issue: http://efeamadasun.com/post/35734306022/excel-has-detected-that-csv-is-a-sylk-file-but-cannot

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                12 Vote for this issue
                Watchers:
                20 Start watching this issue

                Dates

                • Created:
                  Updated: