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.

Recommended Answers

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.

Jump to Post

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 …

Jump to Post

All 5 Replies

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 :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.