excel download needs to be justified

[Former Member]

Hi All,

In LFA1 table field PSTLZ is of 10 char. You can find some of the Postal codes as 94305 for STANFORD, 97204 for PORTLAND , 94258-0501 for SACRAMENTO , KZA-3TL for XYZ and so on.

I am downloading my report output in to spread sheet via a selection screen parameter.

When I download all the data for fields are fine except PSTLZ (Postal Code).

i.e. 94305 comes as right justified , 94258-0501 as left justified , KZA-3TL

as left justified and 97204 as right justified.

Is there any way I can make all of them left justified through my report coding.

[Former Member]

Hi,

What you are seeing is Excel treating the value as either number or character. To treat every value as character prefix it with a single quote ' before the value.

Regards,

Nick

[Former Member]

Hi Nick,

Thnx for your reply...

how can i declare single quotes and prefix it to my variable.

CONSTANTS: c_prefix VALUE ' ' '.

this shows error.

[Former Member]

Hi,

There is one quote missing, correct is:

CONSTANTS: c_prefix VALUE ''''.

i.e. 4 single quotes. Every pair of single quotes is replaced with one single quote in the output.

[Former Member]

Hi mark,

I used that but when i use download to excel single quotes comes before the postal code

i need to remove that.

you wld be more clear if you read my requirement

[Former Member]

Hi,

Did you concatenate the single quotes with space? In general for the fields starting with quotes will be considered as a text in excel.

Regards,

Vikranth

[Former Member]

Hi Vikranth,

Here's my code:-

CONSTANTS: c_prefix  value ''''.
DATA : z_pstlz TYPE string.
LOOP AT i_lfa1.
      z_pstlz = i_lfa1-pstlz.
      CONDENSE z_pstlz NO-GAPS.
      IF  z_pstlz CO '0123456789' .
        CONCATENATE c_prefix i_lfa1-pstlz INTO i_lfa1-pstlz.
      ENDIF.

IF i_lfa1-ort01 NE space .
          it_excel-field1  = i_lfa1-ort01 .
          APPEND it_excel.
          CLEAR it_excel.
        ENDIF.
        IF i_lfa1-regio NE space .
          it_excel-field1  = i_lfa1-regio .
          APPEND it_excel.
          CLEAR it_excel.
        ENDIF.
        IF i_lfa1-pstlz NE space .
          it_excel-field1  = i_lfa1-pstlz .
          APPEND it_excel.
          CLEAR it_excel.
        ENDIF.

 LOOP AT it_excel.
    CONCATENATE
       it_excel-field1
     
     INTO t_line-line
           SEPARATED BY c_delim.
    APPEND t_line.
  ENDLOOP.

but it stll gets quotes infornt of postal code like '93445. i want it to be 93445 in excel and left aligned.

[Former Member]

Hi,

Which FM are you using to download into excel? Which internal table are you passing to be downloaded?

Regards,

Vikranth

[Former Member]

***********************TYPES Declaration *******************************

TYPES: t_zzidwnld_filepath1 LIKE sy-entry, "max 72 chars

BEGIN OF t_zzidwnld_data1,

line(1280),

END OF t_zzidwnld_data1.

********************CONSTANTS Declaration ******************************

CONSTANTS : c_delim(1) TYPE x VALUE '09'. "ascii code for tab

DATA: BEGIN OF it_excel OCCURS 0 ,

field1(35),

field2(35),

END OF it_excel .

DATA: t_line TYPE t_zzidwnld_data1 OCCURS 0 WITH HEADER LINE.

DATA : v_lifnr1 LIKE lfa1-lifnr .

********end of change Sudhir ASR 19386

• these variables is used to pass values from select statements

DATA : v_lifnr LIKE lfa1-lifnr,

v1_lifnr LIKE lfa1-lifnr,

v2_lifnr LIKE lfa1-lifnr,

v_name1 LIKE lfa1-name1,

v_name2 LIKE lfa1-name2,

v_konzs LIKE lfa1-konzs,

v_name3 LIKE lfa1-name3,

