Excel Download from AL11

[former_member207873]

Hi Experts,

I am facing an issue downloading AL11 data into the excel. I have a got an ALV report and before the report is getting displayed we are trying to sent the final internal contents to the AL11 directory. So our AL11 file looks something like this.

Coloumn Headings

     Coloumn Data

The '#' (as delimiter) i have annexed so that each data will sit in a cell in the excel. But it is not working. The whole line data is coming in a single cell. I am using the the standard option to download to excel . system -> List -> Save -> Local File. So is it possible to download to excel from AL11 with data in exact cells as we see in ALV ?

P.S. I am on 4.6 C

B.R.

[Former Member]

Hi B.R.

The quick solution is to use the "Text to Columns" feature in Excel.

Alternatively you'd need to look at the exceptionally high volume of SCN posts on downloading to Excel.

Checkout this WIKI page for a start, not sure it's applicable to your release though

http://wiki.scn.sap.com/wiki/display/ABAP/abap2xlsx

Regards

Arden

[rosenberg_eitan]

Hi,

Why not convert your data to CSV format ?

See here Multi Line Cell In Excel Using CSV by Mail  | SCN

https://en.wikipedia.org/wiki/Comma-separated_values

Regards.

[former_member207873]

Hi Eitan Rosenberg ,

I am on 4.6C. So your code may not work. I tried to add ',' (CSV) as you suggested to the values in the program. But even  after trying to download from AL11 in the excel format it is coming in one single line.

B.R.

[rosenberg_eitan]

Hi,

The files needs to behave like CSV.

See here:

https://en.wikipedia.org/wiki/Comma-separated_values

See attached file

[former_member207873]

Hi Eitan Rosenberg ,

Thank you . It helped. Eventhough it is looking messy in AL11 it is coming good in Excel. Only one issue the coloumn heading is missing after certain coloumn. I am declaring it is as string.

[rosenberg_eitan]

Hi,

Can you post a sample of your file ?

Regards.

[former_member207873]

  data: lv_file type FILENAME,
        lv_fieldcat type lvc_t_fcat,
        lv_coloumns type string,
        lt_fieldcat TYPE slis_fieldcat_alv OCCURS 0,
        ls_fieldcat like line of lt_fieldcat,
        lt_fieldcat1 TYPE slis_fieldcat_alv OCCURS 0,
        ls_fieldcat1 like line of lt_fieldcat1,
        lv_text type string,
        lv_text1 type string,
        lv_field type string.

loop at lt_fieldcat into ls_fieldcat.
lv_field = ls_fieldcat-seltext_s.
CONCATENATE lv_text lv_field INTO lv_text SEPARATED BY ','.
clear:lv_field.
endloop.


OPEN DATASET lv_file FOR OUTPUT IN TEXT MODE.
transfer lv_text to lv_file.
clear : lv_text.


  loop at it_data into wa_data.
    loop at lt_fieldcat into ls_fieldcat.
    assign component ls_fieldcat-fieldname of structure wa_data to <fs>.
    if sy-subrc = 0.
    lv_field = <fs>.
    if lv_field cn ','.
    replace ',' with space into lv_field.
    endif.
      concatenate lv_text1 lv_field into lv_text1 separated by ','.
      clear: lv_field.
    endif.
    endloop.
 transfer lv_text1 to lv_
clear: lv_text1.
endloop.

After some 60 coloumns the heading is missing. Is there any limitation with the string data type ?

[rosenberg_eitan]

Hi

Do all entries of lt_fieldcat have content in seltext_s ?

Regards.

[former_member207873]

yes. they do have . But string could only accept 255 characters. I think thats the issue.

[rosenberg_eitan]

I DL BIG_STRING see aaaa.txt

[rosenberg_eitan]

Try (for debug)

loop at lt_fieldcat into ls_fieldcat. 

lv_field = ls_fieldcat-seltext_s. 

CONCATENATE lv_text ls_fieldcat-fieldname INTO lv_text SEPARATED BY ','. 

clear:lv_field. 

endloop. 

[rosenberg_eitan]

Some code and result

CONSTANTS: c_max_cols TYPE i VALUE 200 .

  OPEN DATASET dataset_path FOR OUTPUT IN TEXT MODE ENCODING DEFAULT MESSAGE message . " Output type

