# Presentation: 757304

## Creating Flat ASCII Files and Uploading Records

- Training Developed by the QA Program Team

**Notes:**
We would like to welcome you to the Training session that will focus on Creating Flat ASCII files and Uploading records into the ISIR Analysis Tool.

My name is XX and with me today is XX.  We will be alternating between navigating the session and presenting.

XX will be presenting the first part of the session today, and I will present the second half.

The materials are available on our website should need to refer to them after this session.

## Training Objectives

**Training Objectives**

- Identify Population to Upload into the ISIR Analysis Tool for 07-08

- Create and Upload a Flat ASCII file to bring records into the ISIR Analysis Tool

- Review ISIR Analysis Tool Calendar of Activities

**Notes:**
What will we cover during this session?

We will  identify the population that you will need to upload into the tool to analyze for 2007-08.  For QA schools the group to analyze is your institutional verification population. This session is geared for QA schools.  For non QA schools who may view this session, you will determine your own population to analyze.

Together we will review how to create a flat file. You will see that you have several options and you need to determine which works best for your institution. In addition, we will learn how to upload the ISIR records into the tool. There have been no changes in the upload process but you may learn a new option that might work better for you when you begin creating your ASCII file and uploading records. 

Last but not least, we will review the 2007 2008 ISIR Tool  Calendar which outlines required activities related to the ISIR Analysis Tool.

Now I am going to turn it over to XX and she will walk you through the process for creating ASCII files and review one of the three ways to create the file.

## Have you heard? 

**Have you heard? **

- Preparation is everything

**Notes:**
Preparation of the Flat ASCII file is the key. When you plan what is needed with your staff and/or computer support, you will find that you have everything you need to prepare your flat file.

In the next few slides we will discuss what you need to do to prepare for creating your Flat ASCII  file and how to create it.

## Population to Analyze

- A Sample of Records that Met Your Institutional Criteria

**Notes:**
First you need to know which population you will be analyzing.

2007-08 is the year you will analyze a group of records that you verified (based on your criteria). You can analyze up to 1,500 records in the ISIR Analysis Tool.

## Prepare Flat File of Verified Records

- SSN
- First Two letters of the last name (CAPS)
- Initial Transaction
- Paid on Transaction
- Y (to indicate the record met your verification criteria) or blank space

- 111221234MA0105Y
- 111331235LA0204
- 111441236EV0106Y

- Length: 16 Character Field

**Notes:**

To create your ASCII file for your sample of verified records, you will need to know for each record:

the SSN 
First two letters of the last name (must both be in CAPS)
the initial transaction number (just prior to verification) 
the paid on transaction number (after you verified the student) 
All of these records should have been selected for institutional verification. You can include the “Y” in your ASCII file or you may choose to manually update the Verification Flag once your file has been uploaded.  We will discuss this option later.

Why is this sample important?  Because the sample will provide you with an opportunity to determine how well your criteria is working at catching errors that effect EFC and eligibility AND will allow you to determine if adjustments need to be made to any of your criteria for 2008-09.

## Creating Flat ASCII Files

**Creating Flat ASCII Files**

- File with BOTH transactions

- File with only ONE transaction

- File with NO transactions

**Notes:**
Now that you know who and what you need to create your flat file we can begin to create the file. You have a few more decisions to make as you have several options available to you. You can create the following flat files:
File with both transactions
File with one transaction
File with NO transactions

Which is best? That is really a choice you need to make. Let’s demonstrate how to create each and discuss why you might choose a particular option.

Let’s first discuss the option of preparing the flat file with both transactions.  This option works well if you have both the initial and paid on transaction identified prior to uploading your files.

## File With BOTH Transactions

- Flat ASCII File

- Introduce the New Policy and Procedure Assessment

