DSR powershell CSV data extractor

DSRExtractor.ps1

[CmdletBinding()]
param(
  [Parameter(Mandatory=$true)]
  [String]$csvPath
)

#HACK: DSR's CSV export has redundant headers by default. This overrides them.
$fixedHeader = 'record_number','project_id','flag','process1_completed','process1_processing','recheck_date','expire_date','dt_added','dt_archived','attempt_count','ordered','warning_count','error_count','email_sent','service_flag','Site_Name','Site_Id','address1','address2','city','state','zipcode','country','service_phone','contact_name','Site_Manager_Phone','contact_phone_alt','contact_email','contact_name2','District Manager Phone','contact_email2','email','id','dt_order_complete','approved','archived','selected_service','decline_description','more_phones','alarm_system','add_site_nidloc','add_site_installtime','add_site_wiring','add_site_dwellingtype','date','order_id','approver_name','approver_email','changed_values','dt_ordered','dt_approved','dt_declined','request_dial','result_check_timed_out','network_userid','branch_number','branch_manager_name','branch_manager_phone','branch_manager_email','disclaimer_response','dt_service_selected','use_existing_service','presented_service','last4ssn','clone_date','scheduled_deployment_date','actual_deployment_timestamp','Category','circuit_type','group_name','group2_name','userid','contact_name3','contact_phone3','contact_email3','phone_service_carrier','franchise','deployment_group_name','Location Priority','contact_phone4','Unused','record_number2','phase','status','substatus','attention_required','existing_bb_connection','dwelling_type','nid_location','site_alarm','inside_wiring','preferred_service','service_speed','ip_block_size','ip_address_start','ip_address_end','ip_address_router','ip_address_gateway','ip_subnet_mask','ip_dns_primary','ip_dns_secondary','order_number_clec','date_foc_ilec','date_foc_ilec_time','date_line_verified','date_foc_clec','date_foc_clec_time','jeopardy','date_clec_complete','date_shipped','shipping_tracking_number','date_last_contact','date_record_updated','order_status','service_type','site_phone','enabled_dial','enabled_broadband','provisioned_by','provider_name','order_number_provider','date_foc_provider','date_foc_provider_time','date_provider_complete','billing_code','milestone_service_options_presented','milestone_service_selected','milestone_order_placed','milestone_service_activated','milestone_installation_complete','milestone_enabled','milestone_canceled','milestone_record_updated','milestone_address_changed','milestone_billing_conversion_requested','milestone_billing_conversion_completed','milestone_billing_conversion_failed','billing_conversion_status','billing_install_type','billing_install_cost','billing_monthly_cost','billing_monthly_cost_prorated','billing_equipment_cost','billing_equipment_cost_prorated','billing_equipment_cost_term','billing_taxes_onetime','billing_taxes_monthly','billing_taxes_monthly_prorated','billing_franchise_fee','billing_franchise_fee_prorated','billing_franchise_fee_term','billing_other_charge1_label','billing_other_charge1','billing_other_charge1_prorated','billing_other_charge1_term','billing_other_charge2_label','billing_other_charge2','billing_other_charge2_prorated','billing_other_charge2_term','billing_notes','billing_exceptions','billing_billed_to_dsr','billing_info_received','billing_info_complete','details_provider','details_provider_title','details_service_desc','details_service_speed','details_provider_phone','details_provider_tech_support_phone','details_allow_vpn','details_split_bill','details_commitment_period','details_early_cancelation_penalty','billing_dsr_provisioning_onetime','billing_dsr_helpdesk_support_monthly','billing_dsr_linksys_befsr11_onetime','billing_dsr_6inch_ethernet_cable_onetime','billing_dsr_end_user_enablement_onetime','billing_dsr_helpdesk_support_registration_onetime','details_end_user_provisioned','billing_fusf','billing_dsr_linksys_befsr41_onetime','billing_dsr_linksys_befsr81_onetime','billing_dsr_shipping_onetime','details_service_type','details_fusf','verification_count','clec_order_number','clec_circuit_number','appointment_set','appointment_date','appointment_time','appointment_assignment','appointment_comment','appointment_completed_tstamp','appointment_completed_by','id2','details_service_access_method','details_service_access_method_details','dev_conf_additional_comments','service_payment_info_collected','billing_account','billing_account_verified','Target Install Date','date_target_enablement','date_netgate_order_placed','netgate_order_number','att_site_id','billing_info_received_auth_user','billing_info_received_tstamp','billing_info_complete_auth_user','billing_info_complete_tstamp','billing_info_dsr_national_consolidation','billing_address_confirmation_status','billing_address_confirmation_who','billing_address_confirmation_tstamp','billing_ref_num','clone_reason','turned_away','Location Priority2','details_modem_make_model','details_modem_mac','date_follow_up','date_broadband_install','date_sos','appointment2_set','appointment2_date','appointment2_time','appointment2_assignment','appointment2_comment','appointment2_completed_tstamp','appointment2_completed_by','milestone_included_in_static_ip_report','constr_required','constr_cost_total','constr_cost','constr_waived_cost','constr_notified_date','constr_approval_date','constr_liability_notified_date','constr_liability_approval_date','constr_liability_approval_who','constr_liability_approval_dt','constr_liability_denial_date','constr_tentative_completion_date','constr_schedule_tracking','constr_schedule_variance_explanation','constr_completed_date','constr_liability_denial_who','constr_liability_denial_dt','constr_time_frame','constr_scope','selected_construction_options_id','service_upgrade_request','service_upgrade_request_details','service_upgrade_unavailable_details','service_effective_date_acknowledged','service_effective_date_by','service_effective_date','service_upgrade_requested_date','service_upgrade_request_to_provider_date','estimated_service_effective_date','service_upgrade_service_disruption','service_upgrade_service_disruption_length','service_upgrade_hardware_change','service_upgrade_dispatch_required','service_upgrade_ip_change_required','service_upgrade_billing_account_number_change_required','cloned_for_service_upgrade','service_upgrade_dispatch_date','service_upgrade_dispatch_time','service_upgrade_dispatch_comment','service_upgrade_order_status','billing_ts_id','first_name_as_unsigned','ATT Billing ID'

