Hello, I'm trying to import xlsx to datagrid using oledb.
but i'm geting this: Syntax error (missing operator) in query expression.I've read other related posts but didn't helped
this is the code:

  private void button1_Click(object sender, EventArgs e)
        {

            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\alm1.xlsx" + @";Extended Properties=""Excel 12.0 Xml;HDR=YES;ImportMixedTypes=Text;TypeGuessRows=0""";
            OleDbCommand command = new OleDbCommand("SELECT TXT" + "FROM [2$]", conn);
            DataSet dstxt = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dstxt);
            dataGridView1.DataSource = dstxt.Tables[0];

can anyone help? I'm pretty sure it's a stupid mistake of me but can't figure it out.
thank you in advance.

Suppose, your problem is in ConnectionString. Your ConnectionString should be

conn.ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\alm1.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

To know more, go here, if you like.

thank you for your reply but it didn't work.
infact i thing my connectionstring is ok

it didn't work

Did it perform an exception as you get earlier?

Your SQL Query Statement is in wrong format.

"SELECT TXT" + "FROM [2$]"

What is "TXT"?
You tried to concatinate two strings. If "TXT is a Field Name or Column header, a space is needed in between "TXT" and "FROM".After concatination it will transform to "SELECT TXTFROM [2$]". This is a wrong SQL Statement. Your SQL Statement should be

Select * From [Sheet Name]

This text
"SELECT TXT" + "FROM (...)"
will resolve to
SELECT TXTFROM (...)"
As a result of that the FROM keyword will not be found
Should read
"SELECT TXT " + "FROM (...) "
I prefer to add a space to all substrings that are part of a SQL statement.

yep it was the space thing ..silly me.
Anyway thank you both for your replies helped alot :)

This article has been dead for over six months. Start a new discussion instead.