Read long number from excel file

[oliver_am]

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.

[anand_sagarsethi]

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

Thanks

Anand

[oliver_am]

Yeah, I know that. But there are a lot of fields and lines in the excel file. If there isn't any other options...

[anand_sagarsethi]

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

[Sandra_Rossi]

About the conversion you try, I don't understand your point: if you have only a text "9.99+11", how can you expect a program to retrieve the non-significant digits (12017) if they are not in the text !!?? The only way is to look directly at the excel file, but be aware that a number with exponent has a limited number of significant digits, maybe something around 15 digits, so you can still lose some digits; for instance, if you enter 16 digits like 1234567890123456 in Excel, then it's represented as 1.23457E+15 and the real value is 1234567890123450, so you lose the last digit "6"). The only way to not lose digits, is to store the numbers using the text format.

[former_member400468]

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

[Former Member]

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

[pokrakam]

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.

[pokrakam]

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.

[Sandra_Rossi]

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! 🙂

[pokrakam]

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 🙂

[mangesh_parihar]

Hi,

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

It should work.

Regards,

Mangesh