Importing Scantron Test Scoring Reports into Excel

A. Editing out the undesirable text from the report file

  • Edit the rpt*.txt file with a text editor (i.e. Notepad), NOT a Word Processor (i.e. MS-Word).
  • While not deleting the data rows, delete all text surrounding all the tables, except the data column headers at the top of each table. You should also delete any text splitting the tables as shown below.

Delete any text that is splitting the rows from the original data file

  • Once you have removed all the headers and page separator text, each table should have all their data organized in columns without any breaks.
  • Copy and paste each full table separately into a new text file.  Reason: each table has different column definitions and therefore cannot be imported all at once.  Each table must be imported separately as a separate file.

B. Importing the tables into Excel

  • Launch Microsoft Excel.

From the file menu, select Open

  • From the File menu, select Open.
  • Choose "Fixed width" and click on "Next".

Choose fixed width, then Next

  • Create left and right "break lines" for each data column.  Please note that if the possibility occurs that a column may contain a 3-digit number, you must specify 3 columns for that data column.  (i.e. the % column requires a width of 3-digits due to a possible score of 100)

.Create left and right break lines for each data column

  • Click on "Next".
  • For Name or Student Number data columns, choose "Text".

For name or student number, choose Text

 

  • For numeric data columns, choose "General".

For numeric data columns choose General

For numeric data columns choose General

  • For any columns that you wish to NOT import, choose "Do not import column (skip)".
  • Click on "Finish".  Your data will now appear inside Excel.
  • Repeat this process for each table file that you created in Step A that you wish to import.