Last updated June 13, 2007

Viewing CTAT Log Data in Excel


In it's raw form, CTAT log data is escaped XML text that can be be processed for including in an OLI database; however, this format is not easily readable, nor can it be opened in a spreadsheet editor such as Excel. This article describes how to use a CTAT program called LogFormatUtils to transform raw CTAT log data into an XML format that is human-readable and can be opened in Excel.

Transforming raw log data with LogFormatUtils

To transform raw log data with LogFormatUtils, you will need:

  • CTAT 2.2 or greater

  • Java Runtime Environment (JRE) v1.4.2 or greater

  • Raw CTAT log files (text files created by CTAT ending in .log)

Optionally, you can include browser logs created by the Firefox extension Slogger. This feature is sometimes used in experiments where you are logging both use of a tutor and Web browsing behavior. If present, the CTAT program will merge (based on timestamps) and transform both the CTAT log files and browser log files to a single file readable in Excel.

To transform CTAT log data for viewing in Excel:

  1. At a command prompt, change the current directory to the CTAT home directory (eg, "C:\Program Files\Cognitive Tutor Authoring Tools" on Windows or "/Applications/Cognitive Tutor Authoring Tools" on Mac OS X):

    C:\>cd CTAT
  2. Run the LogFormatUtils program, specifying the CTAT log file to transform, browser log file, the name of an intermediary file that holds the merged output, and the name of the final output file:

    C:\CTAT>java -cp "lib\DorminWidgets.jar" edu.cmu.pact.Log.LogFormatUtils \
      user_20070522170645382.log browser_log.xml tempfile finallog.xml 
    • user_20070522170645382.log: the CTAT log file that you'd like to view.

    • browser_log.xml: the Slogger browser log to include. If you're not including a browser log, use a fake name such as this one.

    • tempfile: an intermediary file that holds the merged output. This can be anything you'd like, as it's only used internally by LogFormatUtils to create the final output.

    • finallog.xml: the final output file. This is the file you will open in Excel.


    You can see the LogFormatUtils program's expected syntax by running the program without any files specified.

  3. Open the final output file in Excel.


    If prompted by Excel to select how to open the file, choose "As an XML list". You can ignore the following message about the lack of an XML schema, or any errors that appear in the "XML Import Error" window that mention importing data as text—click OK to view the data.

To customize the columns that are included in the output file:

By default, all possible columns are included in the output. Most of these columns map to elements or attributes described in the tutor_message DTD user's guide. You can customize the set of columns that are included in your output file:

  1. Open the file logformatprefs.xml in a text editor. By default, this file exists in the CTAT home directory.

  2. To exclude a column, change its value (the text in between the opening and closing XML tags) to FALSE. To include a column, change its text value to TRUE.


    In addition, you can set <show_focus_entries> to FALSE to remove rows that describe CTAT windows gaining or losing the user's focus.

  3. Save the file, and re-run LogFormatUtils as specified above.