Skip to Content
-1

Read long number from excel file

Hi all,

I'm reading an excel file using ole2 object (well, using FM ALSM_EXCEL_TO_INTERNAL_TABLE )

Some fields are long numbers, for example 999000012017, but is being reading as 9,99E+11.

How can I read the correct value?

I've tried with:

DATA: lv_text TYPE char128.
DATA: lv_float TYPE f.

IF <lv_field> CS 'E+'.
*lv_float = <lv_field>.

WRITE <lv_field> TO lv_text EXPONENT 0.
ENDIF.

Thanks in advance

EDIT: I've tried with FM QSS0_FLTP_TO_CHAR_CONVERSION in SE37
but i'm losing some data; it's converting 999000012017 to 999000000000,00

In my program i'm getting a dump if I use this FM because I have a char and I need a float.

Add comment
10|10000 characters needed characters exceeded

  • The point is that the digits are there. But OLE will transfer the view representation - i.e. what's visible in the cell.

    Try it: paste 999000012017 into a blank Excel sheet. It shows 1E+12. Now make the column bigger, it will show 9.99E+11. Click on it and in the cell content the number is still visible. Change the format to Number and you will see all the digits. This is why I imagine Richard's answer should work.

    As an aside, ABAP2XLSX behaves the opposite way: you get the 'internal' contents. e.g. date and time values come back to SAP as a serial number, making today's date 42894.

  • Thanks. I understand very well the situation. In fact, I think it's/was just a wording issue in the question. Originally, Oliver said something like "I'm converting 9.99E+11 with QSS0_FLTP_TO_CHAR_CONVERSION, and it doesn't give 999000012017". It's why I answered, but my answer is not even more clear, sorry! :)

  • OK, I got confused between your example of number being too long for Excel (16 digits) and his 12 digit example that fitted comfortably within Excel's number limit. I was a bit confused when it seemed that you didn't 'get' the original problem, it just seemed too unlikely :)

  • Get RSS Feed

5 Answers

  • Jun 08, 2017 at 06:55 AM

    Hi,

    When I've had this problem it's been down to the format of the Cell in the Excel file. As an experiment, try formatting the column that contains the long number so that it is wide enough to display the number in it's normal format 9ie not exponential). If you then get the long number in it's entirety, that is your problem.

    Otherwise I would suggest changing the column to 'text'.

    If either of those two work then if you are in control of the format of the spreadsheet you're done. Otherwise you may need to talk to the author to try and get that column format changed. If that doesn't happen then go full OLE and get your program to do it on the fly,.

    Rich

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 07, 2017 at 03:26 PM

    if you can change the value as text in the excel, it should work correctly.

    Thanks

    Anand

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      If FM ALSM_EXCEL_TO_INTERNAL_TABLE
      is giving you results in that way.

      I would say the problem is with the excel sheet, but as you said you cant change it.

      I would go for alternatives, I will try to use different function module:

      TEXT_CONVERT_XLS_TO_SAP

      KCD_EXCEL_OLE_TO_INT_CONVERT

      Anand

  • Jun 08, 2017 at 06:29 AM

    Hi!

    May be you can try another FM - TEXT_CONVERT_XLS_TO_SAP, you can set explicitly your structure to this FM, and it converts the values inside.

    Hope it's helpful

    Evgeny

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 08, 2017 at 10:14 PM

    An option that involves a fair bit of effort is to use ABAP2XLSX. IMHO it would not be wasted effort though, there are many benefits, especially if your Excel processing gets more complex than a simple table.

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 22, 2017 at 09:06 AM

    Hi,

    You can use that particular field type in internal table as string.

    It should work.

    Regards,

    Mangesh

    Add comment
    10|10000 characters needed characters exceeded