Batch Load of Communications Process<br>1. Create an Input File

Overview

This document describes step one of the Batch Load of Communications process.

Step-by-Step Procedure

1. Query data

Offices run queries from their source database(s), such as the data warehouse, or enter the data directly into an Excel file to create an input file. The input file identifies the EmplIDs that are to be assigned a given communication. The file contains EmplIDs, Communication Speed Keys, and other required data based on the Administrative Function.

2. Format data

Offices must format the data using a template for the appropriate Administrative Function, and name and save the file to a local drive. 3C Resources offers file templates to help you create your input file.

Sample Template (ADMP) - columns A-I

sample template columns A-I

Column	Date Element (field)	Valid Values	Required?
A	Admin (Administrative) Function	ADMP, ADMA, AWRD, FINA, GEN, LOAN, PROP, PROS, SPRG, STRM	Yes
B	Comm Key	Any valid value from COMM_SPDKEY_TBL for the Admin Function (see valid Admin Functions above)	Yes
C	EMPLID	Valid unique person key from M-Pathways	Yes
D	Last Name	Last name	Yes
E	Variable Data 1	GEN – Blank PROS, PROP – 4-character Academic Career ADMA, ADMP – 4-character Academic Career AWRD, FINA, LOAN - 4-digit Aid Year SPRG, STRM – 4-character Academic Career	Varies by Admin Function
F	Variable Data 2	FINA, GEN, PROS, – Blank PROP – 5-character program code ADMA, ADMP – Student Career Number from application AWRD – 4-character Academic Career LOAN – 4-character Academic Career (optional) SPRG -- Career Number STRM – 4-character Academic Term	Varies by Admin Function
G	Variable Data 3	FINA, GEN, PROS, PROP, SPRG, STRM – Blank ADMA, ADMP – Application Number AWRD – Item Type (optional) LOAN – Loan Type Cd (optional)	Varies by Admin Function
H	Variable Data 4	GEN, PROS, PROP, ADMA, AWRD, FINA, SPRG, STRM – Blank ADMP – Application Program Number LOAN – Application Sequence (optional)	Varies by Admin Function
I	Variable Data 5	ADMA, ADMP, AWRD, FINA, GEN, PROS, PROP, SPRG, STRM – Blank LOAN – Loan App ID (optional)	Varies by Admin Function

Sample Template (ADMP) - columns J-O

sample template columns J-O

Column	Date Element (field)	Valid Values	Required?
J	Communication Date	Format must be mm/dd/yyyy May be blank on the input file, then will be loaded based on the Run Control parameter on the Communication Batch Load page Note: If a different date is specified on the Run Control parameter (see step 2), it takes precedence over the date in the input file.	No
K	Completed Comm Flag	Y or N Y= required on input if a completed date is on input file May be entered on Run Control; defaults to 'N' if completed date is blank Note: The Run Control parameter takes precedence over what is in the input file.	No
L	Completed Date	Only used when the Completed Comm Flag = Y (format is mm/dd/yyyy) May be blank on input file if entered on Run Control or defaulted to System Date on Run Control Note: The Run Control parameter takes precedence over Completed Date in the input file	No
M	Completed Id	Valid EMPLID Required on input file if a date is Completed Date on input file May be entered on Run Control and will defaults to ID associated with Run Control Note: The Run Control parameter takes precedence over a different Completed ID in the input file.	No
N	Letter Print Date	Format is mm/dd/yyyy May be blank on input file May be entered on Run Control or defaults to System Date on Run Control Note: The Run Control parameter takes precedence over dates from the input file.	No
O	Letter Signer Id	Valid signer’s Emplid, which must have already been set up and exist on Comm Signer table Signer ID can be blank on the input file Note: If a Letter Signer ID is specified in the input file, but has not been previously set up, the upload process will not reflect a signer for the communications.	No

Sample Template (ADMP) - columns P-W

sample template columns P-W

Column	Date Element (field)	Valid Values	Required?
P	Enclosure Code 1	Valid 3-character enclosure code	No
Q	Enclosure Code 2	Valid 3-character enclosure code	No
R	Enclosure Code 3	Valid 3-character enclosure code	No
S	Enclosure Code 4	Valid 3-character enclosure code	No
T	Enclosure Code 5	Valid 3-character enclosure code	No
U	Enclosure Code 6	Valid 3-character enclosure code	No
V	Comment Print Flag	Y or N; defaults to 'N' if left blank	No
W	Comments	Unit’s internal communication note or comment printed in a letter Note: Files created before 5/25/05 no longer require quotes.	No

Input file requirement tips

File requirement tips
Administrative function	Only one Administrative Function for each input file
File template	Each data element (field) must be positioned in a specified order in the file. Use the file template for the appropriate Admin Function; each template is for use with one Admin function.
File format and naming standards	File must be comma separated (.csv) or an Excel file (.xls). Note: Excel 2007 users will need to select the .xls file type extension when saving the spreadsheet (Microsoft Excel 97-Excel 2003 & 5.0/95 Workbook) (.xls). To save your file in the .csv format, use the Save As command and choose the appropriate .csv type: Excel for Windows: CSV (MS-DOS) (.csv) Excel for Macintosh: CSV (Windows) Note: Do not use the CSV file format CSV (Comma delimited) (*.csv)
Valid values	Have valid dates formatted MM/DD/YYYY (e.g., 07/01/2005) Year must be four (4) digits (YYYY) Name is LAST name
Cell formats	Do not format cells using Custom Formats (Format > Cell > Number > Custom)

Input file recommendation tips

File rcommendations
File size	If you have more than 10,000 rows of data to be imported, split the file into multiple files, each to be uploaded separately. If uploading a file during student registration, split the file so that each file contains less than 8,000 rows to avoid decreasing M-Pathways speed.
Column headings	If headings are loaded column headings (headers) from the Excel file, the first field must be Admin Function. Note: A header row may be present in your file, but when the system reads the file, the system ignores the row. The first four fields are always required. The next five fields may be required depending on the Administrative Function.
Values	Leave off leading zeros from numeric codes, such as emplid, acad_prog, and application number (the Batch Load of Communications program will populate leading zeros, when appropriate; this is not true for dates) Use of upper or lower case letters in fields are allowed (fields that require upper case will be converted by the program)
File naming standards	File names should be relevant to the business process. Do not use a date to name your file.

Continue to Batch Load of Communications Process - 2. Upload the Communications Input File to the M-Pathways System.