Converting Excel to Tab Delimited

[Former Member]

hello all,

I need to write a program in ABAP that will take in Excel file and convert it to a Tab Delimited file.

How can I go about doing that?

Thanks in advance.

Regards,

Fred.

[former_member181962]

1) Upload the excel file into sap using gui_upload.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = 'C:/TEST.XLS'

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

TABLES

data_tab = l_tab

EXCEPTIONS

conversion_error = 1

file_open_error = 2

file_read_error = 3

invalid_type = 4

no_batch = 5

unknown_error = 6

invalid_table_width = 7

gui_refuse_filetransfer = 8

customer_error = 9

OTHERS = 10.

2) DOWNLOAD USING GUI_DOWNLOAD

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

FILENAME = 'C:/ NEW.TXT'

FILETYPE = 'BIN'

HAS_FIELD_SEPARATOR = 'X'.

TABLES

DATA_TAB = L_tab

EXCEPTIONS

FILE_WRITE_ERROR = 1

NO_BATCH = 2

GUI_REFUSE_FILETRANSFER = 3

INVALID_TYPE = 4

NO_AUTHORITY = 5

UNKNOWN_ERROR = 6

HEADER_NOT_ALLOWED = 7

SEPARATOR_NOT_ALLOWED = 8

FILESIZE_NOT_ALLOWED = 9

HEADER_TOO_LONG = 10

DP_ERROR_CREATE = 11

DP_ERROR_SEND = 12

DP_ERROR_WRITE = 13

UNKNOWN_DP_ERROR = 14

ACCESS_DENIED = 15

DP_OUT_OF_MEMORY = 16

DISK_FULL = 17

DP_TIMEOUT = 18

FILE_NOT_FOUND = 19

DATAPROVIDER_EXCEPTION = 20

CONTROL_FLUSH_ERROR = 21

OTHERS = 22.

[Former Member]

i'm sorry, i want to correct the subject. It should be 'Converting Excel file to Fixed Length format'

Ravi, how will this convert the excel file to fixed length?

I need to write a logic in one program itself that will take in excel file and convert it to a fixed length file. This will be a common program used by anybody who wants to convert files. Excel file will be given in and result will come out as a fixed length file.

thanks.

[former_member927251]

Hi Fread,

1. Take the Excel Data into an internal table. Refer the code below.

REPORT zexceltabc.

PARAMETERS: filename LIKE rlgrap-filename,

begcol TYPE i DEFAULT 1 NO-DISPLAY,

begrow TYPE i DEFAULT 1 NO-DISPLAY,

endcol TYPE i DEFAULT 100 NO-DISPLAY,

endrow TYPE i DEFAULT 32000 NO-DISPLAY.

PARAMETERS: kzheader AS CHECKBOX.

DATA: BEGIN OF intern OCCURS 0.

INCLUDE STRUCTURE alsmex_tabline.

DATA: END OF intern.

DATA: BEGIN OF intern1 OCCURS 0.

INCLUDE STRUCTURE alsmex_tabline.

DATA: END OF intern1.

DATA: BEGIN OF t_col OCCURS 0,

col LIKE alsmex_tabline-col,

size TYPE i.

DATA: END OF t_col.

DATA: zwlen TYPE i,

zwlines TYPE i.

DATA: BEGIN OF fieldnames OCCURS 3,

title(60),

table(6),

field(10),

kz(1),

END OF fieldnames.

DATA: BEGIN OF data_tab OCCURS 0,

value_0001(50),

value_0002(50),

value_0003(50),

value_0004(50),

value_0005(50),

value_0006(50),

value_0007(50),

value_0008(50),

value_0009(50),

value_0010(50),

value_0011(50),

value_0012(50),

value_0013(50),

value_0014(50),

value_0015(50),

value_0016(50),

value_0017(50),

value_0018(50),

value_0019(50),

value_0020(50),

value_0021(50),

value_0022(50),

value_0023(50),

value_0024(50),

value_0025(50),

value_0026(50),

value_0027(50),

value_0028(50),

value_0029(50),

value_0030(50),

value_0031(50),

value_0032(50),

