SilkTest Question 18: How to read data from Microsoft Excel worksheet?

For QA purposes we are trying to read a block of cells from Excel spreadsheet and use values later for creating data driving automated test. The testing process set up in such way that we don’t want to use exact column names and prefer to just get data from cell numbers specified for example as B4:D11. Basically we want to obtain the cell values for all spreadsheet cells from B4 to D11 and print them out.

Try to create your test script based on the following code

[] hDBC = DB_Connect ("DRIVER=Microsoft Excel Driver (*.xls); FIRSTROWHASNAMES=1;READONLY=FALSE;
DRIVERID=790;DBQ=C:\QA.xls")
[] //run a SQL statement
[] hSQLq = DB_ExecuteSQL (hDBC, "SELECT * from [Sheet1$B4:D11]")
[] //while there are still rows to retrieve
[-] while DB_FetchNext (hSQLq, description, var1, var2)
[] print("{++i}:{ description } { var1} { var2} !")
[]
[] DB_Disconnect(hDBC)

For training purposes you have to take a look at SilkTest user manual for the following functions:
  • DB_Connect
  • DB_ExecuteSQL
  • DB_FetchNext
  • DB_FinishSql
and Datatypes:
  • HDATABASE
  • HSQL
Also take a look for the following answer about DBTester database functions.

2 comments:

priya said...

I tried the same logic to read values from cell B2:B18

[ ] HSQL hSQL = DB_ExecuteSQL (hDBC, "SELECT * FROM [Sheet2$B2:B18]")
[ ] //STRING var1
[ ] STRING var2
[ ] INTEGER i
[-] while DB_FetchNext(hSQL, var2)
[ ] print("{++i}:{var2}!")

But I get an error stating that "Variable is not been set". I am unable to resolve . Please help me

Oscar Ayala said...

You have to initialize i to 0:

...
[ ] INTEGER i = 0
[-] while DB_FetchNext(hSQL, var2)
[ ] print("{++i}:{var2}!")

SilkTest interview questions for QA Testers