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