**Notes:**
Let’s discuss how we would prepare a flat file with both transactions. This might be the best way to bring in records since it allows you the opportunity to choose the actual initial and paid on transactions. You can also save a step and add the “Y” as we did in this example which identified that the students met the school’s institutional criteria. By adding this to the flat file it will  automatically upload the school verification flag in the ISIR Tool during the upload process. The School Verification Flag can also be manually updated through the Student Listing on the Tool’s home page.
The flat file would look like the example on the screen only longer. The file needs to be created and saved as a text file.  The easiest way to create the file would be using a program like Microsoft Notepad.  We will provide more information about this on a later slide.

The file contains the student identifier (SSN and first two letters of the last name (both  in CAPS) , the Initial transactions (in this example it happens to be 01) and Paid On transactions (in this example, happens to be 02) and the School Verification Flag  “Y”   You must identify what is the initial and paid on transaction. 
Remember: Preparation is everything. 

SSN flat files can contain up to 10,000 records—however, your ISIR Analysis Tool database can only hold 1500 student records with Initial and Paid On transactions at any given time.  If you are a larger school and have verified more than 1500 records, you can use the random sample feature of the tool to bring in a random sample of up to 1500 records from your Flat ASCII File.  We will demonstrate this in a few slides.

You must, at a minimum, provide the Original SSN and Original Name ID for each student you are requesting in your SSN flat file. The remaining 5 positions in the layout are not required. For example, the “Paid On Transaction Number” field value and the “School Verification Flag” field value can be omitted from the end of each record and you would not need to pad the row with blank spaces. 

## Upload Your Data

**Notes:**
Once you have created the flat file with both transactions, the ISIR Analysis Tool allows you to request:

ISIR Data: This is the option we will choose for this class.  This allows you to upload your Flat ASCII File that that you created for those students for whom you want to request ISIR data from the ISIR datamart.

User-Defined Data: If you choose, this option  allows you to upload your Flat ASCII File that contains the SSN, first two letters of the last name and user defined data that you will want appended to a student ISIR record. We will have another class that will address user-defined data.

## Build/Rebuild

**Notes:**
After selecting ISIR Data, there are several options to choose from during the upload process, they are:          

Build/Rebuild:  Create your database or overlay existing records, clearing and reloading all records.
Append: Adds to the existing records stored currently in the ISIR Analysis Tool database. An example of this is if you uploaded your FLAT ASCII files that contains just an initial ISIR transaction, you use Append to upload your FLAT ASCII file of the paid on transaction.
Clear: Removes all existing records currently in the ISIR Analysis Tool database.
Auto-Load Initial: This feature will add the initial transactions to all records in the ISIR Analysis Tool database that have a paid on transaction but do not have an  initial transaction.  We will discuss this later in the session
Auto-Load Paid-On: This feature will add the paid on transactions to all records in the ISIR Analysis Tool database that have an initial transaction but do not have a  paid on transaction. We will discuss this later in the session.

Since we are bringing records into the Tool for the first time, we would select Build/Rebuild.

## Build/Rebuild

**Notes:**
This is just a reminder message that if you have files already uploaded in the tool this new upload will overwrite them. If you had additional records in the system and you were just adding them you would need to use APPEND option. 

When you are first creating your FLAT ASCII File, you would simply click OK.

## File With BOTH Transactions

**Notes:**
File – With Transactions:  This option is used when you have identified both the initial and paid-on  ISIR transaction numbers on the FLAT ASCII file.

## Random Sample

**Notes:**
This function may be important during 07-08 as your Flat File of Verified Records could potentially contain more than 1500 records.

Would you like to use a random sample of records?  If your Flat File has 1,500 records or less, you would not need to use the random sample feature. However,if you have verified more than 1,500 records, you can write a FLAT ASCII file identifying your institutionally selected population (up to the 10,000 limit) and the Tool can randomly select 1,500 to analyze. 
More about the random sample feature: If your school has verified more than 1,500 students, you would most likely select YES, and then indicate how many records (up to 1,500) you would like uploaded into the ISIR Analysis Tool.  It is important to note that if you upload a file of more than 1,500 records and the "Would you like to use a random sample?" question is set to NO, the ISIR application would select the first 1,500 records.  If the "Would you like to use a random sample question?" is set to YES, you would have to indicate how many records you want in your sample, and the Tool would randomly select a sample out of the number of records in your ASCII file.  On the other hand, you can also use the random sample feature even if your ASCII files contain fewer than 1,500 records.  For example, if you create an ASCII file with 1000 records and only want the ISIR Analysis Tool to upload 500 records, you could set the Question to YES, and than indicate that you would like the ISIR Analysis Tool to randomly select 500 records out of the 1000 records on your ASCII file.

Again, if your Flat File contains more than 1500 records, you would select Yes and let the Tool know how many records (up to 1500) that you would like uploaded from your Flat ASCII File).

