Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Issue related to Excel to Text tab delimited conversion

Former Member
0 Kudos

Hi,

I am facing a strange issue in converting an excel file to a text tab delimited file. I have an excel sheet which contains two columns:

vendor new_zip

153357 99645-6340

162642 99669-7631

$209930-1 91320-1201

$209989-1 91710-5766

When i save this file as a text tab delimited file this is how it turns into:

vendor new_zip

153357 99645-6340

162642 99669-7631

$209930-1 91320-1201

$209989-1 91710-5766

i am using WS_UPLOAD since i am not using the enterprise version of SAP. In the above mentioned u can see that the zip values(first character) of the first two records and the vendor numbers(last character) of the last two records get in line with each other. so when i load this file it gives a "File open error" exception. if i move the zip codes of the first two records by one space so that they are not in line with the vendor numbers of the last two records the file uploading happens fine. but this is something which cant be done from the user point of view. so can anyone tell as to what can be done without changing the concept of using text tab delimited file and aviod this problem?

thanks & regards,

Bala.

5 REPLIES 5

Former Member
0 Kudos

Hi Bala,

Try using comma delimited file.

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

Why not save it as a comma delimited text file, then when you upload, upload it to a flat ITAB.

data: begin of itab occurs 0,
      rec(500) type c,
      end of itab.

Then you can loop at the internal table and use the SPLIT statement to get the values of the individual fields.

data: begin of vendor occurs 0,
      number(10) type c,
      zip(10) type c,
      end of vendor.


Loop at itab.

split itab-rec at ',' into vendor-number
                           vendor-zip.
append vendor.

endloop.

REgards,

Rich Heilman

Former Member
0 Kudos

Sorry i didnt know how it got posted twice, but as i had said earlier can you provide a solution for the tab delimited file rather than using the comma seperated file.

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

Sure, I guess you can try doing it with tab.

data: begin of vendor occurs 0,
      number(10) type c,
      zip(10) type c,
      end of vendor.

<b>CONSTANTS:con_tab  TYPE c VALUE cl_abap_char_utilities=>horizontal_tab.</b>


Loop at itab.

split itab-rec at <b>con_tab</b> into vendor-number
                               vendor-zip.
append vendor.

endloop.

REgards,

Rich Heilman

Former Member
0 Kudos

The following program uses alsm_convert_excel_to_internal_table.but i want to use split at tab delimiter.pls help.

REPORT ZSAINAL_BDC_TRAN .

*INCLUDE BDCRECX1.

**********************************************************

*definition of selection screen

**********************************************************

SELECTION-SCREEN BEGIN OF BLOCK bl1 WITH FRAME .

SELECTION-SCREEN BEGIN OF BLOCK bl2 WITH FRAME TITLE text-t01.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 10(20) text-c01.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 10(10) text-c02.

SELECTION-SCREEN POSITION 22.

PARAMETERS: p_txt RADIOBUTTON GROUP grp1 DEFAULT 'X'.

SELECTION-SCREEN COMMENT 30(12) text-c03.

SELECTION-SCREEN POSITION 46.

PARAMETERS: p_xls RADIOBUTTON GROUP grp1 .

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN END OF BLOCK bl2.

**********************************************************

*PARAMETERS

**********************************************************

PARAMETERS ctumode LIKE ctu_params-dismode DEFAULT 'A'.

PARAMETERS cupdate LIKE ctu_params-updmode DEFAULT 'S'.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN END OF BLOCK bl1.

**********************************************************

*DATA

**********************************************************

DATA : bdc_data LIKE STANDARD TABLE OF bdcdata INITIAL SIZE 10

WITH HEADER LINE.

*DATA: bdc_tab LIKE TABLE OF bdcdata INITIAL SIZE 0 WITH HEADER LINE.

**********************************************************

*Internal table to get legacy data records structured in

*target format

**********************************************************

DATA: BEGIN OF inrec OCCURS 0,

lifnr LIKE lfa1-lifnr,

name1 LIKE lfa1-name1,

stras LIKE lfa1-stras,

ort01 LIKE lfa1-ort01,

END OF inrec.

**********************************************************

*Internal table of structure ALSMEX_TABLINE suitable for

*uploading records from an Excel worksheet

**********************************************************

DATA: it_excel TYPE STANDARD TABLE OF alsmex_tabline

INITIAL SIZE 0 WITH HEADER LINE,

it_excel_dummy TYPE alsmex_tabline.

data: messtab like bdcmsgcoll occurs 0 with header line.

DATA: L_MSTRING(480).

DATA: L_SUBRC LIKE SY-SUBRC.

**********************************************************

*START-OF-SELECTION

**********************************************************

START-OF-SELECTION.

PERFORM read_data.

PERFORM populate_bdcdata.

***********************************************************

*START NEW SCREEN

**********************************************************

FORM bdc_dynpro USING program dynpro.

CLEAR bdc_data.

bdc_data-program = program.

bdc_data-dynpro = dynpro.

bdc_data-dynbegin = 'X'.

APPEND bdc_data.

ENDFORM.

**********************************************************

*INSERT FIELD

**********************************************************

FORM bdc_field USING fnam fval.

  • IF FVAL <> NODATA.

CLEAR bdc_data.

bdc_data-fnam = fnam.

bdc_data-fval = fval.

APPEND bdc_data.

  • ENDIF.

ENDFORM.

&----


*& Form read_data

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM read_data.

IF p_txt = 'X'.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = 'C:\URMILA.TXT'

filetype = 'ASC'

