[Microsoft][ODBC Excel Driver] too few parameters. Expected 2

Problem Description

While using database query with MS excel driver, and debugging the log file, there is a message [Microsoft][ODBC Excel Driver] too few parameters. Expected 2 which does not allow software to output correct data. This is the common message while querying excel file which happens because of not selecting column/cell as desired with correct format. 

Steps to Resolve

1. As a prerequisite, OpenPlant Isometrics Manager needs 32 bit ODBC drivers. In case DBQUERYs are used on Microsoft products like Access (*.Mdb) or Excel (*.xls, *.xlsx) one must check if the Windows OS contains recent 32 bit ODBC drivers.
2. Start this application: C:\Windows\SysWOW64\odbcad32.exe
   
3. Go to the drivers tab and check if the following driver is present:

     
   
   Note: If not it is advised to install a more recent driver set, to be found here - >>>>

4. Create/Use excel file with which contains data to be queried inside OpenPlant Isometrics Manager. 
5. Store it in desired location, say C:\LineList.xlsx.
   
   
   
   
6. User requirement is to check any field , say at 76th column value based on LINENUMBER at 14th column from sheet name say Sheet11. see Below image. 
   For Line number at N column should be counted as F14
   
   
   For Value to be queried for BX column should be counted as F76
   
   Note - Prefix F is mandatory for all count and to query any field
   
   
7. Navigate to style folder for corresponding project. See path mentioned below for default project. 
   
   
   
   
8. Open Textnodes.txt file. 
   
   
9. Add query as mentioned below. 
   

   exds=Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=$(IE_EXCELPATH)
   LDP=DBQUERY($(exds), Select [F76] from [Sheet11$] where[F14]='$(LINENUMBER)')
   LDT=DBQUERY($(exds), Select [F76] from [Sheet11$] where[F14]='$(LINENUMBER)')

   
   
   

10. Save & close file. 
11. Launch OpenPlant Isometrics Manager & verify results.

NOTE: If you are going to follow the above method, please make sure that the first row of the Excel Sheet is blank

For example, if below is the format of the excel sheet to be used

          

Writing a query like

          

will give an error like

          

The reason being if the first row is not blank, the [F_] nomenclature can't be used to refer to columns in the Query. In that case,add a blank row on the top as shown.

Still a Problem

If there is still some problem, then please create a Service Request and contact Bentley Product Advantage group along with the list of all relevant details to get in depth analysis and for possible solution or workarounds. 

See Also

http://communities.bentley.com/products/plant/design___engineering/w/plant_design_and_engineering__wiki/18475.dbquery-usage-logging-debugging

Comments or Corrections?

Bentley's Product Advantage Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!