locked
formated as text or preceeded by apostrophe RRS feed

  • Question

  • When I import data from excel using  gridview , the excel cell, having the following type of data, is not importing to Gridview

    if the excel  cell  like ' formated as text or preceeded by apostrophe'   The data loook like

    '456

    '567

    The above type of data is not coming to gridview. Any idea to insert into database  having data with apostrophe in front of the data.

    My code is as follows

                if (System.IO.File.Exists(strfile))
                {
                    DataSet objDataset1 = new DataSet();
                    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                           "Data Source=" + strfile + ";Extended Properties=Excel 8.0;";
                    string query = String.Format("select * from [{0}$]", comboBox1.SelectedItem);
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connString);
                    DataSet dataSet = new DataSet();
                    dataAdapter.Fill(dataSet);
                    dataGridView1.DataSource = dataSet.Tables[0];
                                }
    }

    With Thanks

    POl


    polachan

    Tuesday, April 3, 2012 3:19 PM

Answers

  • Actually, now that I think about it, a better way to go might be to add a computed column to the DataTable and just bind the display column to that:

    dataAdatper.Fill(dataSet);

    DataColumn dispCol = new DataColumn("DisplayNum"); dispCol.Expression = "Substring(BadCol,2,LEN(BadCol))" //Substring expression to skip the apostrophe dataSet.Tables[0].Columns.Add(dispCol);

    dataGridView1.DataSource = dataSet.Tables[0];

    You can then bind the column of the data grid the computed column to display this number.


    • Edited by InLocoAbsentia Thursday, April 5, 2012 1:46 PM
    • Marked as answer by Bob Shen Monday, April 16, 2012 3:19 AM
    Tuesday, April 3, 2012 5:17 PM

All replies

  • If the column you are sticking the data into is set up as an integer column, it won't work.  You'll have to write something to remove the apostrophe.  You can use DataGridView.RowValidating to parse the correct value, if this is the case.
    Tuesday, April 3, 2012 3:29 PM
  • Please can u give a sample code for

    DataGridView.RowValidating to parse the correct value

    With Many thanks

    Pol


    polachan

    Tuesday, April 3, 2012 4:30 PM
  • Actually, now that I think about it, a better way to go might be to add a computed column to the DataTable and just bind the display column to that:

    dataAdatper.Fill(dataSet);

    DataColumn dispCol = new DataColumn("DisplayNum"); dispCol.Expression = "Substring(BadCol,2,LEN(BadCol))" //Substring expression to skip the apostrophe dataSet.Tables[0].Columns.Add(dispCol);

    dataGridView1.DataSource = dataSet.Tables[0];

    You can then bind the column of the data grid the computed column to display this number.


    • Edited by InLocoAbsentia Thursday, April 5, 2012 1:46 PM
    • Marked as answer by Bob Shen Monday, April 16, 2012 3:19 AM
    Tuesday, April 3, 2012 5:17 PM
  • Please it would be very helpful to me if u can give me a sample code ,   according to my example.

    With thanks

    Pol


    polachan

    Thursday, April 5, 2012 8:47 AM
  • I've edited the example I gave to show where you should put the code to try it out.  It should come in between the last and second-to-last line of your original post.
    Thursday, April 5, 2012 1:47 PM