SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 9 of 9
  1. #1
    Junior Member
    Join Date
    Dec 2003
    Location
    New Jersey
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Numeric Field Overflow

    Does anyone have an idea how to get the data out from an excel file with the first rows being blank.
    I receive the following error "Numeric Field Overflow" for a excel file that has the first few rows that are blank.
    I use the following to get the data out of the excel.
    STRING sExpected="C\SilkTest\Reports\ETest.xls"
    STRING sSheet="Sheet1$"
    LIST OF EXCELDB lrData={...}
    EXCELDB rData={}

    hdbc=DB_ConnectDBConnect.ConnectString"Excel",sExp ected))
    hstmnt = DB_ExecuteSQL(hdbc, "Select * from `{sSheet}`")
    while DB_FetchNext(hstmnt,rData)
    ListAppend(lrData,rData)
    DB_FinishSql (hstmnt)
    DB_Disconnect (hdbc)

  2. #2
    Senior Member
    Join Date
    Jan 2005
    Location
    UK
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Numeric Field Overflow

    Read the resolution from the knowledge base article from MicroFocus http://kb.microfocus.com/display/4/k...aspx?aid=22232

  3. #3
    Advanced Member KishoreApplabs's Avatar
    Join Date
    Oct 2006
    Posts
    965
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Numeric Field Overflow


    Small work around try this one may be it will solve your problem

    1. Open Excel file
    2. Select first two blank rows
    3. Try to delete my selecting right mouse button(select on delete)
    4. Save the file
    5. Close Excel file
    6. Try to run your script

    Regards,
    Kishore

  4. #4
    Junior Member
    Join Date
    Dec 2003
    Location
    New Jersey
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Numeric Field Overflow

    @Kishore, this file is automatically generated. Therefore, I cannot change the format unless I do low level recording, open the file, format, save and close the file.

    @Rakesh, I tried what the Microfocus site suggests. However there are blank rows in the file. It passes the first 2 rows and starts giving me error. So I updated the code and it works. I have many different Excel files with different formats. Hopefully they all work.
    [ ] STRING sSQL
    [ ] list of anytype laData
    [ ]
    [ ]
    [ ] INTEGER iCount=0
    [ ] hdbc = DB_Connect(DBConnect.ConnectString("Excel",sExpect edFile))
    [ ] hstmnt = DB_ExecuteSql(hdbc,"Select count(*) from `Sheet1$`")
    [ ] DB_FetchNext(hstmnt, iCount)
    [ ] DB_FinishSql(hstmnt)
    [ ]
    [ ] hstmnt = DB_ExecuteSql(hdbc,"Select * from `Sheet1$`")
    [-] for i=1 to iCount
    [+] do
    [ ] DB_FetchNext(hstmnt, laData)
    [ ] Print(laData)
    [+] except
    [ ] Print("Exception")
    [ ] // DB_FetchNext(hstmnt, laData)
    [ ] // Print(laData)
    [ ] DB_FinishSql(hstmnt)
    [ ] DB_Disconnect(hdbc)

  5. #5
    Advanced Member KishoreApplabs's Avatar
    Join Date
    Oct 2006
    Posts
    965
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Numeric Field Overflow


    1)if it's automatically generated file why it showing first two rows in blank columns?
    2)As per your requirement if you want delete the rows in the column try to use "Delete" command in DB_ExecuteSQL
    3)for your testing purpose right now you can go delete rows manually then execute your script you will get the idea
    4) for deleting blank rows your excel format will not be change

    Regards,
    Kishore

  6. #6
    Junior Member
    Join Date
    Dec 2003
    Location
    New Jersey
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Numeric Field Overflow

    The file is system generated. It is format specified by the Business. The DB_ExecuteSQL does not delete the row in the file, it will delete everything in the sheet. Currently some Excel files have empty rows between rows with data. I tried what Micro Focus has on its site, the do/except loop and it works. It returns only rows with data.

  7. #7
    Senior Member
    Join Date
    Jan 2005
    Location
    UK
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Numeric Field Overflow

    I've attached a sample spreadsheet (Excel 2003) together with code below to read the spreadsheet data.

    <font class="small">Code:</font><hr /><pre>[+] testcase TestIT() appstate none
    [ ] HDATABASE hdbc
    [ ] HSQL hstmnt
    [ ] LIST OF STRING lsData
    [ ] // Connect to our spreadsheet
    [ ] hdbc = DB_Connect("DSN=Silk DDA Excel; DBQ=C:\Book1.xls; UID=; PWD=;")
    [ ] hstmnt = DB_ExecuteSql(hdbc, "SELECT * FROM `Sheet1$`")
    [ ] // Iterate through our data
    [+] while (DB_FetchNext(hstmnt, lsData))
    [ ] Print(lsData)
    [ ] // Close our connection
    [ ] DB_FinishSql(hstmnt)
    [ ] DB_Disconnect(hdbc)</pre><hr />

    When you Run Testcase, all rows are read except for Row 1. Row 1 is considered to be the Heading, this is so columns can be queried based on Column names. If you were to delete the data out of any of the cells the value will be replaced with a NULL value, i.e. empty string. Run Testcase with a deleted cell, and notice how to the value has been replace with NULL. You could then perform a conditional statement to check for NULL values.

  8. #8
    Junior Member
    Join Date
    Dec 2003
    Location
    New Jersey
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Numeric Field Overflow

    @Rakesh, the above code does not work, that is what I had started with. Btw there is no file attached.

  9. #9
    Senior Member
    Join Date
    Jan 2005
    Location
    UK
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Numeric Field Overflow

    Is it possible to attach your excel document, as I'm certain that the code that I had written works fine.

 

 

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 10.71%
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 01:36 AM.

Copyright BetaSoft Inc.