-1

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.

Eddayfy
  • 1
  • 1
  • 2
  • 1
    Double check the name of the sheet. You've added a space between `topos` and `.architectures` – Greg Oct 09 '21 at 11:15
  • thank you for your answer.The name of the sheet i get it programmatically using a function that get me the name of the first sheet in an excel file, that's mean the space is there – Eddayfy Oct 09 '21 at 11:26
  • 1
    Can you put a break point on `OleDbCommand("select * from [" + sheetName + "$];` and compare the name to the excel sheet? – Greg Oct 09 '21 at 11:29
  • Yes i did an i found it's the same, the renaming of the sheet in file make it works, but i need to use the name in the file. – Eddayfy Oct 09 '21 at 11:50

2 Answers2

0

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

Sylvain Girard
  • 11
  • 2
0

The problem was in the dots in the Sheet name, the dots should be an #.

Like that:

"select * from ['" + sheetName.Replace('.', '#') + "$'];"

Eddayfy
  • 1
  • 1
  • 2