SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 7 of 7
  1. #1
    Junior Member
    Join Date
    Feb 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Silktest script to read data from Excel sheet

    I have created silktest script to read data from Excel sheet. When I run the code I am getting the following error message. Excel sheet is in place.

    Error Message:

    [ ] *** Error: (42S02) [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.

    [ ] Occurred in DB_ExecuteSql

    [ ] Called from Readexcel at sample.t(15)



    Code I am using:

    [-] type excelread is record

    [ ] INTEGER EMPNO

    [ ] STRING EMPNAME

    [ ] INTEGER SALARY

    [ ]

    [ ] excelread ex1

    [-] testcase Readexcel( ) appstate none

    [ ] HDATABASE hdbc

    [ ] HSQL hstmnt

    [ ] STRING sDataSource = "C:\Documents and Settings\Swathi\Desktop\Book1.xls"

    [ ] //hdbc = DB_Connect("DSN=Silk DDA Excel;DBQ=C:\Documents and Settings\Swathi\Desktop\Book1.xls;UID=;PWD=;")

    [ ] //hdbc = DB_Connect("DSN= Excel Files;DBQ=C:\Documents and Settings\Swathi\Desktop\Book1.xls;UID=;PWD=;")

    [ ] hdbc = DB_Connect("DSN=Segue DDA Excel;DBQ={sDataSource};UID=;PWD=;")

    [ ] //hdbc = DB_Connect("DSN=sudhaexcel")

    [ ] hstmnt = DB_ExecuteSQL (hdbc, "Select * from Book1")

    [-] while DB_FetchNext(hstmnt, ex1)

    [ ] Print ("EMP NUMBER IS : {ex1.EMPNO}")

    [ ] Print("EMP NAME IS : {ex1.EMPNAME}")

    [ ] Print("EMP Salary is : {ex1.SALARY}")

    [ ] DB_FinishSQL(hstmnt)

    [ ] DB_Disconnect(hdbc)

    [ ]

    Please help in the script

  2. #2
    Member
    Join Date
    Nov 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Silktest script to read data from Excel sheet

    --" hstmnt = DB_ExecuteSQL (hdbc, "Select * from Book1") "--

    Change the query >>

    Give the excel sheet name were you have your data.

    Also print hstmnt.

    Query >> "Select * from sheet1"

    Regards,
    Rohit S

  3. #3
    Advanced Member
    Join Date
    May 2005
    Location
    Fremont, CA
    Posts
    832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Silktest script to read data from Excel sheet

    I haven't done this in a while, but if I'm not mistaken, the query should be of a form:
    <font class="small">Code:</font><hr /><pre>
    select * from [$Sheet1]...
    </pre><hr />

    dimaj

  4. #4
    Junior Member
    Join Date
    Feb 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Silktest script to read data from Excel sheet

    we tried Sheet1 and [Sheet1$] and we are getting the same error response though.


    Could uou please help us in this type of script.

  5. #5
    Junior Member
    Join Date
    Feb 2009
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Silktest script to read data from Excel sheet

    Here is the code i have used.

    [-] type excelread is record
    [ ] INTEGER EMPNO
    [ ] STRING EMPNAME
    [ ] INTEGER SALARY
    [ ]
    [ ] excelread ex1
    [-] testcase Readexcel( ) appstate none
    [ ] HDATABASE hdbc
    [ ] HSQL hstmnt
    [ ] STRING sDataSource = "C:\Documents and Settings\Swathi\Desktop\Book1.xls"
    [ ] //hdbc = DB_Connect("DSN=Silk DDA Excel;DBQ=C:\Documents and Settings\Swathi\Desktop\Book1.xls;UID=;PWD=;")
    [ ] hdbc = DB_Connect("DSN=Segue DDA Excel;DBQ={sDataSource};UID=;PWD=;")
    [ ] hstmnt = DB_ExecuteSQL (hdbc, "Select * from Sheet1")
    or
    hstmnt = DB_ExecuteSQL (hdbc, "Select * from [Sheet1$]")
    [-] while DB_FetchNext(hstmnt, ex1)
    [ ] Print ("EMP NUMBER IS : {ex1.EMPNO}")
    [ ] Print("EMP NAME IS : {ex1.EMPNAME}")
    [ ] Print("EMP Salary is : {ex1.SALARY}")
    [ ] DB_FinishSQL(hstmnt)
    [ ] DB_Disconnect(hdbc)

  6. #6
    Member
    Join Date
    Aug 2006
    Posts
    152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Silktest script to read data from Excel sheet

    I generally use this format ...

    hstmnt = DB_ExecuteSQL (hdbc, "SELECT * FROM `Sheet1$`")

    Although this shud work as well - hstmnt = DB_ExecuteSQL (hdbc, "Select * from [Sheet1$]")

    Make sure that the sheet name in your Book1.xls is Sheet1

    Also, make sure your excel file is named as "Book1.xls" &amp; not "Book1.xls.xls" Some times if the file extensions are hidden then we dont realize how the file is actually named ... it might not be the case with u but its better to be sure [img]/images/graemlins/smile.gif[/img]

    2lz

  7. #7
    Senior Member
    Join Date
    Apr 2008
    Location
    Hyderabad
    Posts
    339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Silktest script to read data from Excel sheet

    Hi Sunil,

    I think in the path to excel file you gave the user name "Swathi". try by replacing it with your username.
    If it is not the case then follow the below steps.

    1. Create a new excel Book1 in C:drive
    2. Enter the data into it
    3. make sure that the Work book name is "Book1.xls" and sheet name is "Sheet1".
    4. Copy the below code try it now.

    <font class="small">Code:</font><hr /><pre>

    [+] type excelread is record
    [ ] INTEGER EMPNO
    [ ] STRING EMPNAME
    [ ] INTEGER SALARY
    [ ] excelread ex1
    [+] testcase Readexcel( ) appstate none
    [ ] HDATABASE hdbc
    [ ] HSQL hstmnt
    [ ] STRING sDataSource = "C:\Book1.xls"
    [ ] hdbc = DB_Connect("DSN=Segue DDA Excel;DBQ={sDataSource};UID=;PWD=;")
    [ ] hstmnt = DB_ExecuteSQL (hdbc, "Select * from [Sheet1$]")
    [-] while DB_FetchNext(hstmnt, ex1)
    [ ] Print("EMP NUMBER IS : {ex1.EMPNO}")
    [ ] Print("EMP NAME IS : {ex1.EMPNAME}")
    [ ] Print("EMP Salary is : {ex1.SALARY}")
    [ ] DB_FinishSQL(hstmnt)
    [ ] DB_Disconnect(hdbc)
    </pre><hr />
    Its working fine for me.
    Regards,
    Krishna Chaitanya

    =====================
    Try and fail, but don't fail to try
    =====================

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 9.38%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 06:17 AM.

Copyright BetaSoft Inc.