* Headings
  DO c_max_cols TIMES .

    TRANSFER 'Head-' TO dataset_path NO END OF LINE .

    number = sy-index .
    TRANSFER number TO dataset_path NO END OF LINE .
    TRANSFER ','    TO dataset_path NO END OF LINE .

  ENDDO .

  TRANSFER ' ' TO dataset_path .

* Detail lines
  DO 20 TIMES .

    DO c_max_cols TIMES .

      number = sy-index .

      TRANSFER number TO dataset_path NO END OF LINE .
      TRANSFER ','    TO dataset_path NO END OF LINE .

    ENDDO .

    TRANSFER ' ' TO dataset_path .

  ENDDO .

  CLOSE DATASET dataset_path .

Rename test_data.txt as csv (site security......)

[former_member207873]

Thanks for your extended help. But I am on 4.6 C and NO END OF LINE wont work. More over I read that there is a limitation of 512 characters per line in AL11. (May be as of 4.6 C). Any other work around ?

[rosenberg_eitan]

Lets try with out NO END OF LINE.

Create a small program with my code and lets see what we get.

Regards.

[rosenberg_eitan]

What is the affect of

[former_member207873]

getting some junk data. Numbers are getting converted to '#'.

[former_member207873]

Length also i tried. Didnt have any impact . It was getting stopped at the exact position as it was before.

[rosenberg_eitan]

Sorry I am out of tricks....

[former_member207873]

Hi Eitan Rosenberg,

Thanks a lot for your help and patience. I found this article and  I think this is a standard behaviour of earlier versions of SAP (I am on 4.6 C)

Files Downloaded from AL11 are Truncated after 512 characters - ABAP Development - SCN Wiki

May be I could try the logic as mentioned by Vinod in Limit of app. server flat file length when usin... | SCN  

But again breaking coloumn heading into rows and the corresponding row data is going to be challenging.. Sigh !!!!

[rosenberg_eitan]

Hi,

mention HTML

You can create HTML table .

you can use multiple TRANSFER

I believe excel will render the file correctly .

IMHO its worth a try....

Regards.

p.s. Tell your boss its time to upgrade (a lot of money .......)

[former_member207873]

But the user wants it through AL11..

[raymond_giuseppi]

Or maybe you it is time to industrialize your communications/data exchanges with data integration tools such as Talend, Informatica, etc.

Regards,

Raymond

[former_member207873]

Raymond Giuseppi

We may not be having the luxury of these tools as of now ? Any alternative work arounds ?

[raymond_giuseppi]

Export/Import in a csv/xml/html format with the true extension and not an xls*.

Regards,

Raymond

[rosenberg_eitan]

One more trick.....

DATA: message TYPE string .
   DATA: number TYPE n LENGTH 3 .

   DATA: my_string TYPE string .

   CONSTANTS: c_max_cols TYPE i VALUE 300 .

   OPEN DATASET dataset_path FOR OUTPUT IN TEXT MODE ENCODING DEFAULT MESSAGE message . " Output type


   TRANSFER '<style>'  TO dataset_path .
   TRANSFER 'table, th, td {  border: 1px solid black;  border-collapse: collapse}' TO dataset_path .

   TRANSFER 'th {  text-decoration: underline; text-transform: capitalize; font-style: italic;}' TO dataset_path .
   TRANSFER '</style>'  TO dataset_path .

   TRANSFER '<TABLE>' TO dataset_path .

   TRANSFER '<TR>' TO dataset_path .

* Headings
   DO c_max_cols TIMES .

     TRANSFER '<TH>' TO dataset_path .

     number = sy-index .

     CONCATENATE 'Head-' number INTO my_string RESPECTING BLANKS .

     TRANSFER my_string TO dataset_path  .

     TRANSFER '</TD>' TO dataset_path .

   ENDDO .

   TRANSFER '</TR>' TO dataset_path .

* Detail lines
   DO 20 TIMES .

     TRANSFER '<TR>' TO dataset_path .

     DO c_max_cols TIMES .

       TRANSFER '<TD>' TO dataset_path .

       number = sy-index .

       TRANSFER number TO dataset_path .

       TRANSFER '</TD>' TO dataset_path .

     ENDDO .

     TRANSFER '</TR>' TO dataset_path .

   ENDDO .

   TRANSFER '</TABLE>' TO dataset_path .

   CLOSE DATASET dataset_path .

