Fetch data from Excel Sheet
-
Dear friends, I want to fetch data from Excel sheet sheet1 using ASP.NET with C# and store the data in SqlServer database table using BulkCopy method. I am able to fetch complete excelsheet data, but I want only some rows. I want sql statement to fetch the data. Excel sheet contains one Date column. I want to fetch data from only particular dated rows. I am writing the following query select * from [Sheet1$] where Date=21/04/10 It is giving me error datatype mismatch. Please, can anyone help me. It's very urgent. Pls.... Thanks, Dileep.
-
Dear friends, I want to fetch data from Excel sheet sheet1 using ASP.NET with C# and store the data in SqlServer database table using BulkCopy method. I am able to fetch complete excelsheet data, but I want only some rows. I want sql statement to fetch the data. Excel sheet contains one Date column. I want to fetch data from only particular dated rows. I am writing the following query select * from [Sheet1$] where Date=21/04/10 It is giving me error datatype mismatch. Please, can anyone help me. It's very urgent. Pls.... Thanks, Dileep.
Try the following query: select * from [Sheet1$] where Date='04/21/2010' Note that the date should be in mm/dd/yyyy format and within single quote(').
Anurag Gandhi.
http://www.gandhisoft.com
Life is a computer program and every one is the programmer of his own life. -
Try the following query: select * from [Sheet1$] where Date='04/21/2010' Note that the date should be in mm/dd/yyyy format and within single quote(').
Anurag Gandhi.
http://www.gandhisoft.com
Life is a computer program and every one is the programmer of his own life.Dear Anurag, I am still getting this following error. [System.Data.OleDb.OleDbException] = {"Data type mismatch in criteria expression."} I have written the following code. filename = path + "SS_EmpLoginTimings.xls"; string s = "provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=" + filename + ";Extended Properties=Excel 8.0;"; OleDbConnection con = new OleDbConnection(s); con.Open(); OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$] where Date='04/21/2010'", con); DataSet ds = new DataSet(); da.Fill(ds); DataTable dt = ds.Tables[0]; BulkCopyData(dt, "SS_EmpLoginTimings"); public bool BulkCopyData(DataTable dt, string tblName) { SqlBulkCopy bulk = new SqlBulkCopy(conn); bulk.DestinationTableName = tblName; conn.Open(); bulk.WriteToServer(dt); conn.Close(); return true; } Please help me. It's very urgent. I need to finish this work by today evening. Pls pls pls... Regards, Dileep.
-
Dear Anurag, I am still getting this following error. [System.Data.OleDb.OleDbException] = {"Data type mismatch in criteria expression."} I have written the following code. filename = path + "SS_EmpLoginTimings.xls"; string s = "provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=" + filename + ";Extended Properties=Excel 8.0;"; OleDbConnection con = new OleDbConnection(s); con.Open(); OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$] where Date='04/21/2010'", con); DataSet ds = new DataSet(); da.Fill(ds); DataTable dt = ds.Tables[0]; BulkCopyData(dt, "SS_EmpLoginTimings"); public bool BulkCopyData(DataTable dt, string tblName) { SqlBulkCopy bulk = new SqlBulkCopy(conn); bulk.DestinationTableName = tblName; conn.Open(); bulk.WriteToServer(dt); conn.Close(); return true; } Please help me. It's very urgent. I need to finish this work by today evening. Pls pls pls... Regards, Dileep.
Have you got da and ds the wrong way round in the da.fill(ds)? It's while since I coded anything like this so I could be totally wrong. Maybe try putting the criteria as Date = '21-Apr-2010'.
-
Dear friends, I want to fetch data from Excel sheet sheet1 using ASP.NET with C# and store the data in SqlServer database table using BulkCopy method. I am able to fetch complete excelsheet data, but I want only some rows. I want sql statement to fetch the data. Excel sheet contains one Date column. I want to fetch data from only particular dated rows. I am writing the following query select * from [Sheet1$] where Date=21/04/10 It is giving me error datatype mismatch. Please, can anyone help me. It's very urgent. Pls.... Thanks, Dileep.
http://www.experts-exchange.com/Programming/Languages/.NET/Q_25020438.html[^] Try that page - scroll down to the bottom and look for postings by sbsnewbie
-
Dear friends, I want to fetch data from Excel sheet sheet1 using ASP.NET with C# and store the data in SqlServer database table using BulkCopy method. I am able to fetch complete excelsheet data, but I want only some rows. I want sql statement to fetch the data. Excel sheet contains one Date column. I want to fetch data from only particular dated rows. I am writing the following query select * from [Sheet1$] where Date=21/04/10 It is giving me error datatype mismatch. Please, can anyone help me. It's very urgent. Pls.... Thanks, Dileep.
How consistant is the data in your spreadsheet? Do all of the rows have dates in the date column? The Excel database driver determines the datatype of each column by the values in the first 10 rows. If your first ten rows have nothing in the date column or have other text like N/A or -, then that column will not be considered a date and you can't query it like a date.
-
How consistant is the data in your spreadsheet? Do all of the rows have dates in the date column? The Excel database driver determines the datatype of each column by the values in the first 10 rows. If your first ten rows have nothing in the date column or have other text like N/A or -, then that column will not be considered a date and you can't query it like a date.
Dear friend, All the rows in that column are having date only. I don't know what's wrong. I have tried in many ways. I am getting the following error. [System.Data.OleDb.OleDbException] = {"Data type mismatch in criteria expression."} Since last day afternoon, I am struggling for this. I have written queries in all following possible ways. select * from [sheet1$] where LoginDate='21/04/10' select * from [sheet1$] where LoginDate='21/04/2010' select * from [sheet1$] where LoginDate='21-04-10' select * from [sheet1$] where LoginDate='21-04-2010' select * from [sheet1$] where LoginDate='21-Apr-10' select * from [sheet1$] where LoginDate='21-Apr-2010' select * from [sheet1$] where LoginDate='04/21/10' select * from [sheet1$] where LoginDate='04/21/2010' select * from [sheet1$] where LoginDate='04-21-10' select * from [sheet1$] where LoginDate='04-21-2010' select * from [sheet1$] where LoginDate='Apr-21-10' select * from [sheet1$] where LoginDate='Apr-21-2010' select * from [Sheet1$] where [LoginDate] = '"+DateTime.Parse("04-21-10").ToString()+"'" select * from [Sheet1$] where [LoginDate] = '"+DateTime.Parse("04-21-10")+"'" Still I am not getting. Can anyone, pls help me. It's very urgent. Regards, Dileep
-
How consistant is the data in your spreadsheet? Do all of the rows have dates in the date column? The Excel database driver determines the datatype of each column by the values in the first 10 rows. If your first ten rows have nothing in the date column or have other text like N/A or -, then that column will not be considered a date and you can't query it like a date.