This needs to be coded in Python

REQUIRED ACTIVITY

You are provided an enrollment file: IFT394_TO_IA_CAD_20170904.xlsx

Convert it to a .csv file.

The table below provided the expected format of each field in the file:

Field Name	Format	Purpose & Constraint
Effective Date	MM/DD/YYYY	Indicate the coverage effective date
Status	5 character max (eg Drop, Add, …)	Action indicator. If “Add”, member is added to enrollment; if “Change”, info about an enrolled member is updated; if “Drop”, member is cancelled
EmployeeID		Identified employee
ClientID	Up to 6 integer digits	Identified client
MemberFirstName
MemberMiddleName
MemberLastName
MemberSSN
DOB_Month	2 digits.	Month of birth
DOB_Day	2 digits	Day of birth
DOB_Year	4 digits	Year of birth
Address1
Address2		Not required
City
State
ZipCode
AreaCode	3 digit
HomePhone	7 digits
Email	Must include @ and .
Deduction Method	“Employer Payroll Deduction” or “Employer sponsored”
Customer-Defined		Not required
Relationship	1 char (P, S, C, D)	Primary, Spouse, Child, or Dependent
Primary	1 char (Y, N)	Designate primary enrollment
FamilyID		Shared value across family enrollments.
UniqueID		Uniquely identified each member
Plan_Type	“Individual” or “Family”	Designate the plan type of the member
ProductID	“PAP”, “PA”	Not required

You are to develop a Python script to pre-process the file so that:

1. The header names are written as in the table above, and in that order. For example, we must have “Effective Date” instead of “EffectiveDate”; “DOB_Year” instead of “DOB Year”; “Plan_Type” instead of “Plan Type”; or “ProductID” instead of “PID”.

2. The area code is exactly 3 digits and the home phone is exactly 7 digits. Analyze these two fields carefully to understand how you can design your script to capture issues such as +1 xxx xxxxxxx, or an area code with 3 digits, but a home phone with 10-digits still.

3. There cannot be a relationship D, S, or C that is Primary. If this occurs, your script must set the dependent as “N” (primary).

4. There cannot be a D, S, or C relationship without an identifying primary, or that is of plan type “Individual”. A primary and a dependent are linked by their FamilyID. If this occurs, your script report must include an alert to the user about it.

5. All zip codes must be 5 digits

6. Months of birth must be between 1 and 12; and day of birth must be between 1 and 31 depending on the month of birth. For example, you cannot have a month of birth a 2 (i.e. February) and a day of birth as 30.

7. Year of birth must be 4 digits.

8. SSN must be 9 digits; not other character such as “-“ or “.”

9. Finally, your script must rearrange the records so that Primaries are processed before the dependents. In other words, primaries must come first.

Submissions

Your script

A sample run

Page | 1