v_name4 LIKE lfa1-name4,

v_stras LIKE lfa1-stras,

v_ort01 LIKE lfa1-ort01,

v_regio LIKE lfa1-regio,

v_pstlz LIKE lfa1-pstlz,

v_landx LIKE t005t-landx,

v_land1 LIKE lfa1-land1,

v_ktokk LIKE lfa1-ktokk,

v_verkf LIKE lfm1-verkf,

v_zterm LIKE lfb1-zterm,

v1_bukrs LIKE lfb1-bukrs,

v1_zterm LIKE lfb1-zterm,

v_parvw LIKE wyt3-parvw,

v_lifn2 LIKE wyt3-lifn2,

v_pabez LIKE tpaum-pabez,

v_sortl LIKE lfa1-sortl,

vs_telf1 LIKE lfm1-telf1,

v_telf1 LIKE lfa1-telf1,

v_telf2 LIKE lfa1-telf2,

v_telfx LIKE lfa1-telfx,

v_telx1 LIKE lfa1-telx1.

DATA : z_pstlz TYPE string.

----

---

*/ Program Selections *

*/ *

----

---

• SELECT-OPTIONS: S_______ FOR _____________. *

• PARAMETERS: P_______ LIKE _____________. *

----

---

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-111.

SELECT-OPTIONS: s1_lifnr FOR lfa1-lifnr MATCHCODE OBJECT kred,

*Code Added BY RKSHARMA For ASR#23796 on Dated 05/30/2007

s1_name1 FOR lfa1-name1,

*End Of Code Added BY RKSHARMA For ASR#23796 on Dated 05/30/2007

s1_ktokk FOR lfa1-ktokk,

s1_erdat FOR lfa1-erdat.

SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-222.

SELECT-OPTIONS s1_bukrs FOR lfb1-bukrs .

*****start of change Sudhir ASR 19386

PARAMETERS : p_date LIKE sy-datum .

******end of change Sudhir ASR 19386

*Code Added BY RKSHARMA For ASR#23796 on Dated 05/30/2007

PARAMETERS : p1_loevm AS CHECKBOX,

p1_sperm AS CHECKBOX.

*End Of Code Added BY RKSHARMA For ASR#23796 on Dated 05/30/2007

SELECTION-SCREEN END OF BLOCK b2.

SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE text-333.

PARAMETERS: p1_sort LIKE rfpdo-kkvzksor DEFAULT '1'.

PARAMETERS: p1_ml RADIOBUTTON GROUP rb1 .

*****start of change Sudhir ASR 19386

SELECTION-SCREEN BEGIN OF LINE .

SELECTION-SCREEN COMMENT 1(27) text-100 .

SELECTION-SCREEN POSITION 33 .

PARAMETERS: p1_e1 RADIOBUTTON GROUP rb1 .

SELECTION-SCREEN COMMENT 40(20) text-101 .

SELECTION-SCREEN POSITION 63 .

PARAMETERS: px_c1 AS CHECKBOX .

SELECTION-SCREEN END OF LINE .

PARAMETERS: p_file TYPE rlgrap-filename DEFAULT 'C:\TEMP\ZPRC0340.XLS',

*****end of change Sudhir ASR 19386

p1_fl RADIOBUTTON GROUP rb1.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN POSITION 33.

PARAMETERS: p1_rol RADIOBUTTON GROUP rb1.

SELECTION-SCREEN COMMENT 1(29) text-044 FOR FIELD p1_rol.

SELECTION-SCREEN POSITION 69.

PARAMETERS:

p1_ppv LIKE box1 DEFAULT c_box2.

SELECTION-SCREEN COMMENT 48(20) text-055 FOR FIELD p1_ppv.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK b3.

INITIALIZATION.

i_sort-text2 = 'Sorted by :'.

*****start of change Sudhir ASR 19386

s1_ktokk-low = 'ONET'.

s1_ktokk-option = 'EQ'.

s1_ktokk-sign = 'I' .

APPEND s1_ktokk.

CLEAR s1_ktokk.

s1_ktokk-low = 'VEND'.

