Reading Excel with C#

Question

I am trying to read an Excel file with C# and I keep getting this error: oledbexception cannot update. database or object is read-only on the line. Any ideas?

        string connStr = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
        SqlConnection conn = new SqlConnection(connStr);
        //file upload path

        string path = FileUpload1.PostedFile.FileName;
        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileUpload1.PostedFile.FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd = new OleDbCommand("Select [Coupon], [First Name], [Last Name] from [Sheet1$]",excelConnection);
        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(conn);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "CPC_Coupons";
        sqlBulk.WriteToServer(dReader);
        excelConnection.Close();

Thanks!

Answer 1

you need to change connectionstring like this, from ConnectionStrings

as there is article in MS Support site for this ADO.net with Excel

 string FileName = GettheFileName; 
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileName+";Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

from MS support site ADO.net with Excel

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting.

PS : If you do not have column headings, you need to specify HDR=No in the connection string

Answer 2

Here's my method, works with sheets created in Excel 2007.

public static DataTable ReadExcel(string path)
{
    //create a DataTable to hold the query results
    DataTable dTable = new DataTable();

    try
    {
        if (!File.Exists(path))
            return null;

        //create the "database" connection string 
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

        //create the database query
        string query = "SELECT * FROM [Sheet1$]" ;

        //create an OleDbDataAdapter to execute the query
        OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
        //fill the DataTable
        dAdapter.Fill(dTable);
        dAdapter.Dispose();
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
    }

    return dTable;
}