SPONSORS:






User Tag List

Results 1 to 5 of 5
  1. #1
    Junior Member
    Join Date
    May 2001
    Location
    Oakland,CA,USA
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems with getting data from excel

    Hi,
    I am attempting to get data from an excel spreadsheet via DBTester. I am using the following DBTester code:

    [-] testcase Test2 () appstate none
    [ ] string Subscriber_ID
    [ ]
    [ ] string sConnect = "DSN=Excel Files;DBQ=G:\test.xls"
    [ ] handle hDB = DB_Connect(sConnect)
    [ ]
    [ ] string sSQL = "SELECT Str(Subscriber_ID) FROM `Sheet1$` `Sheet1$`"
    [ ]
    [ ] handle hSQL = DB_ExecuteSQL (hDB, sSQL)
    [ ]
    [-] while (DB_FetchNext (hSQL, Subscriber_ID))
    [ ] Print(Subscriber_ID)
    [ ]
    [ ] DB_FinishSQL (hSQL)
    [ ] DB_Disconnect (hDB)


    I have narrowed down my spreadsheet to one column consisting of 132 rows of 9 digit numbers. When I run the test, I get the following output:

    [-] Testcase Test2 - 1 error
    [ ] 453781326
    [ ] 567474176
    [ ] 462393764
    [ ] 449803391
    [ ] 456159430
    [ ] 456159430
    [ ] 453809421
    [ ] 453809421
    [ ] 453809421
    [ ] 453809421
    [ ] 455763273
    [ ] 449994103
    [ ] 451391866
    [ ] 451391866
    [ ] 580093010
    [ ] 580093010
    [ ] 462829169
    [ ] 462829169
    [ ] 462829169
    [ ] 462829169
    [ ] 462829169
    [ ] 462829169
    [ ] 458082560
    [ ] 458082560
    [ ] 165545646
    [ ] 458981417
    [ ] 458981417
    [ ] *** Error: (HY000) [Microsoft][ODBC Excel Driver] Numeric field overflow.
    [ ] Occurred in DB_FetchNext
    [ ] Called from Test2 at bug.t(14)


    It seems as though the breaking point is after the 31st number. I don't know what the error actually means or why it would happen.

    Any help would be *really* appreciated.

    Thanks in advance,

    Kevin


    ------------------

  2. #2
    Senior Member
    Join Date
    Sep 2000
    Location
    Twin Cities, MN, USA
    Posts
    653
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Problems with getting data from excel

    Hi Kevin,

    Since you state all the numbers are nine digits in length we can rule out the size of the number.
    It may be the Str you're using in your select statement. Using a to_char may make things work better, like this:

    select DISTINCT to_char(Subscriber_ID) from 'Sheet1$' 'Sheet1$'

    You will need to DB_Fetchnext into a string for this to work. Like you are already doing:

    DB_FetchNext (hstmnt, Subscriber_ID)

    Steve

    ------------------

  3. #3
    Senior Member
    Join Date
    Sep 2000
    Location
    Twin Cities, MN, USA
    Posts
    653
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Problems with getting data from excel

    My apologies for my last post. I still had my brain in Oracle mode...

    Your SQL looks fine. How about the formatting in your spreadsheet? I wonder if you formatted the cells as General or Text if the query would work.

    I can't find anything about the error that relates to the trouble you're having. All the docs available to me say the problem is from retrieving too large a number into a data type, but your code takes care of that.

    Sometimes when people run queries in Excel that I have set up to extract data from an Oracle DB they get an Overflow error and the resolution has been to reboot.

    ------------------

  4. #4
    Senior Member
    Join Date
    Aug 1999
    Location
    Cambridge, UK
    Posts
    470
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Problems with getting data from excel

    The error looks like it is one returned by the odbc driver, rather than originating in silktest. So, it could be a bug in the driver or could be a problem with the data being reported.
    Try using a different odbc client to run the sql query; the problem should be shown up quickly and easily there.
    Another point to check might be to remove the Str() bit from your query, and pull the data straight into a numeric variable, to see if that helps isolate the cause of the problem.

    ------------------

  5. #5
    Senior Member
    Join Date
    Jul 1999
    Location
    Boston, MA. USA
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Problems with getting data from excel

    Not sure if this will help at all, but James Soderborg published an article in segue@work about accessing Excel using DBTester. Here is the link to the article (you will need your Segue username and customer number to access the article): http://customers.segue.com/protected...messo_toc_.htm

    If you have any issues accessing it, please let me know and I will email you the article.

    Cheers!
    Heidi Gilmore
    Segue

    ------------------

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin 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 04:06 PM.

Copyright BetaSoft Inc.