topic Excel formatting issue in Application Development and Automation Discussions

Excel formatting issue

former_member212705 — Tue, 12 Nov 2013 09:36:09 GMT

Hi Experts,

I want to ask one question. If i am downloading data in csv format. Then the fields which are having larger values automatically converts to exponential format.

and also it converts some data to the exponential .suppose i have one value '12E13'

If i save this value to excel then value gets converted to exponential that destroys value 12E13 completely if i convert it back to normal value:

How to prevent these value to convert to exponential while downloading to excel. I am using GUI_DOWNLOAD FM. One solution is to concatenate ' in the start but that is not a good practice. I have to do it via coding. So please suggest any pointer on it.

I know many others have asked this question previously but i could not found proper solution in any thread.

Regards,

Ashish T.

Re: Excel formatting issue

Tomas_Buryanek — Tue, 12 Nov 2013 11:37:40 GMT

I believe it is standard behavior of Excel with CSV files.

Solution other than single quote " ' " is to export/download to XLSX or other format.

Re: Excel formatting issue

former_member209120 — Tue, 12 Nov 2013 11:53:12 GMT

This message was moderated.

Re: Excel formatting issue

Former Member — Tue, 12 Nov 2013 12:00:15 GMT

Use the following FM

FM CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
       EXPORTING
         i_filename     = p_file "File Name
       TABLES
         i_tab_sap_data = i_export. "internal table

Re: Excel formatting issue

former_member212705 — Tue, 12 Nov 2013 12:44:55 GMT

HI Ramesh,

But again if i upload that excel then data will come with replace statement

Basically i have to download the data from tables and upload them again.

Regards,

A.Trivedi

Re: Excel formatting issue

Tomas_Buryanek — Tue, 12 Nov 2013 13:00:02 GMT

If you want only download and upload. Than Excel exponential format does not matter 🙂

Just use GUI_DOWNLOAD & GUI_UPLOAD (or class methods). Do not need change data anyhow.

Try open downloaded format in notepad if you dont understand. Datas in file are not converted to exponentioal format.

Re: Excel formatting issue

former_member212705 — Tue, 12 Nov 2013 13:14:48 GMT

Hi Tomas,

I have to do it in CSV format. But u know it matters, suppose a value in DB is '10E23' then excel will show its value as exponential format and value will be changed.

Re: Excel formatting issue

Tomas_Buryanek — Tue, 12 Nov 2013 14:23:41 GMT

To make it clear. You wrote:

"Basically i have to download the data from tables and upload them again."

So why you open it in Excel and change it there? If you dont change it, it will remain '10E23' and you can upload it back.

If it has to be CSV and you have to open&change it in Excel, then only solution what I know is to properly configure CSV import and export in Excel (by it I mean opening and saving in MS Excel or other program). For example you can specify columns as "text" in Excel "Text import wizard", then data in cells will not change.

Re: Excel formatting issue

former_member212705 — Wed, 13 Nov 2013 06:40:23 GMT

Now suppose i am downloading it in excel look at the value

Now when i upload into internal table:

everthing is in XLS format as u said.

Re: Excel formatting issue

Tomas_Buryanek — Wed, 13 Nov 2013 07:58:28 GMT

I really would like to help you, but it is still unclear what are you doing or what you want to do.

Once again:

1) GUI_DOWNLOAD data from SAP to CSV

2) what you do here?

3) GUI_UPLOAD from CSV to SAP?

If you skip 2), than there should be no problem. I tried it... CSV is simple text file.

------

By the way XLS is not the same format as XLSX. And with these formats it really depends on how you specify cell formats.

Re: Excel formatting issue

Former Member — Wed, 13 Nov 2013 08:56:42 GMT

Hi ,

If you want to DB Value in to Excel Sheet from SAP using GUI_DOWNLOAD FM then you must use "FILETYPE = 'DBF'" check FM Documentation .

Use FM "TEXT_CONVERT_XLS_TO_SAP" to Upload 'CSV' File into SAP .

Note :

Please read below note SAP Recommend avoid CSV file formats using GUI_UPLOAD FM

Showcase GUI_UPLOAD: Comma separated value (CSV) file ,Notes :1066919

also check here i am using GUI_DOWNLOAD with DBF File Type but instead of .csv , i am download the file into .xls . How to Download Leading with Zeros from SAP to ... | SCN

Regard's

Smruti

Re: Excel formatting issue

former_member212705 — Wed, 13 Nov 2013 09:03:57 GMT

Hi Smruti,

Is there any limitation of DBF format , i think it is able to download only 255 columns??

Re: Excel formatting issue

Former Member — Wed, 13 Nov 2013 10:26:55 GMT

Yes , then you use FM"SAP_CONVERT_TO_XLS_FORMAT" for download the data into .CSV File which is suggest by but you need to Concatenate with each value with single quote .

Suppose your ITAB-FIELD1 = '12E13' .

CONCATENATE '''' ITAB-FIELD1 TO ITAB-FIELD1 .

and One thing you must keep your mind if FIELD1 actual size is 10 Char but in your case you take SIZE is 11 Char for Single Quote for additional Char .

Note : Single Quote mean Convert DBF Format .

Regard's

Smruti

Re: Excel formatting issue

former_member219761 — Wed, 13 Nov 2013 10:30:33 GMT

I think my document can help you

http://scn.sap.com/docs/DOC-48726

Budtawan