Family The excel sheet has been converted to a csv file. You are to develop a Pe

Question

Family

The excel sheet has been converted to a csv file. You are to develop a Perl 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.

EffectiveDate Status EmployeeID ClientID MemberFirstName MemberMiddleName MemberLastName MemberSSN DOB Month DOB_Day DOB_Year Address1 Address2 City State ZipCode Area_Code Home_Phone Email DeductionMethod CustomerDefined Relationship Primary Family_ID UniqueID Plan_Type PID 36220 Add 1.23E+08 176456 Taylor Justin Jeffries 320-22-3145 4 23 1990 123 E Test Drive Milwaukee WI 53005 262 1231230 TaylorJustin@mail.com Employer Payroll Deduction P Y 998754 667 Individual PA 36526 Drop 3.25E+08 441234 Chad Wilson Smith 344-11-5413 5 22 1980 540 E Mainest St San Diego CA 92001 0 6.46E+09 ChadWilson@mail.com Employer Payroll Deduction P Y F777 665 Family PAP 36525 Change 3.01E+08 908765 Alex Arturo Coleson 432-33-3434 2 13 1971 5111 W Arboles Drive Los Angeles CA 92221 949 9110911 AlexArturo@yahoo.com Employer Sponsored D N F777 654 Family PA 37623 Drop 5.43E+08 784332 Alexandra Marie Samson 655-32-0999 1 6 1963 6800 Main St Santa Barbara CA 92018 0 1.95E+10 AlexandraMarie@aol.com Employer Sponsored D Y F778 332 Family PA 40858 Drop 3.22E+08 301001 Kyle Robert Walker 765-12-5656 3 5 1967 77123 W Faraway Drive Newport Beach CA 92312 858 7896756 KyleRobert@eathlink.net Employer Payroll Deduction P Y F778 333 Family PA 43050 Add 1 111111 Thomas Edward Brady 123-45-6789 10 10 1977 1 Patriots Place Foxboro MA 2035 619 6.19E+09 tbrady@mail.com Employer Payroll Deduction P Y 1 1 Individual 43050 Add 2 222222 Kennedy S Nou 213-53-8976 12 15 1994 2007 Fruit Tree Ln. Queen Crek AZ 85142 623 6.23E+09 knou1@asu.edu Employer Payroll Deduction S N 665462 665462 Individual 42370 Add P123 123456 Austin Bradley Mork 1.23E+08 2 1 1994 505 W BASELINE RD 2003 Tempe AZ 85283 0 1.6E+10 amork1@asu.edu Employer Payroll Deduction P Y 198721 198721 Individual PAP 42401 Drop P124 111111 John Wayne Doe 9.88E+08 7 24 1980 1234 FAKE LANE RD Phoenix AZ 85254 602 4023214 johnwaynedoe@mail.com Employer Sponsored P Y 987111 987111 Individual PA 42430 Add P125 222222 Alice Ann Girl 4.57E+08 9 12 1990 6732 LAKESIDE RD Tucson AZ 85641 0 1.48E+10 aliceanngirl@mail.com Employer Payroll Deduction P Y 222321 222321 Individual PA 42828 Add P126 333333 Bob Chris Guy 3.22E+08 1 30 1974 1095 MOUNTAIN RD Tempe AZ 85283 602 7915402 bobchrisguy@mail.com Employer Payroll Deduction P Y F123 L111 Family PAP 42828 Change P127 444444 Amber Blah Guy 6.94E+08 12 5 1978 1095 MOUNTAIN RD Tempe AZ 85283 602 4715897 amberblahguy@mail.com Employer Sponsored S N F123 L112 Family PAP 43050 Change 3 333333 Luke Skywalker 978-65-4321 11 31 1951 456 Tatooine Place Tatooine AK 2121 234 2.35E+09 lskywalk@mail.com Employer Payroll Deduction C Y 3 3 Family 43050 Drop 4 444444 Peter Parker 798-56-2143 10 14 1980 221 Baker St New York City NY 45678 978 9.78E+09 pparker@mail.com Employer sponsored D N 4 4 Family 43050 Drop 5 555555 Bartholomey Henry Allen 456-12-9783 12 12 1975 1255 Random St. Central City KA 91023 455 4.56E+09 ballen@mail.com Employer sponsored P Y 5 5

Family

Explanation / Answer

NAME VERSION SYNOPSIS DESCRIPTION QUICK 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_utf16le_string($row, $column, $string, $format) keep_leading_zeros() write_blank($row, $column, $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, $url, $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) set_column($first_col, $last_col, $width, $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, $top_row, $left_col) merge_range($first_row, $first_col, $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, $last_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_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_fg_color() set_border() set_border_color() copy($format) UNICODE IN EXCEL COLOURS IN EXCEL DATES AND TIME IN EXCEL An Excel date/time is a number plus a format Spreadsheet::WriteExcel doesn't 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, { parameter => 'value', ... }) validate criteria value | minimum | source maximum ignore_blank dropdown input_title input_message show_input error_title error_message error_type show_error

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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