Import File Formats

Import File Formats:

• Exit Import File Format - CRC
• Exit Import File Format - Banner
• Clearinghouse File Format

Customer and Account Data Import File Format - CRC

Back to Top

Customer and Account Data Import File Format – Banner

Back to Top

Customer and Account Data Import File Format - Delimited/Excel

Field Name	Type/Length	Example	Required
SSN	Alpha 9	223087246	Yes
SIDNUMBER	Alpha 12	T1234567890	Yes
FIRSTNAME	Alpha 60	Gary	Yes
LASTNAME	Alpha 60	Cage	Yes
CAMPUS	Alpha 2	KN	Yes
SCHOOL_CODE	Alpha 6	TSU	Yes
TRANSDATE	Date YYYYMMDD	20020128	Yes
AMOUNT	Alpha 12	500.00	Yes
FAOLOANNAME	Alpha 10	PERK10	Yes
ENROLLFROM	Date YYYYMMDD	20101101	Yes
ENROLLEND	Date YYYYMMDD	20111101	Yes
MIDDLENAME	Alpha 60	Joseph	No
SUFFIX	Alpha 3	Jr	No
MAIDENNAME	Alpha 60	Smith	No
DOB	Date YYYYMMDD	19700101	No
DRIVERLICENSE	Alpha 25	VA1234567890	No
SEX	Alpha 1	M	No
TERM	Alpha 6	199901	No
ADDRESS1	Alpha 75	451 River Road	No
ADDRESS2	Alpha 75	PO Box 1	No
CITY	Alpha 50	Herndon	No
COUNTRY	Alpha 30	USA	No
STATE	Alpha 3	VA	No
ZIP	Alpha 30	20171	No
DAYPHONE	Alpha 18	5555551212	No
DAYEXT	Alpha 10	123	No
EVEPHONE	Alpha 18	5555551212	No
EVEEXT	Alpha 10	123	No
BURSARLOANNUMBER	Alpha 12	ARDEBT	No
EST_GRAD_DATE	Date YYYYMMDD	20120101	No
DRIVERS_LICENSE_STATE	Alpha 2	NJ	No
PROM_NOTE	Alpha 6	A123456	No
ADDRESS_KIND	Alpha 16	Primary	No
Academic_Level	Alpha 1	1	No
DueDate	Date YYYYMMDD	20101115	No
Charge_amount	Alpha 12	50.00	No
INTERNET	Alpha 128	smith@aol.com	No
FOREIGNID	Alpha 15	BX12345	No
CELPHONE	Alpha 12	7725551212	No
FAX	Alpha 12	7725551212	No
SPOUSE_SSN	Alpha 15	123456789	No
SPOUSE_NAME	Alpha 50	John Smith	No
COMAKER_SSN	Alpha 15	123456789	No
COMAKER_DOB	Date YYYYMMDD	19750101	No
COMAKER_FIRSTNAME	Alpha 60	Jill	No
COMAKER_LASTNAME	Alpha 60	Smith	No
COMAKER_ADDRESS1	Alpha 75	123 University Blvd.	No
COMAKER_ADDRESS2	Alpha 75	PO Box 123	No
COMAKER_CITY	Alpha 50	Dallas	No
COMAKER_STATE	Alpha 3	TX	No
COMAKER_ZIP	Alpha 30	1234567890	No
COMAKER_COUNTRY	Alpha 30	USA	No
COMAKER_SUFFIX	Alpha 3	JR.	No
COMAKER_SEX	Alpha 1	M	No
COMAKER_DAYPHONE	Alpha 18	7725551212	No
COMAKER_DAYEXT	Alpha 10	123	No
COMAKER_EVEPHONE	Alpha 18	7725551212	No
COMAKER_EVEEXT	Alpha 10	123	No
COMAKER_CELLPHONE	Alpha 12	7725551212	No
COMAKER_EMAIL	Alpha 128	jsmit@gmail.com	No
COMAKER_FAX	Alpha 12	7725551212	No
INTEREST_AMOUNT	Alpha 12	500.00	No
COLL_FEE_AMOUNT	Alpha 12	500.00	No

Back to Top

Payment Import File Format

Using SSN

Back to Top

Using Customer ID

Collection Agency Payments Import File Format

Field	Length	Columns	Format	Description	Required?	Example
Debtor Account Number	12	1 - 12	Alphanumeric	Customer ID	Yes	W01234567890
Payment Date	10	13 - 22	MM/DD/YYYY	Date of Payment	Yes	05/07/2009
Amount Paid by Debtor to Agency	10	23 - 32	999999.99	Append negative sign (-) if needed; total length of field with sign (-) not to exceed 10 bytes	Yes	50.00 100.00-
Amount Paid by Debtor to School	10	33 – 42	999999.99	Append negative sign (-) if needed; total length of field with sign (-) not to exceed 10 bytes	Yes	50.00 100.00-
Agency Commission (Amount Due Agency)	10	43 – 52	999999.99	Append negative sign (-) if needed; total length of field with sign (-) not to exceed 10 bytes	Yes	50.00 100.00-
Amount Paid by Agency to School	10	53 – 62	999999.99	Append negative sign (-) if needed; total length of field with sign (-) not to exceed 10 bytes	Yes	50.00 100.00-
Account Type	10	63 - 72	Alphanumeric	Name of Account for which payment was collected	No	Perkins
Check/Reference Number	17	73 – 89	Alphanumeric	Check or reference number associated with this payment	No	12345 ABCDE Visa
Debtor Last Name	60	90 - 149	Alphanumeric	Debtor’s last name	No	Smith
Debtor First Name	60	150 – 209	Alphanumeric	Debtor’s first name	No	Mary
Debtor Middle Name	60	210 – 269	Alphanumeric	Debtor’s middle name	No	Jane
Remarks	50	270 – 319	Alphanumeric	Free form space provided to agency to pass on any additional information to the school.	No	Return to Client Payment to account Direct Payment Check By Phone Returned NSF Check Final Balance Received
Status	3	320 – 322	Alphanumeric	Free form; any value used by agency to identify account status	No	PIF, ACT, SIF, RTC
Agency Commission Rate	7	323 – 329	99.9999	Commission percentage	No	30.0000
Debtor’s Remaining Balance	10	330 - 339	999999.99	Append negative sign (-) if needed; total length of field with sign (-) not to exceed 10 bytes	No	50.00 100.00-

