split string into datatable

Question

I got response from flight reservation web site. I saved this response into one string variable then I split this string into chars then I am trying to save this output on data table but problem is that data is not well formatted.

I got this o/p:-

Needed o/p like this:-

Note:- I want to add J8 C7 D1 I0 S0 Y9 B9 to M9 H9 Q9 K0 L0 U0 T0 E0 then resultant string store in separate column. this is my project requirement for customer understanding.

code is :-

 protected void Button1_Click(object sender, EventArgs e)
 {
   string request = @" AN29MARLASJFK
        ** AMADEUS AVAILABILITY - AN ** JFK JOHN F KENNEDY.USNY       58 FR 29MAR 0000
        ** HI NEWLY RENOV HOL INN SOHO IS CENTRALLY LOC IN HEART OF
        ** NYC MOST VIBRANT NGHBORHD*SUBWAY 1 BLK TO BK >HAHINYC19B
        1   DL1348  J8 C7 D1 I0 S0 Y9 B9 /LAS 1 JFK 3  705A    258P  E0/738 9     4:53
                    M9 H9 Q9 K0 L0 U0 T0 E0
        2AA:BA8666  F7 AL J7 CL DL IL Y7 /LAS 1 JFK 8  820A    420P  E0/738  TR   5:00
                    B7 H9 K9 M9
        3   AA 264  F7 A0 P0 Y7 B7 H7 K7 /LAS 1 JFK 8  820A    420P  E0.738 9     5:00
                    M7 L3 W0 S0 V0 G0 N0 Q0 O0
        4   DL 092  J9 C9 D0 I0 S0 Y9 B9 /LAS 1 JFK 3 1145A    746P  E0/738 8     5:01
                    M9 H9 Q9 K0 L0 U0 T0 E0
        5   B6 194  Y7 E7 K7 H7 Q7 B0 L0 /LAS 3 JFK 5  136P    930P  E0.320 N     4:54
                    V0 R0 W0 M0 Z0 O0 U0 S0 P0
        6  :HA2500  F4 J4 P0 A4 Y4 W4 Q4  LAS 3 JFK 4  230P   1029P  E0.320 7TR   4:59
                    B4 N0 M0
        7   VX 260  J7 C5 D2 W7 Q3 Z2 Y7 /LAS 3 JFK 4  230P   1029P  E0.320 7     4:59
                    V7 B7 H7 E1 U0 M0 I0 L0 S0 N0
        8   DL1728  J9 C9 D9 I8 S4 Y9 B9 /LAS 1 JFK 3  445P   1239A+1E0/73H 8     4:54
                    M9 H9 Q2 K0 L0 U0 T0 E0
        9   DL 322  J9 C9 D9 I9 S9 Y9 B9 /LAS 1 JFK 3  950P    541A+1E0/73H 9     4:51
                    M9 H9 Q9 K1 L0 U0 T0 E0
        >";

    DataTable ds = new DataTable();
    DataRow dr=null;

    ds.Columns.Add("A", typeof(string));
    ds.Columns.Add("B", typeof(string));
    ds.Columns.Add("C", typeof(string));
    ds.Columns.Add("D", typeof(string));
    ds.Columns.Add("E", typeof(string));
    ds.Columns.Add("F", typeof(string));
    ds.Columns.Add("G", typeof(string));
    ds.Columns.Add("H", typeof(string));
    ds.Columns.Add("I", typeof(string));
    ds.Columns.Add("J", typeof(string));
    ds.Columns.Add("K", typeof(string));
    ds.Columns.Add("L", typeof(string));


    int startindex = request.IndexOf(" 1  ");

    request = request.Substring(startindex - 1);
    var respArray = request.Split(new char[] { '\t', '\r', '\n' });

    foreach (string value in respArray)
    {
        dr = ds.NewRow();

      var tokens = value.Split(new[] { @"   " }, StringSplitOptions.RemoveEmptyEntries);

        ds.Rows.Add().ItemArray = tokens.Where((t, i) => i != 12).ToArray();
    }

}

Answer 1

Here you should use string operation and regular expression. I solved your problem. Please run my code and let me know if you have any doubt.

    int startindex = request.IndexOf(" 1  ");
    request = request.Substring(startindex - 1); //Remove the special character line from the input

    string subs;

    while (request.Trim() != ">") // Here we create the loop for execute whole string.
    {           
            int r1 = request.IndexOf("\r", 3);
            subs = request.Substring(0, r1); // Here we take the first line of the input.
            dr = dt.NewRow(); // Here we create the new row for each line.

            MatchCollection m1 = Regex.Matches(subs, @"\w{3}\s\d"); // Take the LAS 1 character from the line for merging the seat availability classes value.
            string origin = m1[0].Value; // Here we assign the origin city name to string variable.
            string destination = m1[1].Value; // Here we assign the destination city name to string variable.
            int end = subs.IndexOf(origin);
            int start = end - 1;

            dr["Origin"] = origin; // Here we bind the origin city name to data column.
            dr["Destination"] = destination; // Here we bind the destination city name to data column.

            request = request.Substring(subs.Length); // Here requset variable contain the substring after the took of first line for processing.
            r1 = request.IndexOf("\r\n", 3); // Here we are taking the index of second line.

            string replace = request.Substring(3, r1).TrimStart();

            string result = subs.Substring(0, start) + replace + subs.Substring(end); // here we merge the seat availability classes value.

            Regex regex = new Regex(@"(\s(\w\w\s){2,})"); // Here we create the regular expression for seat availability classes. 
            Match match1 = regex.Match(result);


            dr["COS"] = match1.ToString(); // Here we bind the seat availability classese to data column.
            dr["Segment"] = result.Substring(0, result.IndexOf(match1.ToString()) - 2); // Here we bind the Segments to data column.


            subs = result;
            start = subs.IndexOf(destination);
            dr["Details"] = subs.Substring(start + 5); // Here we bind the further(timing details) details of flight to data column.
            dt.Rows.Add(dr); // Here we are binding whole rows to data table. 

            request = request.Substring(r1); // requset variable contains the remaining substring.

    }

    GridView1.DataSource = dt;
    GridView1.DataBind(); // Here we are binding the whole data table to GridView.
    dt.Dispose(); // Here we dispose the datatable and free the memory.