Preparing data

The samples and examples found in this website have, of course, all been prepared in advance. Almost all of the examples have real datasets ready for you to download. When they come down the internet "wire" to your computer, they do so (or are supposed to do so) as Excel workbooks, set up as Lertap workbooks. (Click here for a definition of what makes an Excel workbook a "Lertap workbook".)


Okay, now let's anticipate the day when you'll want to make your own Lertap workbook. How will you do it?


First of all, let's take a straightforward, common, example: you've got a pile of test or survey results sitting next to you on your desktop. They might be the actual questions, with marks where students or participants have indicated their selected answers (such as circles around item options). How to enter the results into Excel so that they meet the requirements of a Lertap workbook?


Not too hard to do at all. You'd go to the New menu, and there you'd probably select the "Blank" option. This action will set up a new Excel workbook and it'll have the two worksheets, Data and CCs, required to make it a "Lertap workbook". Easy-peasy, right? Then all you need do is type answers into the Data sheet, and put at least two lines in the CCs worksheet: a *col line, followed by a *key line if you have a test, or a *sub line if you have a survey.


No need to get overly anxious about these tasks -- we've got two pre-prepared samples on this website with answer sheets having actual item responses. These may be used for practice; you print the answer sheets, create a new Lertap workbook, and then enter the responses, by hand (fingers), into the Data worksheet (great fun, much more rewarding than vacuuming the carpets, if that's what you were supposed to be doing). One of these samples is ChemQuiz, the other is CEQ. These exercises are typical of those completed by instructors who want to process a class test or survey.


After you've done this a couple of times, you may come to regard one of the other options on the New menu as a shortcut. This could happen, for example, if you repeatedly use the same test or survey. In this case, the New menu's "Headers" option, or the "Copy" option, could save you a wee bit of time.


Someone asks: "Must I use Lertap's New menu to set up a new workbook? Can't I use Excel?"


No. Yes.


No, users don't have to use Lertap's New menu. Yes, Excel's  File  tab, and then its New option, will certainly create a new workbook. Its worksheets will have names such as "Sheet1", "Sheet2", and so on. You must then change the names of the worksheets; one of them should be named Data, the other CCs. If Excel gives you "Sheet3" as well, you can delete it, although this is not required. The advantage of using the "Blank" option on Lertap's New menu is that it does these things for you, saving time.


What about this situation: somehow you obtain an Excel workbook already set up with rows containing student data, such as their IDs, maybe a class code of some sort, and then columns with item responses. Great. Make sure the name of the worksheet with the data is called "Data". Make sure this Data worksheet has some sort of title its first row, and that its second row has column headers, such as, for example, ID, Class, I1, I2, and so on. Then add a CCs worksheet, and pop at least two lines into it. (Look here for an example of this process.)


Now, we've saved a very common situation for last: you've got a data file created by a scanner.


Scanners prepare their data files in a variety of ways. There are some which will create a "csv" file (comma-separated values), and these files can be opened straightaway in Excel without much ado at all. Should this happen to you, open the csv file with Excel, and then save it as an Excel workbook, using the options on the  File  tab. Then go about making sure that the name of the worksheet with data is named "Data", format the Data worksheet so that its first two rows are as expected by Lertap, add a CCs worksheet with at least two lines, and Bob's your uncle! (We've got a beaut example of this very process. Clickity click click here.)


Many scanners create data files which are text files, often called ASCII files. These files will usually have an extension of "dat" or "txt".


Excel has a "Text Import Wizard" which greatly eases the task of preparing text files so that Excel can work with them. It isn't too hard to use; the next topic shows how (Ted's your aunty!).


How'd you like to have a sample dat file to work with? Okay, can do, the next paragraph has an option to download such a file, which was originally from the University of Iowa. It's called "mondaty.dat".


How'd you like to have some comments on how to prepare this dat file for Lertap? Hmmm, once again, you're in luck, we've got a special document for you (a web page, in this case): click here, and NOTE: the download link in this document looks for the file at This link no longer works. Download the file from here instead.


You'll note that the document does not use the Text Import Wizard. What you could do for a bit of excitement is to use the Text Import Wizard to accomplish what's done by copy and paste in the document. The copy-and-paste approach works in this document as the mondaty.dat file is very simple, with no ID fields of any sort before the actual item responses; this is not at all characteristic of what scanner output files usually look like. See why: page ahead.





If Bob is not your uncle, and Ted is not your aunty, just pretend that they are when you come to Australia (where these terms are common).