Excel formatting issue

[former_member212705]

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.

[Tomas_Buryanek]

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.

[former_member209120]

This message was moderated.

[former_member212705]

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

[Tomas_Buryanek]

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.

[former_member212705]

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.

[Tomas_Buryanek]

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.

[former_member212705]

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.

[Tomas_Buryanek]

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.

[Former Member]

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

[Former Member]

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

[former_member212705]

Hi Smruti,

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

[Former Member]

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

[former_member219761]

I think my document can help you

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

Budtawan