#HACK: DSR's CSV export arbitrarily splits lines sometimes. This tries to detect and repair
#This will need to be tweaked if reusing the script for other clients
$rawText = Get-Content $csvPath | select -skip 1
$rawLines = $rawText.Split([Environment]::NewLine)
#Cobbling it all back together...
For($i=0; $i -lt $rawLines.Length - 1; $i++){
    if($rawLines[$i] -eq ''){
        continue
    }
    if ($rawLines[$i] -notmatch '"BGL.*"$' ){
        $rawLines[$i + 1] = $rawLines[$i] + $rawLines[$i + 1]
        $rawLines[$i] = ''
    }
}
$fixedText = ($rawLines | ? {$_ -ne ''}) -Join [Environment]::NewLine

#EXHALE...
#Now that we fixed DSR's stupid output data, we can present it
$rawCsv = $fixedText | ConvertFrom-Csv -Header $fixedHeader
#Filter for useful records
$disconnectData = $rawCsv | ? {$_.status.contains('Disconnect')} | ? {$_.date_record_updated -match '2019-(1[0-2]|0[6-9])'}
#Eliminate reconnections to avoid including sites that switched service or went back to Broadband
$reconnectData = $rawCsv | ? {$_.status -notmatch 'Disconnect'} | ? {$_.date_record_updated -match '2019-(1[0-2]|0[6-9])'}
$finalData = $disconnectData | ? {$($reconnectData).Site_Id -notcontains $_.Site_Id}

#Sanitize site numbers and convert to integer
$finalData | % {$_.Site_Id = [int]$($_.Site_Id -replace "\D+")}
#Present as a table reverse ordered site
$finalData | Sort-Object -Property Site_Id | Format-Table -AutoSize -Property Site_Id #,status,date_record_updated

Whoops! Fixed a bad regex that dropped a lot of matches