Sub Read_text_file()  
  Dim strWholeFile As String
     
  Dim ADOStream
  Set ADOStream = CreateObject("ADODB.Stream")
     
  With ADOStream
    .Charset = "utf-8"
    .Mode = 3
    .Type = 1
    .Open
    .LoadFromFile ThisWorkbook.Path & "\SEU_discharge_July.csv"
    .Position = 0
    .Type = 2
    strWholeFile = .ReadText
    .Close
  End With
  Set ADOStream = Nothing
arr = Split(strWholeFile, Chr(10))
End Sub

Sub Palantir()
    Dim s$, r As Range
    Application.ScreenUpdating = False

    Set r = ActiveCell
    s = r.Offset(1, 0)
Line1:
    Do
        r = r & " " & s
        r.Offset(1, 0).Delete Shift:=xlUp
        DoEvents: DoEvents
        s = r.Offset(1, 0)
        If s = "" Then Exit Sub
    Loop Until _
         InStr(1, s, ",AFR/") <> 0 Or InStr(1, s, ",AME/") <> 0 Or InStr(1, s, ",AMR/") <> 0 Or _
         InStr(1, s, ",ANZ/") <> 0 Or InStr(1, s, ",AUS/") <> 0 Or InStr(1, s, ",CAM/") <> 0 Or _
         InStr(1, s, ",EAF/") <> 0 Or InStr(1, s, ",EME/") <> 0 Or InStr(1, s, ",ESA/") <> 0 Or _
         InStr(1, s, ",EUR/") <> 0 Or InStr(1, s, ",IOI/") <> 0 Or InStr(1, s, ",MEA/") <> 0 Or _
         InStr(1, s, ",MED/") <> 0 Or InStr(1, s, ",NAM/") <> 0 Or InStr(1, s, ",NEM/") <> 0 Or _
         InStr(1, s, ",NEU/") <> 0 Or InStr(1, s, ",NEZ/") <> 0 Or InStr(1, s, ",OCE/") <> 0 Or _
         InStr(1, s, ",SAF/") <> 0 Or InStr(1, s, ",SAM/") <> 0 Or InStr(1, s, ",SEM/") <> 0 Or _
         InStr(1, s, ",WAF/") <> 0 Or InStr(1, s, ",WME/") <> 0 Or InStr(1, s, ",WSA/") <> 0 Or _
         InStr(1, s, ",FEA/") <> 0

    Set r = r.Offset(1, 0)
    GoTo Line1


End Sub

shipment_version_instance_id,trade_lane_code,origin_departure_time_utc_expected_timestampInUTC,origin_departure_time_utc_expected_originalTimezone,origin_loc,n_operational_route_points,operational_route_transport_sequence,destination_arrival_time_utc_expected_timestampInUTC,destination_arrival_time_utc_expected_originalTimezone,destination_loc,booked_ffes,trade_code,route_code,route_direction,shipment_ffes,booking_number,is_hazardous,shipment_rate,shipment_yield,fk_scv_org_detail,name,parent_name,parent_code,origin_cluster,destination_cluster,transport_doc_number,first_load_port,last_discharge_port,fk_shipment_version,route_update_reasons,route_update_categories,departure_time_utc_expected,arrival_time_utc_expected,rkst_carrier_code,vessel,voyage,transport_mode,start_point_sequence,start_loc,start_loc_lopsc_cd,end_loc,end_loc_lopsc_cd,service,next_leg_transport_mode,next_leg_vessel,next_leg_voyage,next_leg_etd,next_leg_start_loc,next_leg_end_loc,next_marine_leg_vessel,next_marine_leg_voyage,next_marine_leg_vessel_etd,next_marine_leg_start_loc,next_marine_leg_end_loc,sob_timestampInUTC,sob_originalTimezone,booked_by_party_name,booked_by_party_address,booked_by_party_customer_code,shipper_party_name,shipper_party_address,shipper_party_customer_code,consignee_party_name,consignee_party_address,consignee_party_customer_code,notify_party_name,notify_party_address,notify_party_customer_code,operator_name,imo_label,imo_subrisks,proper_shipping_name,technical_name,packing_group,flash_point,shipment_cargo_description,eqpno,equipment_assignment_instance_id,assigned_container_type_rkem,assigned_ffe,container_type_rkem,container_group,is_oog,stuffed_commodity_name,prorated_ffe,sum_of_stuffing_package_count,container_gross_weight,temperature_lo,temperature_hi,humidity_req,customer_specified_hs_code,package_style_alt_texts,over_height_cm,over_len_cm,over_len_read_cm,over_width_port_cm,over_width_stbd_cm,displacement,displacement_unit,cosize,eqstype,eqouthgu,move,actloc,eqowntp,opr,mlseal,unnos,customer_priority,booked_commitment,co2_reading,o2_reading,co2_setpoint,o2_setpoint,row_number