Using the Excel Template to create text files for submission via the Automated Data Exchange

Once you haveentered datafor each of your students in the template, save the document with a name such as “2012-13 EOY File”. This is the document you will use throughout the EOY data submission phase to enter, change, and add to your files as you work through the edit checks.

A few tips for entering data into the template:

  • Do not change the column width for any of the columns in the template. These are already sized to match the field layout so that when you convert the Excel document to a text file, all the field lengths are correct.
  • Do not change the format of any of the cells. The entire workbook is already formatted as text, which is what is required.
  • Don’t leave blanks cells in any student record.
  • Make sure the data you enter in each cell is the correct number of digits in length. For example the Grade Level field is always three digits long. So, a 12th grader needs to be entered as “120”, a first grader as “010”, a half-day kindergartener as “006” and so on. If you run into problems with Excel cutting off the leading zeros, you can enter an apostrophe ( ‘ ) before the numeric digits. So, rather than 004 for a Pre-K student, you can enter ‘004.
  • If you are copying data from another source into the template (such as from another Excel document or directly from your student information system), you should copy the fields/cells you wish to transfer then use the “Paste Special” option (located under the paste function on the main toolbar in Excel). From the paste options menu, select either “Text” or “Values”. This process will prevent your data from being reformatted as something other than text when you copy it into the template.

Each time you want to make a file submission via the Automated Data Exchange, you will need to complete the following steps: (Note: you will need to follow this process for both the header and the detail file)

  1. Save a copy of the master template under a new name each time you are ready to make another file submission via the Automated Data Exchange, such as “Submission1”, then “Submission2” and so on.
  2. Remove the column headings by deleting the entire first 2 rows in the template (row 1 and 2) and any unused rows below your data. If working in the header file also delete all rows after the adjustment fields.
  3. Select “Save As”, then under the “Save as type” drop down menu click on “Formatted Text (Space Delimited) (prn)”. Save each of these text documents to a folder or location where you can easily find them later.
  4. Close the document. When the screen with the message “Do you want to save changes?” appears, click on the “No” option.
  5. Go to the location where you saved the ".prn" file. Right click on the ".prn" file then click on Rename; the file name should have a box around it. Type the name of the file as described below and then press return.
  6. You will need to do this once for the Header File (A) and once for the Detail File (B).

Data File Name format is: SEY####.nnA

  • SEY - Indicates End of Year collection
  • #### - Is your four digit District/BOCES code
  • .nn - Is the file version number (01, 02, 03, 04.... 99) - The Automated Data Exchange will tell you which file version should be submitted next.
  • A - Is the file format for the Header File
  • B - Is the file format for the Detail File
  1. When you rename the file, you may receive a pop-up warning stating “If you change a file name extension, the file may become unusable. Are you sure you want to change it?” Click on the “Yes” option.

You may experience difficulty completing step 5 above (renaming the text document). For example, your operating system may not allow you to change the file extension from “.prn”, or you may find the system is automatically adding an extension (like .prn or .txt) to your document after you try to rename it (so the resulting document will be read by the ADE system as, for example, SEY###.08a.prn). If this is the case, the ADE will not accept your text file and you will need to complete the following steps:

a)Close the document if it is open

b)Open the folder in which the document resides

c)At the top of the folder window – in the toolbar – select “Tools”, then “Folder Options”

d)In the window that pops up, select the tab titled “View”

e)Uncheck the box next to the text “Hide extensions for known file types”

f)Click on the “Apply” button