topic Re: Problem when transfer data into excel in Application Development and Automation Discussions

Problem when transfer data into excel

Former Member — Tue, 30 Dec 2008 04:16:04 GMT

Hi all,

I saved some entries in an internal table and transferred into an excel file successfully. However, the format of some field was not what I want. I saved an identity number( 18 characters long ) and the excel automatically regarded it as an interger and expressed in the format '4.20102E+17' and it does not work if I widen the cell.

Could anyone give some suggestion on it, thx.

Re: Problem when transfer data into excel

Former Member — Tue, 30 Dec 2008 04:40:40 GMT

Hi,

Select the cell->Right click->Format cells->Number tab->general or Number.

Re: Problem when transfer data into excel

Former Member — Tue, 30 Dec 2008 05:06:38 GMT

Hi,

thx. Sorry, I forget to say that I don't want to do some setting after opening the excel. And I had tried this way before, it will omit the last three digits.

Re: Problem when transfer data into excel

Former Member — Tue, 30 Dec 2008 05:07:22 GMT

Hi,

Try this first delete the entry from that cell '4.20102E+17', select the column in which this cell is present - Right click and set the properties of that column to store as 'Text.' Then again paste your numbet in that cell. Surely it will work.

regards,

Vinod

Re: Problem when transfer data into excel

Former Member — Tue, 30 Dec 2008 08:17:32 GMT

Hi Bin,

One way around is you can add a single column ( ' ) to your idenity number. that is using concatenate keyword. which will keep the text as it is in excel sheet (without ' in final output)

Other way is - >As you are passing the correct data only. It's the limitataion with Excel only. Although, the data is also correct in Excel sheet 4.711E+17 or 4700025000000000 (18 char) is one of the same thing. and in case of calculation it will fetch you the excat result.

In case, you want it to display you have to do some modifications in Excel sheet.

Select the particular column->Right Click->Format cells->Number and select the decimal places as 0 and it will not ommit any digit at the end.

Regards,

Sachin

Re: Problem when transfer data into excel

Former Member — Tue, 30 Dec 2008 08:58:47 GMT

Hi Sachin,

Does the first way mean that I should merge two columns together? I try to concatenate single quotation mark into the identity number, it does work, however, the output has the quotation mark displayed. The current solution is just add some space into it to claasify.

The other way does not work on my excel file. I don't know if there exists any difference between our programs. It has omitted the last three digit before opening the file.

Anyway, thanks a lot!

Regards,

Bin

Edited by: Bin Zhou on Dec 30, 2008 9:59 AM

Re: Problem when transfer data into excel

Former Member — Tue, 30 Dec 2008 09:12:21 GMT

That cell values it takes as default format. You change cell format as required format.

Select the cell->Right click->Format cells->Number tab->general or Number.

Thanks & Regards

Md.Mahaboobkhan

Re: Problem when transfer data into excel

Former Member — Tue, 30 Dec 2008 13:11:05 GMT

Hi,

Instead of downloading directly in XLS format, make it into TXT file initially and later convert into XLS.

Regards,

Prasanth

Re: Problem when transfer data into excel

Former Member — Thu, 14 Oct 2010 02:37:14 GMT

Hi Mohammad,

Thanks for your suggestion all the same although the problem was accepted by the customer at the end.

However I think it should work cause as far as the format of the data is concerned, if excel interpret the data as text as your suggestion, there will be no round problem any more.

By the way, as I know later, if you put a ' at the beginning of the data, it should be interpreted as text.

Re: Problem when transfer data into excel

_IvanFemia_ — Sun, 17 Oct 2010 07:19:28 GMT

Hi,

I know that question is just answered, but I give you these infos:

try to read these blogs

[abap2xlsx - Generate your professional Excel spreadsheet from ABAP|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20046] [original link is broken] [original link is broken] [original link is broken];

[xlsx2abap - Read and edit your Excel files from ABAP|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20791] [original link is broken] [original link is broken] [original link is broken];

Code of this SCN Community project is shared on [Code Exchange|https://cw.sdn.sap.com/cw/groups/abap2xlsx]

Regards

Ivan