Home > Enterprise >  "Syntax error in FROM clause" Excel in c#
"Syntax error in FROM clause" Excel in c#

Time:10-10

i have an Excel file i want to raed it in c# using the OleDb like the following code:

    string sheetName = "sheet1";
    try
    {
        string stringConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=excelfile.xls;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;MAXSCANROWS=0';";
    
        OleDbConnection OleDbConnection_ = new OleDbConnection(stringConnection);
        OleDbCommand OleDbCommand_ = new OleDbCommand("select * from ["   sheetName   "$]; ", OleDbConnection_);
        OleDbConnection_.Open();
    
        DataTable DataTable_ = new DataTable();
        DataTable_.Load(OleDbCommand_.ExecuteReader(), LoadOption.OverwriteChanges);
    
        OleDbConnection_.Close();
    
    }
    catch (Exception)
    {
        throw;
    }

Everything is working fine, just when i change the SheetName Value to " topos .architectures. bureaux " like the name in the xls file that i have, an exception was shown:

Syntax error in FROM clause.

what i messed it up here, thank you.

CodePudding user response:

The error occurs as there is a space in the sheet name.

Try adding single quotes at the beggining & end of the square brackets.

For example:

"select * from ['"   sheetName   "$'];"

CodePudding user response:

I think you just need to remove the $ in "select * from [" sheetName "$]; "

The $ is indeed use in references in Excel itself, but I think is does not apply to the OleDB command syntax

See this question for reference: Reading from excel using oledbcommand

  • Related