has_field_separator = 'X'

  • HEADER_LENGTH = 0

  • READ_BY_LINE = 'X'

  • IMPORTING

  • FILELENGTH =

  • HEADER =

TABLES

data_tab = inrec

EXCEPTIONS

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

OTHERS = 17

.

IF sy-subrc <> 0.

WRITE: /'ERROR UPLOADING TXT FILE FROM PRESENTATION SERVER!',

/ 'RETURN CODE : ',sy-subrc.

  • MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

  • WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ELSE. "filetype is excel

-read excel file into an int. table in row/col format--


CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = 'C:\URMILA.XLS'

i_begin_col = 1

i_begin_row = 1

i_end_col = 4

i_end_row = 3

TABLES

intern = it_excel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

WRITE: /'ERROR UPLOADING XLS FILE FROM PRESENTATION SERVER!',

/ 'RETURN CODE : ',sy-subrc.

  • MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

  • WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ELSE.

--now fill data from it_excel into final legacy data itabinrec----

IF NOT it_excel[] IS INITIAL.

CLEAR inrec.

REFRESH inrec[].

LOOP AT it_excel.

it_excel_dummy = it_excel.

AT NEW col.

CASE it_excel_dummy-col.

WHEN 1.

inrec-lifnr = it_excel_dummy-value(10).

WHEN 2.

inrec-name1 = it_excel_dummy-value(35).

WHEN 3.

inrec-stras = it_excel_dummy-value(35).

WHEN 4.

inrec-ort01 = it_excel_dummy-value(35).

APPEND inrec.

CLEAR inrec.

ENDCASE.

ENDAT.

  • AT END OF row.

  • ENDAT.

ENDLOOP.

ENDIF.

ENDIF.

ENDIF.

ENDFORM.

&----


*& Form populate_bdcdata

&----


  • to populate BDCDATA table from legacy file data contained in

  • internal table inrec.

----


  • --> p1 text

  • <-- p2 text

----


FORM populate_bdcdata.

IF not inrec[] IS INITIAL.

LOOP AT inrec.

CLEAR bdc_data.

REFRESH bdc_data.

PERFORM bdc_dynpro USING 'SAPMF02K' '0106'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'RF02K-LIFNR'

inrec-lifnr.

PERFORM bdc_field USING 'RF02K-BUKRS'

'1000'.

PERFORM bdc_field USING 'RF02K-D0110'

'X'.

PERFORM bdc_dynpro USING 'SAPMF02K' '0110'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=PF03'.

PERFORM bdc_field USING 'LFA1-NAME1'

inrec-name1.

PERFORM bdc_field USING 'LFA1-SORTL'

'ALLGEMEIN'.

PERFORM bdc_field USING 'LFA1-STRAS'

inrec-stras.

PERFORM bdc_field USING 'LFA1-PFACH'

'645793'.

PERFORM bdc_field USING 'LFA1-ORT01'

inrec-ort01.

PERFORM bdc_field USING 'LFA1-PSTLZ'

'12001'.

PERFORM bdc_field USING 'LFA1-ORT02'

'COOK'.

PERFORM bdc_field USING 'LFA1-PSTL2'

'76905'.

PERFORM bdc_field USING 'LFA1-LAND1'

'DE'.

PERFORM bdc_field USING 'LFA1-REGIO'

'11'.

PERFORM bdc_field USING 'LFA1-SPRAS'

'DE'.

PERFORM bdc_field USING 'LFA1-TELF1'

'06894/55501-0'.

PERFORM bdc_field USING 'LFA1-TELFX'

'06894/55501-100'.

PERFORM bdc_dynpro USING 'SAPLSPO1' '0100'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=YES'.

CALL TRANSACTION 'FK02'

USING bdc_data

MODE ctumode

UPDATE cupdate

MESSAGES INTO MESSTAB.

ENDLOOP.

perform mess_handling.

ENDIF.

ENDFORM. " populate_bdcdata

&----


*& Form mess_handling

&----


  • ERROR MESSAGE HANDLING IN CALL TRANACTION

----


  • --> p1 text

  • <-- p2 text

----


form mess_handling.

tables:T100.

LOOP AT MESSTAB.

SELECT SINGLE * FROM T100 WHERE SPRSL = MESSTAB-MSGSPRA

AND ARBGB = MESSTAB-MSGID

AND MSGNR = MESSTAB-MSGNR.

IF SY-SUBRC = 0.

L_MSTRING = T100-TEXT.

IF L_MSTRING CS '&1'.

REPLACE '&1' WITH MESSTAB-MSGV1 INTO L_MSTRING.

REPLACE '&2' WITH MESSTAB-MSGV2 INTO L_MSTRING.

REPLACE '&3' WITH MESSTAB-MSGV3 INTO L_MSTRING.

REPLACE '&4' WITH MESSTAB-MSGV4 INTO L_MSTRING.

ELSE.

REPLACE '&' WITH MESSTAB-MSGV1 INTO L_MSTRING.

REPLACE '&' WITH MESSTAB-MSGV2 INTO L_MSTRING.

REPLACE '&' WITH MESSTAB-MSGV3 INTO L_MSTRING.

REPLACE '&' WITH MESSTAB-MSGV4 INTO L_MSTRING.

ENDIF.

CONDENSE L_MSTRING.

WRITE: / MESSTAB-MSGTYP, L_MSTRING(250).

ELSE.

WRITE: / MESSTAB.

ENDIF.

ENDLOOP.

endform. " mess_handling