Back to Top

Exit Import File Format - CRC

Field Name	Length	Columns	Required?	Format/Comments
Social Security Number	9	1-9	Y	999999999
Last Name	20	10-29	N
First Name	20	30-49	N
Address Line 1	30	50-79	N
City	20	80-99	N
State	2	100-101	N
Zip	9	102-110	N
Parent Name	40	111-150	N
Parent Address Line 1	30	151-180	N
Parent Address Line 2	30	181-210	N
Parent City	20	211-230	N
Parent State	2	231-232	N
Parent Zip	9	233-241	N
Filler	10	242-251	N
Birth Date	9	252-257	N

Back to Top

Exit Import File Format - Banner

Field Name	Length	Columns	Format/Comments
Customer ID	9	1-9
Full Name	32	10-41	Last, First Middle
Status	1	42-42
Last Term	6	43-48
Hours	9	49-57
Grad Term	6	58-63
Grad Date	6	64-69	YYYYMM
Withdraw Code	2	70-71
Withdraw Date	6	72-77	YYYYMM
Alternate ID	9	78-86

Back to Top

Clearinghouse File Format

Field Name	Columns	Length	Description/Format
Header Record
Record Type	1-2	2	Value is A1
Member OE #	3-8	6	Member’s OE number.
Member Name	9-48	40	Name of Member.
Response Date	49-56	8	Database survey/response tape created date MMDDYYYY
Response Type	57-57	1	O = Overlay response A = Additions respons N = Notification (weekly response)
Filler	58-235	178	Blank
Detail Record
Record Type	1-2	2	Value is D1
SSN Change Flag	3-3	1	Y = SSN has changed since last report N = No change noted Z = Unable to determine
Student SSN	4-12	9	Numbers only, no hyphens included
Previous SSN	12-21	9	Provided if applicable
Name Change Flag	22-22	1	Y = Name has changed since last report N = No change noted Z = Unable to determine
First Name	23-42	20	First Name of Student
Middle Initial	43-43	1	Middle Initial of Student
Last Name	44-63	20	Last Name
Name Suffix	64-68	5	E.g. III, Jr, Sr
Permanent Address Change Flag	69-69	1	Y = change in perm address (street/city/state/zip/cntry) N = No change noted Z = Unable to determine
Street Line 1	70-99	30	Student’s permanent street address
Street Line 2	100-129	30	Continued address (e.g. Apt B)
City	130-149	20	Student’s permanent city
State	150-151	2	Student’s permanent state (FO if foreign country)
Zip	152-160	9	Student’s permanent zip (spaces if state = FO)
Country	161-175	15	Student’s permanent country (only if foreign)
Enrollment Status Change Flag	176-176	1	Y = change in enrollment status N = No change noted Z = Unable to determine
Enrollment Status	177-178	1	F or H
Status Start Date Change Flag	178-178	1	Y = change in status start date N = No change noted Z = Unable to determine
Status Start Date	179-186	8	Begin date of reported enrollment status
Flag: Status Start Date precedes CH	187-187	1	Y = Date may be misleading (status may have begun prior to school’s CH membership Start); N = Date Valid
School Change Flag	188-188	1	Y = change in school data from last report N = No change noted Z = Unable to determine
School Code	189-194	6	School code that reported status
School Branch	195-196	2	School branch (00 if N/A)
AGD Change Flag	197-109	1	Y = change in AGD since last report N = No change noted Z = Unable to determine
Anticipated Grad Date	198-205	8	Date student is expected to graduate, reported only if status = F, H, or A
Term Change Flag	206-206	1	Y = change in term dates from last report N = No change noted Z = Unable to determine
Term Begin Date	207-214	8	Start date of current academic term for program in which student is enrolled
Term End Date	215-222	8	End date of current academic term for program in which student is enrolled
Certification Date Change Flag			Y = change in date since last report N = No change noted Z = Unable to determine
Certification Date			Date school certified the reported status
Paper Certification Issued			Y = Paper certification from accompanies response N = Certification form not issued
Filler		3	Spaces
Trailer Record
Record Type	1-2	2	Value is T1
Number of “F”	3-8	6	Number of records with enrollment status = “F”
Number of “H”	9-14	6	Number of records with enrollment status = “H”
Number of “L”	15-20	6	Number of records with enrollment status = “L”
Number of “W”	21-26	6	Number of records with enrollment status = “W”
Number of “G”	27-32	6	Number of records with enrollment status = “G”
Number of “A”	33-38	6	Number of records with enrollment status = “A”
Number of “X”	39-44	6	Number of records with enrollment status = “X”
Number of “D”	45-50	6	Number of records with enrollment status = “D”
Number of “R”	51-56	6	Number of records with enrollment status = “R”
Number of “Z”	57-62	6	Number of records with enrollment status = “Z”
Number of Paper Certifications	63-68	6	Count of hardcopy certification forms issued to accompany notification to member
Total Records	69-76	8	Total number of physical records including all header, detail, and trailer records
Filler	77-235	159	Spaces

  

Back to Top

  

©Ellucian Campus Receivables Collector Help Center