Introduction
SAP has provided us with a lot of standard programs to make our day to day life easier. The standard code suite extends from simple programs to upload or download hierarchies to more complex ones to activate inactive objects in production. The flat file manipulation programs include programs to upload or download flat file to or from the system. But this list does not include one to transpose a flat file.
There are ‘n’ numbers of ways to achieve this. What is given in this article is to consider the separator values e.g. comma (,) or colon (;) as regular expressions and derive the values nested within the separators.
Regular Expressions
In computing, a regular expression provides a concise and flexible means for "matching" (specifying and recognizing) strings of text, such as particular characters, words, or patterns of characters - Wikipedia
Generally, in a given string any value that occurs repeatedly, be it in regular or random intervals, can be termed as a regular expression. In our case, any flat-file whose values are separated by an expression e.g. comma (,) or colon (;) can be taken to contain regular expressions with the separator as the expression itself.
REGEX
The REGEX command was introduced into ABAP with NetWeaver Release 2004s. It has been widely used for manipulating character strings and to search for specific expressions in text files which are extensive.
A code sample for finding regular expression would be like,
FIND ALL OCCURRENCES OF REGEX <expression> IN <string> RESULTS <t_results>.
The above code would execute the REGEX command over the given string and store all occurrences of the ‘expression’ in an internal table
Note: A regular ‘csv’ file would not have a comma separator at the end of a record. An entry would be manually inserted in the program to identify the last value.
CODE
*&---------------------------------------------------------------------*
*& Report ZFILE_TRANSPOSE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zfile_transpose.
*DECLARATION
TYPES: BEGIN OF st_file,
rec TYPE string,
END OF st_file.
TYPES: BEGIN OF st_inter,
row_no TYPE i,
rec TYPE string,
END OF st_inter.
DATA : t_file TYPE TABLE OF st_file, "Internal table to read file
wa_file TYPE st_file,
t_outfile TYPE TABLE OF st_file,
wa_outfile TYPE st_file,
t_inter TYPE TABLE OF st_inter, "Intermidiate table to store file
wa_inter TYPE st_inter.
FIELD-SYMBOLS : <fs_file> TYPE st_file.
FIELD-SYMBOLS : <fs_inter> TYPE st_inter.
DATA : it_csv TYPE match_result_tab, "Internal table for REGEX result
wa_it_csv LIKE LINE OF it_csv.
DATA : col_count TYPE i,
row_count TYPE i,
loop_count1 TYPE i,
loop_count2 TYPE i,
col_len TYPE i,
temp1 TYPE i,
temp2 TYPE i,
temp3 TYPE i,
value TYPE string,
char_len TYPE string.
CONSTANTS: c_filetype TYPE char10 VALUE 'ASC'. "For Filetype DAT
PARAMETERS: p_file1 LIKE rlgrap-filename OBLIGATORY. "Get file loacftion
PARAMETERS: p_file2 LIKE rlgrap-filename OBLIGATORY. "File loaction for transposed data
PARAMETERS: p_sptr TYPE c OBLIGATORY. "Character used for separator
*GET FILE PATH
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file1. "Get FILE from selection screen
PERFORM get_file_path USING p_file1.
*&---------------------------------------------------------------------*
START-OF-SELECTION.
*UPLOAD DATA
PERFORM get_data USING p_file1 CHANGING t_file. "Copy FILE data to internal table
*&---------------------------------------------------------------------*
*CREATE INTERNAL TABLE TO STORE DTO DETAILS
READ TABLE t_file INTO wa_file INDEX 1.
IF sy-subrc = 0.
FIND ALL OCCURRENCES OF p_sptr IN wa_file-rec MATCH COUNT col_count.
col_count = col_count + 1. "To count number of columns
ENDIF.
LOOP AT t_file INTO wa_file.
row_count = row_count + 1. "To count number of rows
ENDLOOP.
LOOP AT t_file INTO wa_file.
loop_count1 = sy-tabix.
col_len = strlen( wa_file-rec ). "Find length of each row
FIND ALL OCCURRENCES OF REGEX p_sptr IN wa_file-rec RESULTS it_csv.
IF sy-subrc = 0.
wa_it_csv-offset = col_len. "Create entry in regex table
APPEND wa_it_csv TO it_csv. "for last comma
ELSE.
MESSAGE 'Incorrect Separator Value' TYPE 'E'.
ENDIF.
temp1 = 0.
LOOP AT it_csv INTO wa_it_csv.
temp2 = temp3 = wa_it_csv-offset.
temp2 = temp2 - temp1.
value = wa_file-rec+temp1(temp2). "Get value between commas
temp1 = temp3 + 1.
loop_count2 = sy-tabix.
IF loop_count1 = 1.
wa_inter-row_no = loop_count2. "Include row number for each record
CONCATENATE value p_sptr INTO wa_inter-rec.
APPEND wa_inter TO t_inter.
ENDIF.
IF loop_count1 > 1 AND loop_count1 < row_count. "Adding comma separation to transposed value
READ TABLE t_inter ASSIGNING <fs_inter> WITH KEY row_no = loop_count2.
IF sy-subrc = 0.
CONCATENATE <fs_inter>-rec value p_sptr INTO <fs_inter>-rec.
UNASSIGN <fs_inter>.
ENDIF.
ELSEIF loop_count1 EQ row_count. "No comma needed for last value
READ TABLE t_inter ASSIGNING <fs_inter> WITH KEY row_no = loop_count2.
IF sy-subrc = 0.
CONCATENATE <fs_inter>-rec value INTO <fs_inter>-rec.
UNASSIGN <fs_inter>.
ENDIF.
ENDIF.
ENDLOOP.
ENDLOOP.
LOOP AT t_inter INTO wa_inter.
wa_outfile-rec = wa_inter-rec.
APPEND wa_outfile TO t_outfile.
ENDLOOP.
*DOWNLOAD DATA
PERFORM put_data USING p_file2 CHANGING t_outfile. "Copy FILE data to internal table
IF sy-subrc = 0.
MESSAGE 'File transposed Succesfully' TYPE 'S'.
ENDIF.
*&---------------------------------------------------------------------*
*& Form GET_FILE_PATH
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE1 text
*----------------------------------------------------------------------*
FORM get_file_path USING p_p_file1.
DATA : wsl_upfile LIKE rlgrap-filename VALUE IS INITIAL.
wsl_upfile = p_file1.
CLEAR: p_file1.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
* FIELD_NAME = ' '
* STATIC = ' '
* MASK = ' '
* FILEOPERATION = 'R'
CHANGING
file_name = wsl_upfile
* LOCATION_FLAG = 'P'
* 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.
p_file1 = wsl_upfile.
ENDFORM. " GET_FILE_PATH
*&---------------------------------------------------------------------*
*& Form GET_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE1 text
* <--P_T_FILE text
*----------------------------------------------------------------------*
FORM get_data USING p_p_file1
CHANGING p_t_file TYPE table.
DATA: l_file TYPE string.
l_file = p_p_file1.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = l_file
filetype = c_filetype
TABLES
data_tab = p_t_file
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 IS INITIAL.
ELSE.
MESSAGE s001(00) WITH 'File not found'.
STOP.
ENDIF.
CLEAR l_file.
ENDFORM. " GET_DATA
*&---------------------------------------------------------------------*
*& Form PUT_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE2 text
* <--P_T_OUTFILE text
*----------------------------------------------------------------------*
FORM put_data USING p_p_file2
CHANGING p_t_outfile TYPE table.
DATA: l_outfile TYPE string.
l_outfile = p_p_file2.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
* BIN_FILESIZE =
filename = l_outfile
filetype = c_filetype
* APPEND = ' '
* WRITE_FIELD_SEPARATOR = ' '
* HEADER = '00'
* TRUNC_TRAILING_BLANKS = ' '
* WRITE_LF = 'X'
* COL_SELECT = ' '
* COL_SELECT_MASK = ' '
* DAT_MODE = ' '
* CONFIRM_OVERWRITE = ' '
* NO_AUTH_CHECK = ' '
* CODEPAGE = ' '
* IGNORE_CERR = ABAP_TRUE
* REPLACEMENT = '#'
* WRITE_BOM = ' '
* TRUNC_TRAILING_BLANKS_EOL = 'X'
* WK1_N_FORMAT = ' '
* WK1_N_SIZE = ' '
* WK1_T_FORMAT = ' '
* WK1_T_SIZE = ' '
* WRITE_LF_AFTER_LAST_LINE = ABAP_TRUE
* SHOW_TRANSFER_STATUS = ABAP_TRUE
* IMPORTING
* FILELENGTH =
TABLES
data_tab = p_t_outfile
* FIELDNAMES =
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.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
ENDFORM. " PUT_DATA
SAP has provided us with a lot of standard programs to make our day to day life easier. The standard code suite extends from simple programs to upload or download hierarchies to more complex ones to activate inactive objects in production. The flat file manipulation programs include programs to upload or download flat file to or from the system. But this list does not include one to transpose a flat file.
There are ‘n’ numbers of ways to achieve this. What is given in this article is to consider the separator values e.g. comma (,) or colon (;) as regular expressions and derive the values nested within the separators.
Regular Expressions
In computing, a regular expression provides a concise and flexible means for "matching" (specifying and recognizing) strings of text, such as particular characters, words, or patterns of characters - Wikipedia
Generally, in a given string any value that occurs repeatedly, be it in regular or random intervals, can be termed as a regular expression. In our case, any flat-file whose values are separated by an expression e.g. comma (,) or colon (;) can be taken to contain regular expressions with the separator as the expression itself.
REGEX
The REGEX command was introduced into ABAP with NetWeaver Release 2004s. It has been widely used for manipulating character strings and to search for specific expressions in text files which are extensive.
A code sample for finding regular expression would be like,
FIND ALL OCCURRENCES OF REGEX <expression> IN <string> RESULTS <t_results>.
The above code would execute the REGEX command over the given string and store all occurrences of the ‘expression’ in an internal table
Note: A regular ‘csv’ file would not have a comma separator at the end of a record. An entry would be manually inserted in the program to identify the last value.
CODE
*&---------------------------------------------------------------------*
*& Report ZFILE_TRANSPOSE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zfile_transpose.
*DECLARATION
TYPES: BEGIN OF st_file,
rec TYPE string,
END OF st_file.
TYPES: BEGIN OF st_inter,
row_no TYPE i,
rec TYPE string,
END OF st_inter.
DATA : t_file TYPE TABLE OF st_file, "Internal table to read file
wa_file TYPE st_file,
t_outfile TYPE TABLE OF st_file,
wa_outfile TYPE st_file,
t_inter TYPE TABLE OF st_inter, "Intermidiate table to store file
wa_inter TYPE st_inter.
FIELD-SYMBOLS : <fs_file> TYPE st_file.
FIELD-SYMBOLS : <fs_inter> TYPE st_inter.
DATA : it_csv TYPE match_result_tab, "Internal table for REGEX result
wa_it_csv LIKE LINE OF it_csv.
DATA : col_count TYPE i,
row_count TYPE i,
loop_count1 TYPE i,
loop_count2 TYPE i,
col_len TYPE i,
temp1 TYPE i,
temp2 TYPE i,
temp3 TYPE i,
value TYPE string,
char_len TYPE string.
CONSTANTS: c_filetype TYPE char10 VALUE 'ASC'. "For Filetype DAT
PARAMETERS: p_file1 LIKE rlgrap-filename OBLIGATORY. "Get file loacftion
PARAMETERS: p_file2 LIKE rlgrap-filename OBLIGATORY. "File loaction for transposed data
PARAMETERS: p_sptr TYPE c OBLIGATORY. "Character used for separator
*GET FILE PATH
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file1. "Get FILE from selection screen
PERFORM get_file_path USING p_file1.
*&---------------------------------------------------------------------*
START-OF-SELECTION.
*UPLOAD DATA
PERFORM get_data USING p_file1 CHANGING t_file. "Copy FILE data to internal table
*&---------------------------------------------------------------------*
*CREATE INTERNAL TABLE TO STORE DTO DETAILS
READ TABLE t_file INTO wa_file INDEX 1.
IF sy-subrc = 0.
FIND ALL OCCURRENCES OF p_sptr IN wa_file-rec MATCH COUNT col_count.
col_count = col_count + 1. "To count number of columns
ENDIF.
LOOP AT t_file INTO wa_file.
row_count = row_count + 1. "To count number of rows
ENDLOOP.
LOOP AT t_file INTO wa_file.
loop_count1 = sy-tabix.
col_len = strlen( wa_file-rec ). "Find length of each row
FIND ALL OCCURRENCES OF REGEX p_sptr IN wa_file-rec RESULTS it_csv.
IF sy-subrc = 0.
wa_it_csv-offset = col_len. "Create entry in regex table
APPEND wa_it_csv TO it_csv. "for last comma
ELSE.
MESSAGE 'Incorrect Separator Value' TYPE 'E'.
ENDIF.
temp1 = 0.
LOOP AT it_csv INTO wa_it_csv.
temp2 = temp3 = wa_it_csv-offset.
temp2 = temp2 - temp1.
value = wa_file-rec+temp1(temp2). "Get value between commas
temp1 = temp3 + 1.
loop_count2 = sy-tabix.
IF loop_count1 = 1.
wa_inter-row_no = loop_count2. "Include row number for each record
CONCATENATE value p_sptr INTO wa_inter-rec.
APPEND wa_inter TO t_inter.
ENDIF.
IF loop_count1 > 1 AND loop_count1 < row_count. "Adding comma separation to transposed value
READ TABLE t_inter ASSIGNING <fs_inter> WITH KEY row_no = loop_count2.
IF sy-subrc = 0.
CONCATENATE <fs_inter>-rec value p_sptr INTO <fs_inter>-rec.
UNASSIGN <fs_inter>.
ENDIF.
ELSEIF loop_count1 EQ row_count. "No comma needed for last value
READ TABLE t_inter ASSIGNING <fs_inter> WITH KEY row_no = loop_count2.
IF sy-subrc = 0.
CONCATENATE <fs_inter>-rec value INTO <fs_inter>-rec.
UNASSIGN <fs_inter>.
ENDIF.
ENDIF.
ENDLOOP.
ENDLOOP.
LOOP AT t_inter INTO wa_inter.
wa_outfile-rec = wa_inter-rec.
APPEND wa_outfile TO t_outfile.
ENDLOOP.
*DOWNLOAD DATA
PERFORM put_data USING p_file2 CHANGING t_outfile. "Copy FILE data to internal table
IF sy-subrc = 0.
MESSAGE 'File transposed Succesfully' TYPE 'S'.
ENDIF.
*&---------------------------------------------------------------------*
*& Form GET_FILE_PATH
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE1 text
*----------------------------------------------------------------------*
FORM get_file_path USING p_p_file1.
DATA : wsl_upfile LIKE rlgrap-filename VALUE IS INITIAL.
wsl_upfile = p_file1.
CLEAR: p_file1.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
* FIELD_NAME = ' '
* STATIC = ' '
* MASK = ' '
* FILEOPERATION = 'R'
CHANGING
file_name = wsl_upfile
* LOCATION_FLAG = 'P'
* 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.
p_file1 = wsl_upfile.
ENDFORM. " GET_FILE_PATH
*&---------------------------------------------------------------------*
*& Form GET_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE1 text
* <--P_T_FILE text
*----------------------------------------------------------------------*
FORM get_data USING p_p_file1
CHANGING p_t_file TYPE table.
DATA: l_file TYPE string.
l_file = p_p_file1.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = l_file
filetype = c_filetype
TABLES
data_tab = p_t_file
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 IS INITIAL.
ELSE.
MESSAGE s001(00) WITH 'File not found'.
STOP.
ENDIF.
CLEAR l_file.
ENDFORM. " GET_DATA
*&---------------------------------------------------------------------*
*& Form PUT_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_P_FILE2 text
* <--P_T_OUTFILE text
*----------------------------------------------------------------------*
FORM put_data USING p_p_file2
CHANGING p_t_outfile TYPE table.
DATA: l_outfile TYPE string.
l_outfile = p_p_file2.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
* BIN_FILESIZE =
filename = l_outfile
filetype = c_filetype
* APPEND = ' '
* WRITE_FIELD_SEPARATOR = ' '
* HEADER = '00'
* TRUNC_TRAILING_BLANKS = ' '
* WRITE_LF = 'X'
* COL_SELECT = ' '
* COL_SELECT_MASK = ' '
* DAT_MODE = ' '
* CONFIRM_OVERWRITE = ' '
* NO_AUTH_CHECK = ' '
* CODEPAGE = ' '
* IGNORE_CERR = ABAP_TRUE
* REPLACEMENT = '#'
* WRITE_BOM = ' '
* TRUNC_TRAILING_BLANKS_EOL = 'X'
* WK1_N_FORMAT = ' '
* WK1_N_SIZE = ' '
* WK1_T_FORMAT = ' '
* WK1_T_SIZE = ' '
* WRITE_LF_AFTER_LAST_LINE = ABAP_TRUE
* SHOW_TRANSFER_STATUS = ABAP_TRUE
* IMPORTING
* FILELENGTH =
TABLES
data_tab = p_t_outfile
* FIELDNAMES =
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.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
ENDFORM. " PUT_DATA
No comments:
Post a Comment