## No Random Sample

**Notes:**
On the other hand, if your Flat file contains less than 1500 records and you do not want to limit the number of records that you bring into the ISIR Tool, you would select “No” for the random sample.

It really all depends on how many records you verified and will be bringing into the Tool for analyzing.

Now, let’s find your Flat ASCII File.

## Locate Your Flat ASCII File

**Notes:**
BROWSE:  Once you have created and saved your Flat ASCII file, this option allows you to identify where your FLAT ASCII file is located on your system. Once you have located the file, you will click on “Upload File” to upload the records into the database.

IT IS IMPORTANT TO REMEMBER WHERE YOU SAVED YOUR FLAT FILE!

## Upload File

**Notes:**
Once you have located the file, you would select the UPLOAD FILE button to proceed.

## ISIR Data Confirmation

**Notes:**
Once your file has been uploaded, you will receive the Data Request: ISIR Confirmation Page.

This page will inform you of the number of successfully loaded records and will also alert you to any records from your FLAT File that were not successfully loaded.  

As you can see on the screen, three records were not loaded from our file.  The tool will alert you the reasons why the records were not loaded.  In our example, one record did not exist, and the other two records did not have a valid EFC.  Those are two common errors that you may find.  Once you receive this confirmation, simply hit the RETURN button.

## Data Validation

**Notes:**
Data Validation: Checks to make sure you have both an initial and paid on transaction for each ISIR record. If not, it will print a list out as to who didn’t and their record will not be uploaded and will be deleted. 

Why might this happen? Your school code is not on both the initial and paid on record, or SSN doesn’t match or the Student doesn’t have a valid EFC.

## Data Validation Confirmation

**Notes:**
This function lets you know the number of records in your database and that validation has been conducted. You can’t get into data analysis unless you have validated your records. 

As a side note, If you append records to add additional students to the database – you MUST always conduct data validation to have your reports reflect the correct data. 

Once you receive this confirmation, again you would simply hit the RETURN button.

## Current Status

**Notes:**
Haven’t been in the tool for awhile and not sure what is in your database? Just select the from the menu  Current Status: This screen displays the number of initial and paid-on records in the Tool database. There are 363 initial and paid on Records loaded into the Tool database.

Now, we will discuss the other options for creating the Flat ASCII file.

## File With Only ONE Transaction 

**File With Only ONE Transaction **

- Initial Transactions

- Paid-On Transactions

**Notes:**
Let’s look at the option of preparing a flat file with just one transaction. You can use this option to upload only one transaction (either the initial or the paid on).  Does it matter if you upload the initial or the paid on first?  Answer: NO, there is no required order. Remember, when f you bring in subsequent records you would use APPEND.

Let’s look at your options for bringing in the Initial transactions first.  You could consider this option if you would like to choose the transaction you want for your initial transaction. You could then use the auto load paid on feature to bring in the paid on transaction eliminating the need to prepare a second flat file. 
Please note, if you choose to upload the initial transaction only and want to include the “School Verification Flag” field “Y”, the two blank positions for the “Paid On Transaction Number” field value must be included prior to the value for the “School Verification Flag” field.  
You can see an example of this on the left-hand side of the slide.

Please note I added  the “Y” at the end of both files on the screen. I did this because all students you analyze in 07-08should have met your institutional criteria. This would save the step of having to update that field at a later date. Remember, you will need to update that field for all records you bring in.

