I am getting invalid sheetnames from Excel files using OleDb. What's wrong?

Question

I try to get excel sheet names, with oledb.

My connection string is:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
filepath + 
";Extended Properties=Excel 12.0;";

where filepath is a filename.

My code for this:

List<string> sheetNames = new List<string>();
_excel.Connect(_path);
DataTable dataTable = _excel.ExecuteSchema();
_excel.Disconnect();
foreach (DataRow row in dataTable.Rows)
{
    string sheetName = row["TABLE_NAME"].ToString();
    if(!sheetName.EndsWith("$'")) { continue; }
    sheetNames.Add(sheetName);
 }

the list with the sheets names contains all valid sheet names and some other sheet names. Example:

• "'correctsheetname$'"
• "'correctsheetname$'Print_Area"

I only add sheets that end in $'

My problem is that if a sheet name contains a single quote, I get it with two single quotes.

Example: for sheet named asheetname's I get 'asheetname''s$''

Afterwards, when I try to get the datasource of this sheet, I am getting an exception that this sheet doesn't exist.

query = "SELECT * FROM ['asheetname''s$']"
_command = new OleDbCommand(query, _connection);
_dataTable = new DataTable();
_dataReader = _command.ExecuteReader();   <-- Exception is thrown here

And the exception message:

{System.Data.OleDb.OleDbException: ''asheetname''s$'' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at PrestaImporter.Entity.Excel.ExecuteQuery(String query)

Answer 1

This should work

"SELECT * FROM [" + asheetname + "$]"

if it won't, try manually typing the sheet name into the string variable asheetname - it should open.

"SELECT * FROM ["SHEET_NAME"$]"