SPONSORS:






User Tag List

Results 1 to 7 of 7
  1. #1
    Member
    Join Date
    Mar 2002
    Location
    Pune, MH, India
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Numeric field overflow while reading Excel file

    Hi,

    While reading from excel file, sometimes following error is shown.
    The statment used to read is DB_FetchNext().

    *** Error: (HY000) [Microsoft][ODBC Excel Driver] Numeric field overflow.

    The field which is to be read contain text or number.

    What is the cause of this ?

    -Niteen



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

  2. #2
    Junior Member
    Join Date
    May 2001
    Location
    Sophia-Antipolis, France
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Numeric field overflow while reading Excel file

    Possible problem could be that you have different cells format in the same column in your Excel sheet. You should try to have the same format in the whole column: select general for example.
    But sometimes it is not the only reason, and frankly, sometimes we didn't find the solution. Sometimes, re-creating the whole sheet will solve the problem.

    I don't know how your are using the data extracted from the worksheet. But, we have solve the problem by formatting all our worksheets in text. That could be done by adding a quote ' character in front of each cell contain (if it is not a formula), for example using a macro. That will make the list of anytype created by the DB_FetchNext, beeing a list of string instead.

    Olivier.

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

  3. #3
    Member
    Join Date
    Mar 2002
    Location
    Pune, MH, India
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Numeric field overflow while reading Excel file

    Hi,

    The value `29' gives overflow error.
    But if it is typed as 0.29 then it does not
    give overflow error. Though it is displayed
    as 029.

    All the cells are of type `General'.
    One observation is, this error is seen only
    if number is typed. For text no error occurs.

    -Niteen



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

  4. #4
    Senior Member
    Join Date
    Jun 1999
    Location
    Fremont, CA, USA
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Numeric field overflow while reading Excel file

    This is a bug in the Microsoft Excel ODBC driver. It seems to only occur on integer values.

    I find the best way to workaround the bug is to type the integer in as a string.

    Instead of typing: 45
    I type in: ="45"

    This seems to correct the problem. I have tried to use Excel to format the cells as text. That worked half of the time, fails the other half. Using the approach above, it works every time.




    ------------------
    David Genrich
    david.genrich@e2open.com
    david@linuxpuppy.net

  5. #5
    Member
    Join Date
    Mar 2002
    Location
    India
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Numeric field overflow while reading Excel file

    <BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>Originally posted by davidgenrich:
    This is a bug in the Microsoft Excel ODBC driver. It seems to only occur on integer values.

    I find the best way to workaround the bug is to type the integer in as a string.

    Instead of typing: 45
    I type in: ="45"

    This seems to correct the problem. I have tried to use Excel to format the cells as text. That worked half of the time, fails the other half. Using the approach above, it works every time.

    <HR></BLOCKQUOTE>

    Hi!
    Actually this is not a Bug in ODBC, try fixing it using 'Format Cells' option in MsExcel, may be u will have to change it twice, 1st change it to 'Text' then to 'Number', u can find that the Cell giving problem is in different format

    -amit




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

  6. #6
    Senior Member
    Join Date
    Jul 2000
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Numeric field overflow while reading Excel file

    I'll add my $.02 ...

    We simply took the approach of formatting the ENTIRE WORKSHEET as "Text". That way we don't have to enter "45" (with the quotes) as David suggests (this works, but you end up doing it over and over and over ...).


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

  7. #7
    Junior Member
    Join Date
    May 2001
    Location
    Sophia-Antipolis, France
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Numeric field overflow while reading Excel file

    Create the following Excel macro, to insert a quote in front of each cell of the matrix in the current worksheet: (you can check the last cell of the matrix by pressing Ctrl+End)

    Public Sub insertQuote()
    For Each c In Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeLastCell))
    cVal = c.Value
    c.Value = "'" + CStr(cVal)
    Next c
    End Sub

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

 

 

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 02:23 AM.

Copyright BetaSoft Inc.