Let’s look at your options for brining in the paid on transactions first.  You can consider this option if you would like to choose the transaction you want for your paid on transaction. You could then use the auto load initial feature to bring in the initial transaction eliminating the need to prepare a second flat file. 

How do you prepare your flat file?  First, create a flat file with the paid on transactions. This would allow you to identify the ISIR you paid on. You can see how the file would look on the right hand side of the slide.

An important note here, If you prepare accordingly, you should be able to write your flat files and identify the ISIR records that were considered initial and paid on (by creating a file with both transactions as discussed on the prior slides)

## Locate Your Flat ASCII File

**Notes:**
This process should look familiar.  Remember, when you select the Browse button, it will allow you to go out and identify where you saved the flat file. Double click on the flat file and it will appear in the box. Select upload file and it will take that file and go out to the data mart and bring the records into the tool.  

It is important to remember where you saved your Flat ASCII file. You may want to write the location down so you can easily remember where it is once you are ready to upload your records.

## Don’t Forget ...

**Don’t Forget ...**

**Notes:**
REMEMBER TO perform DATA VALIDATION. This ensures that you have both an initial and paid on transaction for each ISIR record you are uploading. Having both an initial and paid on record is necessary so that you can determine any differences between the initial and paid on ISIR records. 
Records may be rejected if either the initial or paid on ISIR did not have your school code or an official EFC. Those rejected will be listed after validation has been completed.

If you want to research and determine why records were rejected,  you can print the page as a reference. Once you identify why a record was rejected you can use the APPEND function to  pull in a subsequent ISIR record that has either an official EFC or your school code.  

## File with NO Transactions 

**File with NO Transactions **

- Create a flat file with just SSN and First two letters of the last name

- No blank spaces needed at the end of fileLength: 11 Character Field

**Notes:**
Let’s discuss creating A Flat ASCII file with NO Transactions.  This file only contains the SSN and first two letters of the last name.  You will not include any transaction numbers.  When you upload this file, the system will select the lowest and highest transactions for the initial and paid on.

What is the down side of this option? It doesn’t allow you to identify what the actual initial or paid on records was. The 01 transaction is not always the initial transaction prior to verifying the record. 
The same holds true for the paid on – the highest doesn’t always reflect the ISIR record you paid on after verification.
On the screen you can see the example of what a Flat File with no transactions would look like.

NOTE:  If you are creating the Flat File without transactions, no blank spaces are required after the first two letters of the last name.  The exception would be if you wanted to also populate the “Y” field (which identifies institutionally selected records), you would need to include 4 blank spaces before the Y for each record on your ASCII file that you want to populate the Verification Flag Field (there would need to be a total of 16 characters, i.e. SSN, First two letters of last name in CAPS, followed by 4 blank spaces, followed by Y for a total of 16 characters)

## File With NO Transactions

**Notes:**
Once you create and save the Flat file, you would upload the file using the same procedures we followed earlier, except you would need to indicate you would like to load a file without transactions.

This option would bring in the highest and lowest transaction from the data mart.

## Slide 25

**Notes:**
When uploading a file with NO TRANSACTIONS there are several questions you would have to answer and you can see them here on the screen.

Would you like to use a random sample of records?  For our example, we would say NO since our FLAT file for will contain less than 1500 records.
BUT, If your flat ASCII file contains more than 1500 records, you would select Yes, and a random sample of up to 1500 records will be extracted from the data mart.  

What Type of transactions are you requesting?  For our example we will select Both Initial and Paid on.

If you are requesting Initial Transaction, what is the processed date you would like to use for selecting Initial Transaction? Most of the time, you would enter today’s date and that would bring in all ISIRs processed through that date.

If you are requesting Initial Transactions based on a processed date, would you like the Lowest or Highest transaction? This allows you the flexibility to select what transaction you need.  In most cases, you would select the Lowest Transaction.

## Auto Load Initial

**Step 1: ****Prepare and Upload Paid-On Transactions****into the tool.****Step 2: ****Use Auto Load Initial feature****to bring in the****initial transactions**** **

