loading compass scores

Problem Description :

Loading compass scores

Resolution :

Loading Scores

*** Extracting Data from eCompass

1. Logon to eCompass web site using the jehayes account. (url is https://compass.act.org/eCompass, the C in eCompass must be upper case)

2. From SESSIONS tab select "Create SSR, MSR or CSV files for use with Microsoft Excel® or Microsoft Access" option under Student Records: section

3. Specify dates that include all EXIT testing being loaded, select Gordon College as the Test Center, select Concatenante SSRs into one CSV file and click on Create SSRs

4. Let Excel open the CSV file and save it to "n:\csd\compass\banner export" with name of ecompass_termcode.csv (add a and b after temcode for summer sessions).

*** Organizing data into format for import into Banner

1. Sort on column "FA" which should be labeled Pre/Post

2. Remove all rows coded as "T" which indicates Placement. Leave rows coded as "F" which is Exit

3. Sort on colum "E" which should be labeled Status

4. Remove all rows coded as "I" which indicates Incomplete. Leave rows coded as "C" which is Complete

5. Re-arrange, delete and combine columns to get in the following order - current columns in ():

A - blank

B - Student ID (D)

C - Last name (A)

D - Combined First Name and MI with space between (B & C) =E2&" "&F2 (copy all formulas to values

E - Blank - eventually to hold formatted DOB

F - DOB (AZ)

G - Session Number (AU)

H - Blanck - eventually to hold Test Subj

I - TP Code - used to determine Test Subj (AV)

J - Blanck - eventually to hold test date

K - Date (F)

L - Blank - eventually to hold test score

M - Writing Skills Score (I)

N - Reading Skills Score (J)

O - Algebra Score (M)

P - Blank

Q - Blank

R - Blank - eventually to hold test date

6. Setup formulas and formatting in columns as follows:

E - DOB - use formula =DATE(LEFT(F2,4),MID(F2,5,2),RIGHT(F2,2)) and format as dd-Mon-yyyy. Do a Copy/Paste Values

H - Enter COMR for all TP Codes of 174346, COME for all 174347 and COMM for all 174353

J - Test Date - use formula =DATE(LEFT(K2,4),MID(K2,5,2),RIGHT(K2,2)) and format as dd-Mon-yyyy. Do a Copy/Paste Values

L - Score, copy/paste Writing Skills Score for COME, Reading Skills Score for COMR and Algebra Score for COMM

R - Test Date, copy/paste the first test date column (J)

7. Delete unneeded columns

F - Unformatted Birth Date

I - TP Code

K - Unformatted Test Date

M, N, O - Subj Scores

8. Delete unneeded row 1 (old column headings)

8a. Final Columns - a= blank b= id c=lastname d= first and middle i e= dob f= session id g= subject h= test date i= score j&K blank l= test date

9. Sort data based on columns so highest score for retakes are loaded first (contraint error will prevent 2 tests on same date)

B - Student ID ascending

G - Subj Code ascending

I - Score descending

10. Save in CSV format to ecompass_term.dat.csv (add a and b after temcode for summer sessions).

11. Close Excel.

*** Formatting in Textpad

1. Open the ecompass_term.dat.csv file in Textpad

2. Also open previous compass.dat in Textpad to serve as example of layout

3. Do following Search/Replaces (F8) being sure "Regular Expression" option is selected :

^ with | (^ indicates start of line)

, with |

$ with |||||||||||| ($ indicates end of line)

4. Save file

5. Exit Textpad

6. In My Computer browser or CMD prompt copy file to compass.dat for script to load

*** Running import from CMD prompt

1. Open CMD Prompt

2. Navigate to "n:\csd\compass\banner export" folder

3. Run batch command oracmd_load.bat to load tests from compass.dat to table WORTEST

*** Inserting WORTEST records into SORTEST using TOAD

1. Launch TOAD if needed

2. Open file "n:\csd\compass\banner export\insert_compass_exit.sql" (do not split into parts)

Suggested knowledgebase articles:

loading compass scores

Article Details

Related articles