how to insert splitted string into different rows C#

Question

i am trying to insert to insert below data as my requirement. ex: my excel sheet like this:

  id    name  codes                    
  1       a    12
  2       b    13,14,15
  3       c    16-19

my requirement like this:

 id     name   codes
  1       a      12        
  2       b      13
  2       b      14
  2       b      15
  3       c      16
  3       c      17
  3       c      18                
  3       c      19

am using c#. can anyone help me.... thnx in advance.

my code:

for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
        {

            cmd = "insert into " + tablename + " values (";


            effective_date = VerifyDateTime(range.Cells[rCnt, 7].Value);
            destination = (string)(range.Cells[rCnt, 1] as Excel.Range).Value2;
            prefix = range.Cells[rCnt, 3].Value.ToString();
            codes = range.Cells[rCnt, 2].Value.ToString();
            level = range.Cells[rCnt, 5].Value.ToString();
            rate = range.Cells[rCnt, 4].Value.ToString();
            change = range.Cells[rCnt, 6].Value.ToString();
            company_id = cmbcompanyid.SelectedItem.ToString();


            string s = range.Cells[rCnt, 6].Value.ToString();
            List<string> l = new List<string>(s.Split(';', '-'));
            int le = 0;
            for (le = 0; le <= l.Count; le++)
            {




                change = l[le];


                cmd = cmd + "'" + destination + "','" + codes + "','" + prefix + "','" + rate + "','" + level + "','" + change + "','" + effective_date + "','" + company_id + "')";



                //       cmd = cmd + "'" + destination + "','" + codes + "','" + prefix + "','" + rate + "','" + level + "','" + change + "','" + effective_date + "','" + company_id + "')";



                cmd = ReplaceSpecialCharacters(cmd);

                MySqlCommand sqlCmd = new MySqlCommand(cmd, sqlCon);
                var i = sqlCmd.ExecuteNonQuery();

            }
        }

when am trying to insert data based on split insert statement executing like this " insert into quickcom values ('Aruba Mobile','297','0.1817','0.1817','No Change','56','1899-00-30','Quickcom')'Aruba Mobile','297','0.1817','0.1817','No Change','57','1899-00-30','Quickcom') " how to seperate this statement as multiple stmnts. — user2265801
– user2265801, Commented Apr 17, 2013 at 9:18

Hans Keﬆing · Accepted Answer · 2013-04-17 08:26:17Z

1

There is no built-in way to do this, but it's fairly easy to do yourself:

split the string on the , to get separate values
split the results on the - to get the range limits. One result = no range; two results = lower and upper limit, so emit all numbers inbetween.

The main method to use here is .Split().

The full implementation is left as an exercise to the reader :-)

EDIT
Note that you turn one original value into potentially a lot of new values. A single 'insert' is not enough to process one input line.

edited Apr 17, 2013 at 8:26

answered Apr 17, 2013 at 7:16

Hans Keﬆing

39.6k10 gold badges84 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2265801 Over a year ago

am getting error at max value intialising like " Input string was not in a correct format. ".

Hans Keﬆing Over a year ago

@user2265801 - then you need to inspect that max value, apparently it's not the number you think it should be.

Guffa · Accepted Answer · 2013-04-17 07:16:35Z

0

You would have to parse the string, something like this:

string[] items = value.Split(",");
foreach (string item in items) {
  if (item.Contains("-")) {
    string[] parts = item.Split("-");
    int min = Int32.Parse(parts[0]);
    int max = Int32.Parse(parts[1]);
    for (int i = min; i <= max; i++) {
      // add the value in i to the data
    }
  } else {
    // add the value in item to the data
  }
}

answered Apr 17, 2013 at 7:16

Guffa

703k111 gold badges760 silver badges1k bronze badges

2 Comments

user2265801 Over a year ago

am getting error at max value intialising like " Input string was not in a correct format. ".

Guffa Over a year ago

@user2265801: Then you have a type of value that isn't covered by your examples. Perhaps something like "42-", where the empty value after the dash would not be possible to parse as an integer.

Collectives™ on Stack Overflow

how to insert splitted string into different rows C#

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related