Community in read only mode June 18 & 19
This community will be set in READ ONLY mode for a while on Tuesday June 18 into Wednesday June 19 while we import content and users from our Micro Focus Forums community site. MORE INFORMATION

Why do I get the error " *** Error: (HY000) [Microsoft][ODBC Excel Driver] Numeric field overflow. " when the first row of an Excel spreadsheet is not used as Column Headers ?

Why do I get the error " *** Error: (HY000) [Microsoft][ODBC Excel Driver] Numeric field overflow. " when the first row of an Excel spreadsheet is not used as Column Headers ?

This error is usually associated with the when reading data from Excel file using the DB_fetchNext() method.
The problem appears to be related to the Microsoft drivers for Excel. SilkTest uses these ODBC drivers to read data from the excel spreadsheet and normally identifies the top/first row as the column headers for the Excel Sheet it is currently reading from. If the structure of the excel spread sheet is as follows,

First Line // This line will cause problems

Name City Country Jobtitle
Jim Belfast N Ireland Engineer
Michael London England Sales
Fred New York USA Accounts
Burt San diego USA Marketing

Where "First Line.." is the first row of the excel spreadsheet. When SilkTest tries to read this it has trouble organising the data into columns and gives the error,

*** Error: (HY000) [Microsoft][ODBC Excel Driver] Numeric field overflow. When reading data from Excel file.

The best way to get round this problem is to create Excel files that have the column names at the top of the spreadsheet. This would make the Excel file appear as follows,

Name City Country Jobtitle
Jim Belfast N Ireland Engineer
Michael London England Sales
Fred New York USA Accounts
Burt San Diego USA Marketing

SilkTest will have no problem reading this format.

If the Excel file is being generated automatically and you need to get round the Numeric field overflow error then you can use the Do..Except method to catch the exception and continue to read another line from the spreadsheet. As long as the ODBC connection to the Excel spreadsheet is not closed then you will be able to continue reading rows from the spreadsheet until SilkTest gets to the first line that it can read properly i.e. the row with the column headers.

See code below

[-] testcase read_from_Excel() appstate none
    [ ] 
    [ ] string sConnString="DSN=book1"
    [ ] HANDLE hDB = DB_Connect(sConnString)
    [ ] 
    [ ] //string sSQL = "SELECT * FROM `Sheet1$``Sheet1`"
    [ ] HANDLE hSQL = DB_ExecuteSQL(hDB, sSQL)
    [ ] 
    [ ] list of anytype laData
    [ ] 
    [-] do  //1
        [ ] DB_FetchNext(hSQL, laData)
        [ ] Print("first line?")
        [ ] Print(laData)  
        [ ] 
    [-] except
        [ ] Print("exception handled")
        [ ] Print("There appear to be {listcount(laData)} Colums in the table")
        [ ] DB_FetchNext(hSQL, laData)
        [ ] Print(laData)   
        [ ]  
    [-] do  //2
        [ ] Print(laData)
        [ ] DB_FetchNext(hSQL, laData)
        [ ] Print(3)
        [ ] Print(laData)
    [-] except
        [ ] Print("first four lines handled")
    [ ] // read normally now..
    [ ] 
    [ ] Print("**************************")
    [ ] print("**********DATA************")
    [ ] print("**************************")
    [ ] 
    [ ] 
    [ ] 
    [-] while (DB_FetchNext(hSQL, laData))
        [ ] // process data
        [ ] Print(laData)
    [ ] 
    [ ] DB_FinishSQL(hSQL)
    [ ] DB_Disconnect(hDB)

Tags (2)

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2013-02-15 19:29
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.