value_0033(50),

value_0034(50),

value_0035(50),

value_0036(50),

value_0037(50),

value_0038(50),

value_0039(50),

value_0040(50),

value_0041(50),

value_0042(50),

value_0043(50),

value_0044(50),

value_0045(50),

value_0046(50),

value_0047(50),

value_0048(50),

value_0049(50),

value_0050(50),

value_0051(50),

value_0052(50),

value_0053(50),

value_0054(50),

value_0055(50),

value_0056(50),

value_0057(50),

value_0058(50),

value_0059(50),

value_0060(50),

value_0061(50),

value_0062(50),

value_0063(50),

value_0064(50),

value_0065(50),

value_0066(50),

value_0067(50),

value_0068(50),

value_0069(50),

value_0070(50),

value_0071(50),

value_0072(50),

value_0073(50),

value_0074(50),

value_0075(50),

value_0076(50),

value_0077(50),

value_0078(50),

value_0079(50),

value_0080(50),

value_0081(50),

value_0082(50),

value_0083(50),

value_0084(50),

value_0085(50),

value_0086(50),

value_0087(50),

value_0088(50),

value_0089(50),

value_0090(50),

value_0091(50),

value_0092(50),

value_0093(50),

value_0094(50),

value_0095(50),

value_0096(50),

value_0097(50),

value_0098(50),

value_0099(50),

value_0100(50).

DATA: END OF data_tab.

DATA: tind(4) TYPE n.

DATA: zwfeld(19).

FIELD-SYMBOLS: 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

END-OF-SELECTION.

• max. Feldlänge je Spalte ermitteln...

LOOP AT intern.

intern1 = intern.

CLEAR intern1-row.

APPEND intern1.

ENDLOOP.

SORT intern1 BY col.

LOOP AT intern1.

AT NEW col.

t_col-col = intern1-col.

APPEND t_col.

ENDAT.

zwlen = strlen( intern1-value ).

READ TABLE t_col WITH KEY col = intern1-col.

IF sy-subrc EQ 0.

IF zwlen > t_col-size.

t_col-size = zwlen.

MODIFY t_col INDEX sy-tabix.

ENDIF.

ENDIF.

ENDLOOP.

• max. Spaltenanzahl ermitteln.

DESCRIBE TABLE t_col LINES zwlines.

• wenn die 1. Zeile die Spaltenüberschriften enthält...

SORT intern BY row col.

IF kzheader = 'X'.

LOOP AT intern.

fieldnames-title = intern-value.

APPEND fieldnames.

AT END OF row. " Ende der 1. Zeile

EXIT.

ENDAT.

ENDLOOP.

ELSE.

DO zwlines TIMES.

WRITE sy-index TO fieldnames-title.

APPEND fieldnames.

ENDDO.

ENDIF.

• Data_tab füllen ...

SORT intern BY row col.

LOOP AT intern.

IF kzheader = 'X'

AND intern-row = 1.

CONTINUE.

ENDIF.

tind = intern-col.

CONCATENATE 'DATA_TAB-VALUE_' tind INTO zwfeld.

ASSIGN (zwfeld) TO = intern-value.

AT END OF row.

APPEND data_tab.

CLEAR data_tab.

ENDAT.

ENDLOOP.

CALL FUNCTION 'DISPLAY_BASIC_LIST'

EXPORTING

file_name = filename

TABLES

data_tab = data_tab

fieldname_tab = fieldnames.

2. Then use this internal table and write the data to a file.

Hope this helps.

Regards,

Amit Mishra

[Former Member]

try out this code

Now there is no need to convert the excel file to a tab

delimited file for uploading the data

* Internal Table Defination..........................

DATA: BEGIN OF JTABLE OCCURS 0,

DOCID_001(25) TYPE C, 

FIELD_002(25) TYPE C, 

FIELD_003(25) TYPE C, 

FIELD_004(25) TYPE C, 

FIELD_005(25) TYPE C, 

FIELD_006(25) TYPE C, 

FIELD_007(255) TYPE C, 

FIELD_008(25) TYPE C, 

FIELD_009(25) TYPE C, 