s1_ktokk-option = 'EQ'.

s1_ktokk-sign = 'I' .

APPEND s1_ktokk.

CLEAR s1_ktokk.

[Former Member]

OK, this turns out to be an Excel quirk...

Inside an Excel worksheet you put a single quote in front of a number to left-justify it, but this does not work when you import data. As you found out, the single quote then gets displayed.

I did some Google research and testing and what you actually have to do is put an equal sign (=) in front of the numeric field.

Here is my code:

REPORT  ZTEST no standard page heading.

types: begin of ty_zip,
  zipcode    type char10,
  location   type char30,
end of ty_zip.

data: gt_zip  type table of ty_zip,
      gw_zip  like line of gt_zip.

data: csv_line type string.

define %addline.
  gw_zip-zipcode = &1.
  gw_zip-location = &2.
  append gw_zip to gt_zip.
end-of-definition.


start-of-selection.
  %addline '94305' 'STANFORD'.
  %addline '97204' 'PORTLAND'.
  %addline '94258-0501' 'SACRAMENTO'.
  %addline 'KZA-3TL' 'XYZ'.
  %addline 'B-2000' 'ANTWERP'.

  loop at gt_zip into gw_zip.
    csv_line = '="&ZIP","&LOC"'.
    replace: '&ZIP' with gw_zip-zipcode into csv_line,
             '&LOC' with gw_zip-location into csv_line.
    condense csv_line.
    write: / csv_line.
  endloop.

The output looks like this:

="94305 ","STANFORD "
="97204 ","PORTLAND "
="94258-0501","SACRAMENTO "
="KZA-3TL ","XYZ "
="B-2000 ","ANTWERP "

When I save this in a CSV file and import into Excel, the numeric zipcodes are neatly left-aligned.

94305          STANFORD 
97204          PORTLAND 
94258-0501     SACRAMENTO 
KZA-3TL        XYZ 
B-2000         ANTWERP

Try it out and let us know.

Regards,

Mark

[Former Member]

Hi All,

below is my code. i want field pstlz to be left justified in excel download

* Tables ------------------------------------------------------------- *
TABLES : lfa1,                         "Vendor master (general section).
         lfb1,                         "Vendor master (company code).
         wyt3,                         "Partner Functions.
         lfm1, "Vendor master record purchasing organization data.
         t005,                         "Countries.
         t005t,                        "Country names.
         ekko ,        "Purchasing Document Header   ASR 19386
         tpaum. "Business partner:Language conversion for partner fns.
*----------------------------------------------------------------------*
**Data:                                                                *
**  Constants       (C_...)                                            *
**  Variables       (V_...)                                            *
**  Internal Tables (T_...)                                            *
*----------------------------------------------------------------------*

DATA : box1(1) TYPE c .
DATA : var LIKE sy-index,
       wind(5),
       text(5).
DATA: one(30) TYPE c,
      two(40) TYPE c,
      three(47) TYPE c,
      four(47) TYPE c,
      five(45) TYPE c,
      six(57) TYPE c,
      serch1(2) TYPE c.

CONSTANTS : g VALUE ' '.
CONSTANTS: c_box2(1) TYPE c VALUE 'N'.
CONSTANTS: vend(14) TYPE c VALUE 'Vendor Account',
           comp(12) TYPE c VALUE 'Company Code',
           coun(7) TYPE c VALUE 'Country',
           acct(13) TYPE c VALUE 'Account Group',
           sear(11) TYPE c VALUE 'Search Term',
           post(11) TYPE c VALUE 'Postal Code',
           city1(4) TYPE c VALUE 'City',
           slch(1) TYPE c VALUE '/'.

CONSTANTS: c_prefix VALUE '='.
* This internal table is used for displaying date and type etc of labels
* on the heading.

DATA: BEGIN OF i_sort OCCURS 0,
     text(57) TYPE c ,
     text2(12) TYPE c,
     type(15) TYPE c,
     ind(10) TYPE c,
     date LIKE sy-datum,
