Importing excel data to SQL

Question

I am using ASP.net to make some application, but I have a holdup, problem is that user should import excel file (pattern of file is always the same), but in excel file there is a cell with bunch of names, that looks like this

 ID         Names
  1       Mick Jagger
          Elton John
  2       James Hetfield
          Axl Rose

And so on, so i have 2 or more names per cell ... So far, I imported that excel file to DataSet

sqlCMD.Connection.Open();
            using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
            {
                using (OleDbCommand cmd = new OleDbCommand("Select " + CompanyID + ", * 
                                                            From [Sheet1$]"
                 , excelConnection))
                {
                    OleDbDataAdapter daSheet = 
                    new OleDbDataAdapter(cmd.CommandText, excelConnection);

                    DataTable table;
                    table = new DataTable();
                    daSheet.Fill(table);
                    PreparedData.Tables.Add(table);
                }
            }

So after successful import, I have list of objects imported into data set, which is awesome, but this 2 or more names per cell is giving me trouble, as in object value it's written like this "Mick Jagger \n Elton John", so I am trying to replace it and add it back to data set (I tried making new data set but that didn't work out good)

        int Rows = PreparedData.Tables[0].Rows.Count;
        int Columns = PreparedData.Tables[0].Columns.Count;

        for (int i = 0; i < Rows; i++)
        {
            for (int s = 0; s < Columns; s++)
            {
                if (s == 4 || s == 5 || s == 6 || s == 7)
                {
                    object ValueToReplace;
                    object Replaced;

                    ValueToReplace = PreparedData.Tables[0].Rows[i].ItemArray[s];
                    Replaced = ValueToReplace.ToString().Replace("\n", ";");

                    PreparedData.Tables[0].Rows[i].ItemArray[s] = Replaced;
                }
            }
        }

Point of this is that at the end I want to import that modified data into my SQL table using (which is working)

     SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlCMD.Connection);
     bulkcopy.DestinationTableName = "Excel_Import";
        try
        {
            bulkcopy.WriteToServer(FixedData);
        }
        catch (Exception e)
        {

        }

So can you help me and advice me how I could modify that

PreparedData.Tables[0].Rows[i].ItemArray[s] = Replaced;

Answer 1

When you reference the ItemArray property the NET Framework builds a copy of the data in the current row. If you try to use it to set new values you set the values on the copy not on the original row data.

This is the source code of the ItemArray property from the Reference souce

    public object[] ItemArray {
        get {
            int record = GetDefaultRecord();
            _table.recordManager.VerifyRecord(record, this);
            object[] values = new object[_columns.Count];
            for (int i = 0; i < values.Length; i++) {
                DataColumn column = _columns[i];
                VerifyValueFromStorage(column, DataRowVersion.Default, column[record]);
                values[i] = column[record];
            }
            return values;
        }

As you can see, every time you reference the ItemArray property a new copy of an object array is created and filled with data. Now, also if you change an entry in this array you are not changing the internal data maintained by the DataRow class. Your changes are simply unknown to the DataRow.

However the solution is really simple, just use

 ValueToReplace = PreparedData.Tables[0].Rows[i][s].ToString();
 PreparedData.Tables[0].Rows[i][s] = ValueToReplace.Replace("\n", ";");