none
SELECT SQL QUERY FOR SELECTING FROM EXCEL SHEET USING WHERE CLAUSE RRS feed

  • Question

  • Here is my code.... return null data table

    string chitty = "MTP/HG2";

     DataTable dtdate = con.select("Select FirstInstDate from [Sheet3$] where SeriesNo = "+chitty+" ");

                
    • Edited by MAROON 5 Wednesday, December 4, 2013 11:51 AM
    • Moved by Allen Li - MSFT Thursday, December 5, 2013 9:10 AM ADO.NET provider
    Wednesday, December 4, 2013 11:50 AM

Answers

  • Hello,

    According to your description, you want to query an excel file. For this, we usually use the OleDB way.

    I made an example and please see it below:

    DataTable dt = new DataTable();
    
                string filePath = "E:\\BMX\\Lab\\TestLab\\WindowsFormsApplication1\\ADONET\\DataSource.xlsx";
    
    
                string fileExt = System.IO.Path.GetExtension(filePath);
    
                string fileName = System.IO.Path.GetFileName(filePath);
    
    
                OleDbConnection objOleDbCon = null;
    
                OleDbCommand objOleDbCmd = new OleDbCommand();
    
                OleDbDataAdapter objOleDbDataAdapter = new OleDbDataAdapter();
    
    
                string xConnStr = "";
    
    
                if (fileExt.ToLower() == ".xls")
    
                {
    
    
                    xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
    
                }
    
                else if (fileExt.ToLower() == ".xlsx")
    
                {
    
                    xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
    
                }
    
    
                objOleDbCon = new OleDbConnection(xConnStr);
    
                objOleDbCon.Open();
    
                try
    
                {
    
                    objOleDbCmd = new OleDbCommand("Select * FROM  [Course$] where CourseID=1", objOleDbCon);
    
                    objOleDbDataAdapter = new OleDbDataAdapter(objOleDbCmd);
    
                    objOleDbDataAdapter.Fill(dt);
    
                }
    
                catch (Exception ex)
    
                {
    
                    throw ex;
    
                }
    
                finally { objOleDbCon.Close(); }
    
    
                return dt;
    

    If I misunderstand you, could you please share more information regarding this issue and what type is the ‘con’?

    With these information, we could help you better.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 5, 2013 9:35 AM
    Moderator
  • I don't know what "con" represents in your example but if "chitty" is a string then it needs to be enclosed within single quotes:

    DataTable dtdate = con.select("Select FirstInstDate from [Sheet3$] where SeriesNo = '"+chitty+"'");
    I would also recommend implementing parameters in your data object so you don't have to be concerned with syntax like the above.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, December 5, 2013 1:02 PM

All replies

  • Hi MAROON,

    Since this issue is related to ADO.NET query, I will move this thread to ADO.NET provider forum. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Best Regards,
    Allen Li


    Allen Li
    TechNet Community Support

    Thursday, December 5, 2013 9:12 AM
  • Hello,

    According to your description, you want to query an excel file. For this, we usually use the OleDB way.

    I made an example and please see it below:

    DataTable dt = new DataTable();
    
                string filePath = "E:\\BMX\\Lab\\TestLab\\WindowsFormsApplication1\\ADONET\\DataSource.xlsx";
    
    
                string fileExt = System.IO.Path.GetExtension(filePath);
    
                string fileName = System.IO.Path.GetFileName(filePath);
    
    
                OleDbConnection objOleDbCon = null;
    
                OleDbCommand objOleDbCmd = new OleDbCommand();
    
                OleDbDataAdapter objOleDbDataAdapter = new OleDbDataAdapter();
    
    
                string xConnStr = "";
    
    
                if (fileExt.ToLower() == ".xls")
    
                {
    
    
                    xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
    
                }
    
                else if (fileExt.ToLower() == ".xlsx")
    
                {
    
                    xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
    
                }
    
    
                objOleDbCon = new OleDbConnection(xConnStr);
    
                objOleDbCon.Open();
    
                try
    
                {
    
                    objOleDbCmd = new OleDbCommand("Select * FROM  [Course$] where CourseID=1", objOleDbCon);
    
                    objOleDbDataAdapter = new OleDbDataAdapter(objOleDbCmd);
    
                    objOleDbDataAdapter.Fill(dt);
    
                }
    
                catch (Exception ex)
    
                {
    
                    throw ex;
    
                }
    
                finally { objOleDbCon.Close(); }
    
    
                return dt;
    

    If I misunderstand you, could you please share more information regarding this issue and what type is the ‘con’?

    With these information, we could help you better.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 5, 2013 9:35 AM
    Moderator
  • I don't know what "con" represents in your example but if "chitty" is a string then it needs to be enclosed within single quotes:

    DataTable dtdate = con.select("Select FirstInstDate from [Sheet3$] where SeriesNo = '"+chitty+"'");
    I would also recommend implementing parameters in your data object so you don't have to be concerned with syntax like the above.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, December 5, 2013 1:02 PM