END OF i_sort.
* used for vendor list
DATA: BEGIN OF i_lfa1 OCCURS 0 ,
      name1(35) TYPE c ,                                    "Name1.
      slach(1) TYPE c,
      lifnr LIKE lfa1-lifnr,  "Vendor (creditor) account number.
      name2 LIKE lfa1-name2,                                "Name2.
      konzs LIKE lfa1-konzs,           "Group key.
      name3 LIKE lfa1-name3,                                "Name3.
      name4 LIKE lfa1-name4,                                "Name4.
      stras LIKE lfa1-stras,           "Street and house number.
      ort01 LIKE lfa1-ort01,           "City.
      comma(1) TYPE c,
      regio LIKE lfa1-regio,  "Region (State, Province, County).
      pulstop(1) TYPE c,
      pstlz LIKE lfa1-pstlz,           "Postal code.
      landx LIKE t005t-landx,          "Country Name.
      attn(5) TYPE c,
      verkf LIKE lfm1-verkf,  "Responsible salesperson at vendor's offic
      tel(4) TYPE c,
      telf11 LIKE lfm1-telf1,  "sales person telephone
      tel1(5) TYPE c,
      telf1 LIKE lfa1-telf1,  "sales person telephone
      tel2(5) TYPE c,
      telf2 LIKE lfa1-telf2,  "sales person telephone
      fax(4) TYPE c,
      telfx LIKE lfa1-telfx,  "sales person fax
      email(7) TYPE c,
      telx1 LIKE lfa1-telx1,  "sales person e-mail
      actype(10) TYPE c,
      ktokk LIKE lfa1-ktokk,           "Vendor account group.
      pay(11) TYPE c,
      zterm LIKE lfm1-zterm,           "Terms of payment key.
      sortl LIKE lfa1-sortl,
      bukrs LIKE lfb1-bukrs,     "Company Code ASR 19386
END OF i_lfa1.
* used for partner vendor list
DATA: BEGIN OF ip_lfa1 OCCURS 0 ,
      lifnr LIKE lfa1-lifnr,  "Vendor (creditor) account number.
      name1 LIKE lfa1-name1,                                "Name1.
      slach(1) TYPE c,
      pabez LIKE tpaum-pabez, "Language-specific desc of partner fn.
      lifn2 LIKE wyt3-lifn2,           "Reference to other vendor.
      name2 LIKE lfa1-name2,                                "Name2.
      konzs LIKE lfa1-konzs,           "Group key.
      name3 LIKE lfa1-name3,                                "Name3.
      name4 LIKE lfa1-name4,                                "Name4.
      stras LIKE lfa1-stras,           "Street and house number.
      ort01 LIKE lfa1-ort01,           "City.
      comma(1) TYPE c,
      regio LIKE lfa1-regio,  "Region (State, Province, County).
      pulstop(1) TYPE c,
      pstlz LIKE lfa1-pstlz,           "Postal code.
      landx LIKE t005t-landx,          "Country name.
      attn(5) TYPE c,
      verkf LIKE lfm1-verkf,  "Responsible salesperson at vendor's offic
       tel(4) TYPE c,
      telf11 LIKE lfm1-telf1,  "sales person telephone
       tel1(5) TYPE c,
      telf1 LIKE lfa1-telf1,  "sales person telephone
       tel2(5) TYPE c,
      telf2 LIKE lfa1-telf2,  "sales person telephone
      fax(4) TYPE c,
      telfx LIKE lfa1-telfx,  "sales person fax
       email(7) TYPE c,
      telx1 LIKE lfa1-telx1,  "sales person e-mail
      actype(10) TYPE c,
      ktokk LIKE lfa1-ktokk,           "Vendor account group.
      pay(11) TYPE c,
      zterm LIKE lfm1-zterm,           "Terms of payment key.
      bukrs LIKE lfb1-bukrs,    "Company Code     ASR 19386
END OF ip_lfa1. 

[Sandra_Rossi]

Great solution, Mark. I thought the single quote before the digits was working with CSV files, but it doesn't. ="value" works very well for me (office 2003).