html :

Excel:

[raymond_giuseppi]

An old snippet I wrote back in the 46c days...

DATA:   ls_string     TYPE string,
        ls_item       TYPE string,
        lt_mailtext   TYPE soli_tab. " ...
*&---------------------------------------------------------------------------
* Define some macros
DEFINE clear_html.
  refresh lt_mailtext.
END-OF-DEFINITION.
DEFINE append_html.
  if ls_string is initial.
    ls_string = &1.
  else.
    concatenate ls_string &1 into ls_string.
  endif.
  if strlen( ls_string ) ge 255. " length( soli ) .
    ls_mailtest-line = ls_string.
    append ls_mailtest to lt_mailtext.
    shift ls_string by 255 places left.
  endif.
END-OF-DEFINITION.
DEFINE close_html.
  if ls_string is not initial.
    ls_mailtest-line = ls_string.
    append ls_mailtest to lt_mailtext.
    clear ls_string.
  endif.
END-OF-DEFINITION.
*&---------------------------------------------------------------------------
* Get information from an ALV field catalog
CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
  EXPORTING
    i_structure_name = lv_structure_name
  CHANGING
    ct_fieldcat      = lt_fieldcat
  EXCEPTIONS
    OTHERS           = 0.
ls_variant-report = p_report.
ls_variant-variant = i_varian.
CALL FUNCTION 'LVC_VARIANT_SELECT'
  EXPORTING
    i_dialog            = ' '
    i_user_specific     = ' '
    i_default           = 'X'
    it_default_fieldcat = lt_fieldcat
  IMPORTING
    et_fieldcat         = lt_fieldcat
  TABLES
    it_data             = ct_data
  CHANGING
    cs_variant          = ls_variant
  EXCEPTIONS
    OTHERS              = 0.
DELETE lt_fieldcat WHERE no_out IS NOT INITIAL.
DELETE lt_fieldcat WHERE fieldname EQ 'SMTP_ADDR'.
SORT lt_fieldcat BY row_pos col_pos.
* Build header
clear_html.
append_html:
  '<table border="1"><tbody><tr>'. " fell free to add options
LOOP AT lt_fieldcat INTO ls_field.
  append_html: '<th>'.
  IF ls_field-dd_outlen LT 11.
    append_html: ls_field-scrtext_s.
  ELSEIF ls_field-dd_outlen LT 11.
    append_html: ls_field-scrtext_m.
  ELSE.
    append_html: ls_field-scrtext_l.
  ENDIF.
  append_html: '</th>'.
ENDLOOP.
append_html: '</tr>'.
* For each item in a loop
LOOP AT ct_data ASSIGNING <itab>.
  LOOP AT lt_fieldcat INTO ls_field.
    ASSIGN COMPONENT ls_field-fieldname OF STRUCTURE <itab> TO <field>.
    append_html: '<td>'.
    CLEAR ls_item.
    CASE ls_field-domname.
      " Checkbox
      WHEN 'XFELD'.
        IF <field> IS INITIAL.
          ls_item = '☐'.
        ELSE.
          ls_item = '☑'.                             " or 2612
        ENDIF.
      " standard fields
      WHEN OTHERS.
        IF <field> IS INITIAL. 
          CLEAR ls_item.
        ELSE.
          WRITE <field> TO ls_char LEFT-JUSTIFIED.
          ls_item = ls_char.
        ENDIF.
    ENDCASE.
    CONDENSE ls_item.
    IF ls_item IS INITIAL.
      append_html: ' '. " else no cell generated
    ELSE.
      append_html: ls_item.
    ENDIF.
    append_html: '</td>'.
  ENDLOOP.
  append_html: '</tr>'.
ENDLOOP.
" end
append_html:  '</tbody></table>'.
close_html.

Regards,

Raymond

[former_member207873]

Raymond Giuseppi

I always wanted to ask but restrained from asking you 'maestro', how did you build this much knowledge ? Some times I feel like  you know everything under the sun. You are always hiding behind an avatar. It would be great to see a blog from you saying, how you grew up in your career,technical tips and how to stay inquisitive ?