Exceding the limit of numbers in Excel at target side

[Former Member]

Hi ,

I am working on Proxy to file scenario in that data is coming frm SAP tables at source side data type is string and in target side also data type is string .

But when I am getting the data in .csv excel file if the number are exceding the limit they are converted in Exponational form.

let say in 1 coloum if 15 numbers are coimg then it is ok

if they are coming as a 16 numbers they are converting into 453E+11 form .

Please suggest me on this issue...

Regards

[Former Member]

solved.. user have correct at their side

[Former Member]

solved..

[Former Member]

Hi,

If you open the CSV file with a text editor, how does the content look like ? Especially for cols > 15 ...

rgds

Chris

[VijayKonam]

That is not anything to do with PI. Expand the column width when you open the file in excel. Automatically you would see the full number.

If not you might have to create an Excel file instead of CSV file.

VJ

[Former Member]

HI Vijay HI Sathish,

@Sathish -

---

I am not using any arthmatic function..

@Vijay-----why do we need to make excel file and what we have to do to make excel file...

Regards

Edited by: gangadhar kh on Apr 8, 2010 1:41 AM

[Former Member]

Hi,

Y cannot u convert the scientific number format to decimal format for all those fields which u r expecting to be in scientic format.

Use

Double.parseDouble(strNumber)...

This will convert the string to Double format... or else use BigDecimal format also..

Babu

[Former Member]

Hi Babu,

That means for this we have to use UDF ?

Can plz explain more how we use this ?

I am having one field at sender side which data type is string and receiver side is also is string..

Regards

[Former Member]

Hi,

Can u please clarify these points..

If the number is converting to scientifc form in excel then...

1.Is this number converion happened at source itself or not??? U check the source payload.. is it converted there itself or not..

2.If not in source is it converted after mapping???

3.Or is it converted in target side only??

U can try with a small udf like this..

Input: number(the number u r expecting to convert)

Output: String

Code:

return Double.parseDouble(number); // only 1 line of code

Try with this...

If still nt resolved.. do post

Babu

Babu

[former_member200962]

I dont think any change in XI will help you....it is due to some settings of the Excel that you see the number as 453E+11

Right -click on the cell --> format cell --> keep the option as Number --> reduce the decimal places as per your need and then check the result.

Regards,

Abhishek.

[Former Member]

Correct Abhishek ...

I have checked with this also and it's working ...

But can you please let me know why this happening however I have taken data type length 30 but I have taken the data type as string .

Do I need to take data type as number?

By doing settings in excel will resolve the problem permanently?

or

Do I try wid this code

say phone number +420242425078(string data type) -

---

UDF------ +420242425078( string data type)

Regards

[Former Member]

Hi,

Can u please clarify these points..

If the number is converting to scientifc form in excel then...

1.Is this number converion happened at source itself or not??? U check the source payload.. is it converted there itself or not..

(No the number converion happened at source I have checked the source payload )

2.If not in source is it converted after mapping???

NO it is not converted after mapping also

3.Or is it converted in target side only??

this is converting in target side only...

Regards

[Former Member]

Hi Babu,

please chk my reply below

If the number is converting to scientifc form in excel then...

1.Is this number converion happened at source itself or not??? U check the source payload.. is it converted there itself or not..

(No the number converion happened at source I have checked the source payload )

2.If not in source is it converted after mapping???

NO it is not converted after mapping also

3.Or is it converted in target side only??

this is converting in target side only...

Regards

[former_member200962]

I dont think that it can be solved in XI......if your entire Excel is going to contain only numbers then do the formatting for the entire sheet.....setting length restriction in XI will only help you in viewing the data properly in XI (SXMB_MONI or RWB).

[Former Member]

I dont think that it can be solved in XI......if your entire Excel is going to contain only numbers then do the formatting for the entire sheet.....

In target side in excel we have to do the settings?

setting length restriction in XI will only help you in viewing the data properly in XI (SXMB_MONI or RWB).

Can we use this function in our UDF

Integer.parseInt(input string variable)

will it work?

Regards

[former_member200962]

In target side in excel we have to do the settings?

Same as you did before.....Right-click --> Format Cells --> Number....

Can we use this function in our UDF 

Doubtful if any UDF used in XI will show you a non-exponential value in Excel.

May be you can give it a try and let us know if you are able to see the desired output.

Regards,

Abhishek.

[Former Member]

Hi Abhishek,

I have used this below fuction but it not getting resolve...

int i= Integer.parseInt(a);

return a;

Even as u said formating of colum I have done but when I am saving that file after formating as number at the time of saving it is not getting saved...

Regards

[former_member200962]

I have used this below fuction but it not getting resolve

Thats what i said, i am doubtful if any UDF used in XI will help solve the problem in the EXCEL......it has to be handled in the excel itself.

Even as u said formating of colum I have done but when I am saving that file after formating as number at the time of saving 
it is not getting saved...

I am able to save the excel with Number as the formatting.

[Former Member]

Hi Abhishek,

I tried with like when I am given 420242425078 it is getting converted in 4.202424E+11 and when gievn aaaaaaaaaaaaaaaaaaaa it is coming as it is in excel.

Why this happening in case of numbers why not in case of charecters

Please suggest me on this.........

Regards

[Former Member]

dunno if this helps, but :

"If you don't apply any special formatting to text, Excel uses the General number format, which basically means anything that will fit. That means Excel will show as many decimal places that the text item has room for, it won't use a thousands separator, and if the number can't fit, perhaps due to a narrow column width, Excel uses a scientific number format. If only one format is specified, Excel uses it for any contents of the cell."

that may explain why, for such "big" numbers, XL switch to scientific notation for display

Rgds

Chris

[former_member200962]

Why this happening in case of numbers why not in case of charecters

Characters will appear as it is....there is no exponential factor for a character....is your excel not getting savedwith Number Formatting?

If XI is passing the value as 12345678912345 then the value should appear as it is in excel also, if formatting is done to number.

[Former Member]

Hi Gangadhar,

Please check this note and see its there or not:

Note 958486 - Arithmetic or Statistic function returns wrong result

Also this weblog and help:

/people/thorsten.nordholmsbirk/blog/2006/04/03/never-ever-use-xis-built-in-arithmetic-functions

Regards,

---Satish