**Notes:**
Let’s Now take a look at how the auto load feature of the tool works and how it can assist you with uploading the initial records.
As you can see on the left hand side of the screen, you would first prepare and upload the paid on transaction first.  
Auto-Load Initial: This feature will add the initial transactions to all records in the ISIR Analysis Tool database that have a paid on transaction but do not have an initial transaction.  The auto load feature is a quick way to add the missing transaction numbers for schools that have used the Build/Rebuild function and only loaded one transaction (i.e. Paid On).  The auto-load initial feature will tell the system to go out and get the lowest transaction that exists in the data mart for the students that were already loaded as paid-on.

Why use the Auto-Load Initial Feature?
It saves time since you do not have to create another Flat file identifying the initial records.  As you see, the auto-load initial feature is useful to create your ASCII file identifying paid-on records.  Once you create your ASCII file with Paid on Transactions only and bring those records into the Tool, you can then use the Auto-Load initial feature and the ISIR Tool will automatically bring initial records that have a  paid-on record currently in the Tool. 

If you choose not to use this method, you can keep your previous flat file and modify it at a later date reflecting just the initial transactions for those students you brought in as paid on. 

## Auto Load Paid-On

**     ****Step 1: ****Prepare and Upload Initial Transactions****into the tool.**
**     ****Step 2: ****Use Auto Load Paid-On feature****to bring in the****Paid - On transactions**

**Notes:**
Let’s look how you would Upload your database with a flat file consisting of Initial Transactions and use the Auto Load Paid-On feature to bring in the paid on transactions.

Auto-Load Paid-On: This feature will add the paid on transactions to all records in the ISIR Analysis Tool database that have an initial transaction but do not have a paid on transaction. The auto load feature is a quick way to add the missing transaction numbers for schools that have used the Build/Rebuild function and only loaded one transaction (i.e. Initial).  The auto-load paid on feature will tell the system to go out and get the highest transaction in the data mart for records for which an initial transaction already exists is uploaded into the Tool.

Why use the Auto-Load Paid on Feature?
It saves time since you do not have to create another Flat file identifying the paid-on records. As you see, the auto-load paid on feature is useful to create your ASCII file identifying initial records.  Once you create your ASCII file with Initial transactions only and bring those records into the Tool, you can then use the Auto-Load paid on feature and the ISIR Tool will automatically bring in the paid-on transaction for all initial records currently in the Tool.  
If you choose not to use this method, you can keep your previous flat file and modify it at a later date reflecting just the paid on  transactions for those students. 
If you created your ASCII file with one transaction and would like to bring in the other transaction, you can also Use the EXPORT feature to bring in the other transaction.  If you brought in the initial transactions first, you could use the Export function of the Tool.  By selecting the EXPORT button from the home page menu, the system would send a flat file to your SAIG mailbox for the initial records you currently have in the tool.  It would send the file to your SAIG mailbox where you can obtain the file and then you could indicate the paid on transactions you want and then use that file to upload your identified paid on records.

## Don’t Forget ...

**Don’t Forget ...**

**Notes:**
REMEMBER TO perform DATA VALIDATION. This ensures that you have both an initial and paid on transaction for each ISIR record you are uploading. Having both an initial and paid on record is necessary so that you can determine any differences between the initial and paid on ISIR records. 
Records may be rejected if either the initial or paid on ISIR did not have your school code or an official EFC. Those rejected will be listed after validation has been completed.

If you want to research and determine why records were rejected,  you can print the page as a reference. Once you identify why a record was rejected you can use the APPEND function to  pull in a subsequent ISIR record that has either an official EFC or your school code

## Institutional Verification Flag

**Notes:**
What about the school Verification Flag?  Since you are analyzing your institutionally selected records for 2007-08, all of the records would need to be flagged as a “Y” to indicate they met your criteria.

We have discussed that you can include the school verification flag with your ASCII file.  Let’s take a couple of minutes to discuss the different ways you can set the school verification flag.

