You are provided an enrollment file: IFT394_TO_IA_CAD_20170904.xlsx Convert it t

Question

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:

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”.

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.

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

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.

All zip codes must be 5 digits

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.

Year of birth must be 4 digits.

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

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

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

Explanation / Answer

-> NAME

-> VERSION

-> SYNPOSIS

-> DESCRIPTION

-> OUICK START

-> WORKBOOK METHODS

-> new()

-> add_worksheet($sheetname,$utf_16_be)

-> add_format(%properties)

-> add_chart(%properties)

-> add_chart_ext($chart_data,$chartname)

-> close()

-> compatibility_mode()

-> set_properties()

-> define_name()

-> set_tempdir()

-> set_custom_color($index,$red,$green,$blue)

-> sheets(0,1,....)

-> set_1904()

-> set_codepage($codepage)

-> WORKSHEET METHODS

-> cell notation

-> write($row,$column,$token,$format)

-> write_number($row,$column,$number,$format)

-> write_string($row,$column,$string,$format)

-> write_utf16be_string($row,$column,$string,$format)

-> write_utf16be_string($row,$column,$string,$format)

-> keep_leading_zeros()

-> write_blank($row,$colmun,$format)

-> write_row($row,$column,$array_ref,$format)

-> write_col($row,$column,$array_ref,$format)

-> write_date_time($row,$col,$date_string,$format)

-> write_url($row,$col,$label,$format)

-> write_url_range($row1,$col1,$row2,$col2,$url,$string,$format)

-> write_formula($row,$column,$formula,$format,$value)

-> store_formula($formula)

-> repeat_formula($row,$col,$formula,$format,($pattern =>$replace,...))

-> write_comment($row,$column,$string,...)

-> show_comments()

-> add_write_handler($re,$code_ref)

-> insert_image($row,$col,$filename,$X,$Y,$scale_X,$scale_Y)

-> insert_chart($row,$col,$chart,$X,$Y,$scale_X,$scale_Y)

-> embed_chart($row,$col,$filename,$X,$Y,$scale_X,$scale_Y)

-> data_validation()

-> get_name()

-> activate()

-> select()

-> hide()

-> set_first_sheet()

-> protect($password)

-> set_selection($first_row,$first_col,$last_row,$last_col)

-> set_row($row,$height,$format,$hidden,$level,$collapsed)

-> outline_settings($visible,$symbols_below,$symbols_right,$auto_style)

-> freeze_panes($row,$col,$top_row,$left_col)

-> split_panes($Y,$X,$stop_row,$left_col)

-> merge_range($first_row,$first_col,$last_row,$last_row,$last_col,$token,$format,$utf_16_be)

-> set_zoom($scale)

-> right_to_left()

-> hide_zero()

-> set_tab_color()

-> autofilter($first_row.$first_col,$first_row,$last_col)

-> filter_column($column,$expression)

-> PAGE SET-UP METHODS

-> set_landscape()

-> set_portrait()

-> set_page_view()

-> set_paper($index)

-> center_horizontally()

-> center_vertically()

-> set_margins($inches)

-> set_header($string,$margin)

-> set_footer()

-> repeat_rows($first_row,$last_row)

-> repeat_columns($first_col,$last_row,$last_col)

-> hide_gridlines($option)

-> print_row_col_headers()

-> print_area($first_row,$first_col,$last_row,$last_col)

-> print_across()

-> fit_to_pages($width,$height)

-> set_start_page($start_page)

-> set_print_scale($scale)

-> set_h_pagebreaks@breaks)

-> set_v_pagebreaks@breaks)

CELL FORMATTING

-> creating and using a format object

-> format methods and format properties

-> working with formats

FORMAT METHODS

-> set_format_properties(%properties)

-> set_font($fontname)

-> set_size()

-> set_color()

-> set_bold()

-> set_italic()

-> set_underline()

-> set_font_strikeout()

-> set_font_script()

-> set_font_outline()

-> set_font_shadow()

-> set_num_format()

-> set_locked()

-> set_hidden()

-> set_align()

-> set_center_across()

-> set_text_wrap()

-> set_rotation()

-> set_indent()

-> set_shrink()

-> set_text_justlast()

-> set_pattern()

-> set_bg_color()

-> set_border_color()

-> copy($format)

UNICODE IN EXCEL

COLOURS IN EXCEL

DATES AND TIME IN EXCEL

-> An excel date/time is anumber plus a format

-> spreadsheet::writeexcel doesnt automatically convert date/time strings

-> converting dates and times to an excel date or time

OUTLINES AND GROUPING IN EXCEL

DATA VALIDATION IN EXCEL

-> data_validation($row,$col,{prameter=>'value',....})

-> validate

-> criteria

-> value| minimum|source

-> maximum

-> ignore_blank

-> dropdown

-> input_title

-> input_message

-> show_input

-> error_tittle

-> error_type

-> show_error

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.