Error: Cannot find table 0
I’m using SmartConnect.com or SmartConnect 21 on-premise 21.1.0.947 or greater (which no longer uses Office Excel ODBC Drivers) and using a specific Excel File as a data source instead of using an Excel Folder Data Source, when I get this error message:
Connection could not be validated.
An error occurred retrieving the data schema: Cannot find table 0
The screenshots are from SmartConnect 21, but the same would apply for SmartConnect.com maps using Excel File Sources.
If you’d like to watch a video explaining this, see below or click here.
Daily, the source Excel file is moved into the correct folder and correct file name, and the map normally runs fine to read that source file – but once in a while, opening or executing the map – SmartConnect throws the error:
Connection could not be validated.
An error occurred retrieving the data schema: Cannot find table 0
To resolve this error, we manually create a new Excel file with the same name and copy & paste the data into the new file, but this is an inconvenient workaround process. What is the root cause of this error?
The underlying issue is that for these “problem” source files is that the sheet name is different than what is configured in the map source.
SmartConnect can find the expected source Excel file, but the Excel sheet configured – ‘D-202599b’ doesn’t exist in the new file and the parsing engine fails with this unexpected error.
Since there IS a sheet in the file with the correct data, instead of creating a new Excel file, you could just rename the existing sheet to be the expected name: D-202599b in this case and it would work fine. Still inconvenient but quicker than creating a new file and copying the sheet data.
Because the file and sheet name now exist, the map will be able to read the source data and run successfully again.
On SmartConnect 21, there is another option. We can have SmartConnect 21 prompt for the Excel file and sheet name.
By marking the “Select File at Runtime” checkbox on the datasource, this will force SmartConnect to prompt every time the map is run by the user. This wouldn’t be a good solution for the map running from the eOne SmartConnect Schedule Service since the dialog could not be answered.
When selecting that checkbox, SmartConnect 21 or SmartConnect.com would prompt for the file to be used and it defaults to the configured file. However, it forces you to always select a sheet which forces the user to select whatever sheet is in the file which gets around the mis-named sheet issue. It does, however, slow down the manual process a bit since we must always select a sheet even if the configured sheet did exist.
To avoid this issue in the future, you would want to investigate the source of the “sheet name” issue and determine whether it is a process issue on file creation or simple human error if it was manually entered and saved.