Under the Student Listing Menu you see there is a predefined filter “School Verification Flag”.  You need to populate this field in 2007-08 to flag all records in your sample (since you are analyzing a sample of records that met your criteria) You can do this several ways:

Manually update through Student Listing – UPDATE function. Since all records need to be flagged, you can scroll down to the bottom and select the SELECT ALL function.  Then click SAVE.  Since each page of the listing contains 500 records, you will need to make sure that you perform this process for every 500 records (if you have more than 500 in your student listing).
When you create your initial flat ASCII file.(include SSN, first two letters of the last name (CAPS), the initial and paid on transactions  and the letter “Y” (CAPS).  We just demonstrated that in our prior examples.

## Resources Available for Creating Flat ASCII Files

- EDE Express
- Note Pad
- Excel
- Text Pad
- _[www.Ifap.ed.gov/qahome/guidance.html](http://www.ifap.ed.gov/qahome/guidance.html)_
-     Select: Chapter 1- Getting Started with the ISIR Analysis Tool

**Notes:**
You can use EDE Express to assist you in writing your flat file. Directions in the most recent ISIR Analysis Tool Guide -Appendix C – are still relevant.

A flat file can be created in a text editing program such as  
Notepad
Text pad, or 

Excel. Remember to save the flat file as a text file. 

Refer to the 2006-07 ISIR Analysis Tool Guide – Chapter 1 for detailed information about creating and uploading records

## 2007-08 Timeline of Activitiesfor the ISIR Analysis Tool

| February | 2007 2008 Version of IA Tool on the Web is available |
| --- | --- |
| March – September | Begin implementing your verification selection criteria & complete verifying your applicants |
| October | Prepare flat file, upload data;  |
| November, December, January  | Analyze Data; FSA collects school data; Notify QA Rep. of institutional verification criteria |

**Notes:**
Here, we provide you with a timeline of when you should begin activities that involve the ISIR Analysis Tool. You will verify those students you have selected for institutional verification. In the fall, you’ll prepare and upload a flat file and begin analyzing your data so that you can revise, update, or validate what your verification program will look like for 08-09. You can upload a maximum of 1,500 records in to the tool to analyze. We recommend not less than 350 (unless you verified less than 350 ISIR records). 

## What We Have Learned

**What We Have Learned**

- Preparation Needed For Creating Flat ASCII Files

- How To Create and Upload a Flat ASCII File

- ISIR Analysis Tool Calendar of Activities

**Notes:**
We have learned that preparation is key to collect all the information we need to create a flat ASCII file. 

There are several ways you can create the flat file and the resources available to help you do that. 

In addition, we reviewed the 2007-08 activities related to the ISIR Analysis tool.

## QA Regional Representatives

**Regions 1, 2, and 5 – Holly Langer-Evans – (617) 289-0136**
**Holly.Langer-Evans@ed.gov**
**Regions 3, 4, 6 and 7 – Anne Tuccillo – (202) 377-4378**
**Anne.Tuccillo@ed.gov**
**Regions 8, 9, and 10 – Michael Cagle – (206) 615-2586**
**Michael.Cagle@ed.gov**

**Notes:**
If you have not already done so, get to know your QA Regional Representative.  We are all here to help you be and do the best that you can.  We  always look forward to working with you.

## Training Available on QA Website

- QA Requirements for 2007-2008
- Getting Started in the QA Program
- What's New with the ISIR Analysis Tool for 2007 - 2008
- Creating Flat ASCII Files
- Maximize User-Specified Fields 2007 - 2008
- Making the Most of your Analysis

**Notes:**
Related training sessions on “What’s New with the ISIR Analysis Tool, Maximizing User-Specified Fields, and Making the Most of Your Analysis Efforts, are also available on the QA website. 
You will be able to access all of the sessions you see on the screen and view them from our website.

We hope you enjoyed the session today. On behalf of the QA Program, we wish you the best of luck with your QA Activities.

Have a great day and as always contact us with any questions.

## Questions