FIELD_010(25) TYPE C, 

MSG_011(50) TYPE C, 

END OF JTABLE.

 

* Selection Screen....................................

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

PARAMETERS: P_FILE LIKE RLGRAP-FILENAME

DEFAULT 'c:JJJJJJJJJJ.xls' OBLIGATORY. " File Name

SELECTION-SCREEN END OF BLOCK B1.

 

 

* At Selection Screen Event...........................

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

MASK = '*.xls'

STATIC = 'X'

CHANGING

FILE_NAME = P_FILE

EXCEPTIONS

MASK_TOO_LONG = 1

OTHERS = 2.

 

IF sy-subrc >> 0.

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

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

ENDIF.

 

* Start of selection Event.............................

START-OF-SELECTION.

 

PERFORM C200-UPLOAD_FILE TABLES JTABLE

USING P_FILE.

* End of selection Event...............................

end-of-selection.

 

loop at jtable.

write:/ jtable-DOCID_001,

jtable-FIELD_002,

jtable-FIELD_003,

jtable-FIELD_004,

jtable-FIELD_005,

jtable-FIELD_006,

jtable-FIELD_007,

jtable-FIELD_008,

jtable-FIELD_009,

jtable-FIELD_010,

jtable-MSG_011.

endloop.

 

FORM C200-UPLOAD_FILE TABLES P_TABLE

USING P_FILE.

* Data Declarations.......................................

DATA : L_INTERN TYPE KCDE_CELLS OCCURS 0 WITH HEADER LINE.

DATA : L_INDEX TYPE I.

DATA : L_START_COL TYPE I VALUE '1',

L_START_ROW TYPE I VALUE '1',

L_END_COL TYPE I VALUE '256',

L_END_ROW TYPE I VALUE '65536'.

 

* Field Symbols...........................................

FIELD-SYMBOLS : >FS>.

 

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = L_START_COL

I_BEGIN_ROW = L_START_ROW

I_END_COL = L_END_COL

I_END_ROW = L_END_ROW

TABLES

INTERN = L_INTERN

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

.

IF sy-subrc >> 0.

* MESSAGE E002(ZA). " File Error

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE : / 'File Error'.

EXIT.

ENDIF.

 

IF L_INTERN[] IS INITIAL.

* MESSAGE E003(ZA). " No Data Uploaded

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE : / 'No Data Uploaded'.

EXIT.

ELSE.

SORT L_INTERN BY ROW COL.

LOOP AT L_INTERN.

MOVE L_INTERN-COL TO L_INDEX.

ASSIGN COMPONENT L_INDEX OF STRUCTURE P_TABLE TO >FS>.

MOVE L_INTERN-VALUE TO <FS>.

AT END OF ROW.

APPEND P_TABLE.

CLEAR P_TABLE.

ENDAT.

ENDLOOP.

ENDIF.

 

ENDFORM.

hope this solves ur problem

Thanks & regards

Naresh

[Former Member]

i thank you all for replying quickly to my question.

Naresh, it seems this code is close to what I need. I just had one question: Is the function module 'KCD_EXCEL_OLE_TO_INT_CONVERT' going to convert the excel file to a fixed length file? And then I just loop at my internal table to display the values, right?

thanks.

[former_member181962]

This Function can be of use to you:

ALSM_EXCEL_TO_INTERNAL_TABLE

Regards,

Ravi

[Former Member]

Naresh's code is not what I am looking for.

Yes Ravi, this function that you mentioned will enable me to upload excel data into the program. That part I am fine also.

Here is what I need:

After I upload the excel file into my program, I need to somehow convert it so that it becomes a Fixed Length file. I want to know how to convert it: From excel file format to fixed length format.

Naresh's code just takes in excel file and writes it out. This is not what I need.

Thanks.

[former_member181962]

Hi Fred,

Say if the length of the data uploaded is having records of different lengths, how do you make it fixed length?

Will you find the length of the logest record and set that length as the output length?

Regards,

Ravi

[Former Member]

I can just go ahead and change the lengths in the internal table based on whatever values are coming in.

[former_member188685]

Hi,

check this link..

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

Regards

vijay