Load data into excel , per file includes 1000 records using select statement in abap

Former Member — Mon, 07 Oct 2019 08:10:23 GMT

REPORT zaa_fdatexta1.

DATA:ls_fieldcat TYPE slis_fieldcat_alv,
lt_fieldcat TYPE slis_t_fieldcat_alv,
ls_fieldcatlog TYPE slis_fieldcat_alv,
lt_fieldcatlog TYPE slis_t_fieldcat_alv,
lt_layout TYPE slis_layout_alv.

DATA: w_dref TYPE REF TO data.
FIELD-SYMBOLS: <t_itab> TYPE STANDARD TABLE , "dynamic internal table to read data from the database
<wa_tab> TYPE any.

DATA: w_dref1 TYPE REF TO data.
FIELD-SYMBOLS: <fi_itab> TYPE STANDARD TABLE, " table to load 1000 records from the t_itab
<wfi_tab> TYPE any.

TYPES: BEGIN OF ty_header, " to create field header in xlsx file
fname(100) TYPE c,
END OF ty_header.

DATA: it_header TYPE TABLE OF ty_header,
wa_header TYPE ty_header.

DATA: BEGIN OF inttab OCCURS 100. "table to read files of given table
INCLUDE STRUCTURE dfies.
DATA: check TYPE checkbox. "additional field to check the condition for selected fields
DATA: END OF inttab.

DATA: wa_tab LIKE LINE OF inttab.

TYPES: BEGIN OF ty_int1 , "structure for selecting fields on the selection screen
fieldname TYPE fieldname,
END OF ty_int1.

DATA: inttab1 TYPE TABLE OF ty_int1, " to load the field names of given data base table
wa_tab1 LIKE LINE OF inttab1.

PARAMETERS: p_table TYPE tabname OBLIGATORY.
PARAMETERS: p_file LIKE rlgrap-filename DEFAULT 'C:\Users\aaalve\Desktop\export.xls'.
PARAMETERS: p_enable AS CHECKBOX DEFAULT ' ' USER-COMMAND uc1.
data s_cursor type cursor.

AT SELECTION-SCREEN OUTPUT.

IF p_enable = 'X' .
CALL FUNCTION 'DDIF_FIELDINFO_GET' "to fetch table field information
EXPORTING
tabname = p_table
* FIELDNAME = FIELDNM
langu = sy-langu
* LFIELDNAME = ' '
* ALL_TYPES = ' '
* IMPORTING
* X030L_WA = WATAB
* DDOBJTYPE =
* DFIES_WA =
* LINES_DESCR =
TABLES
dfies_tab = inttab
* FIXED_VALUES =
EXCEPTIONS
not_found = 1
internal_error = 2
OTHERS = 3.

PERFORM popup_display.

ENDIF.

*&---------------------------------------------------------------------*
*& Form popup_display
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM popup_display.

*FIELD CATALOG FOR POPUP SELECT
CLEAR ls_fieldcat.
ls_fieldcat-row_pos = '1'.
ls_fieldcat-col_pos = '1'.
ls_fieldcat-fieldname = 'CHECK'.
ls_fieldcat-tabname = 'INTTAB'.
ls_fieldcat-seltext_m = 'SELECT'.
APPEND ls_fieldcat TO lt_fieldcat.
CLEAR ls_fieldcat.
ls_fieldcat-row_pos = '1'.
ls_fieldcat-col_pos = '2'.
ls_fieldcat-fieldname = 'FIELDNAME'.
ls_fieldcat-tabname = 'INTTAB'.
ls_fieldcat-seltext_m = 'FIELDNAME'.
ls_fieldcat-outputlen = 50.
APPEND ls_fieldcat TO lt_fieldcat.

ls_fieldcat-row_pos = '1'.
ls_fieldcat-col_pos = '3'.
ls_fieldcat-fieldname = 'FIELDTEXT'.
ls_fieldcat-tabname = 'INTTAB'.
ls_fieldcat-seltext_m = 'DESCRIPTION'.
APPEND ls_fieldcat TO lt_fieldcat.
CLEAR ls_fieldcat.

* Display data in a POPUP for selecting the fields
CALL FUNCTION 'REUSE_ALV_POPUP_TO_SELECT'
EXPORTING
i_zebra = 'X'
it_fieldcat = lt_fieldcat
i_tabname = 'INTTAB'
i_checkbox_fieldname = 'CHECK'
TABLES
t_outtab = inttab.
CLEAR p_enable.

ENDFORM. "popup_display

START-OF-SELECTION.

PERFORM selec.
* perform download.

*&---------------------------------------------------------------------*
*& Form SELEC
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM selec .

CREATE DATA w_dref TYPE STANDARD TABLE OF (p_table). "<t_itab> pointing to structure of data base table
ASSIGN w_dref->* TO <t_itab>.

CREATE DATA w_dref1 TYPE STANDARD TABLE OF (p_table). "<fi_itab> pointing to structure of data base table
ASSIGN w_dref1->* TO <fi_itab>.

IF sy-subrc EQ 0.
DATA: lv_temp TYPE string.
LOOP AT inttab INTO wa_tab WHERE check = 'X'. "selecting field names from inttab to inttab1

CONCATENATE lv_temp wa_tab-fieldname INTO lv_temp SEPARATED BY space.

MOVE wa_tab-fieldname TO wa_header.
APPEND wa_header TO it_header.
CLEAR: wa_tab,wa_header.
ENDLOOP.

*do.
*SELECT (lv_temp) FROM (p_table)
*INTO CORRESPONDING FIELDS OF TABLE <t_itab> PACKAGE SIZE 1000.

OPEN CURSOR s_cursor FOR
SELECT (lv_temp) FROM (p_table).
DO.
FETCH NEXT CURSOR s_cursor APPENDING TABLE <t_itab> PACKAGE SIZE 1000.
IF sy-subrc <> 0.
CLOSE CURSOR s_cursor.
* RAISE no_more_data.
EXIT.
ENDIF.
perform download.
ENDDO.

*LOOP AT inttab1 INTO wa_tab1.
**SELECT (wa_tab1) FROM (p_table)
**INTO CORRESPONDING FIELDS OF TABLE <t_itab>. "fetching the records based on selected fields i.e wa_tab1
**CLEAR wa_tab1.
*ENDLOOP.
*ENDSELECT.
*PERFORM download.
*enddo.

ENDIF.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form DOWNLOAD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM download .
* DATA: cnt TYPE string. " to load 1000 records in fi_itab
DATA: d TYPE string. " to create multiple files of excel e.x. export1 export2....
DATA: file TYPE string. " converting p_file to string
file = p_file.
d = 0.
* cnt = 0.
*
* LOOP AT <t_itab> ASSIGNING <wa_tab>. "original table with all records
*
* ASSIGN <wa_tab> TO <wfi_tab>.
* APPEND <wfi_tab> TO <fi_itab>.
* cnt = cnt + 1.
*
* IF cnt > 1000.
d = d + 1.

CONCATENATE 'C:\Users\vbirajda\Desktop\export' d '.xls' INTO file.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
* BIN_FILESIZE =
filename = file
filetype = 'DAT'
append = 'X'
write_field_separator = 'X'
* 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
* VIRUS_SCAN_PROFILE = '/SCET/GUI_DOWNLOAD'
* IMPORTING
* FILELENGTH =
TABLES
data_tab = <t_itab>
fieldnames = it_header
* 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.

* cnt = 0.
* LOOP AT <fi_itab> ASSIGNING <wfi_tab>.
* DELETE <fi_itab>.
* ENDLOOP.
* ENDIF.

* ENDLOOP.

ENDFORM.

Re: Load data into excel , per file includes 1000 records using select statement in abap

Former Member — Mon, 07 Oct 2019 08:19:35 GMT

Hi, Please describe your issue clearly.. not able to understand what problem are you facing exactly .

Re: Load data into excel , per file includes 1000 records using select statement in abap

Former Member — Mon, 07 Oct 2019 08:30:39 GMT

I want to load the data into excel file. Each excel file should contain 1000 records and the requirement has to be done by using select statement in abap.But with this code i am able to fetch all the records instead of 1000 records in each file

Re: Load data into excel , per file includes 1000 records using select statement in abap

Sandra_Rossi — Mon, 07 Oct 2019 08:32:53 GMT

Please use the COMMENT button for comments, questions, adding details, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area: "Before answering You should only submit an answer when you are proposing a solution to the poster's problem"

Re: Load data into excel , per file includes 1000 records using select statement in abap

Sandra_Rossi — Mon, 07 Oct 2019 08:33:05 GMT

Re: Load data into excel , per file includes 1000 records using select statement in abap

Sandra_Rossi — Mon, 07 Oct 2019 08:34:10 GMT

Please use the CODE button to format your code so that it's shown in a more user-friendly format (colorized).

Re: Load data into excel , per file includes 1000 records using select statement in abap

Sandra_Rossi — Mon, 07 Oct 2019 08:36:49 GMT

Please don't post useless code like commented lines of code.

Re: Load data into excel , per file includes 1000 records using select statement in abap

Former Member — Mon, 07 Oct 2019 08:40:40 GMT

please use select endselect statement when using package size.

SELECT * FROM <itab> INTO /APPENDING CORRESPONDING FIELDS OF TABLE <itab > PACKAGE SIZE 1000.

ENDSELECT.

Also, there is no concept of indexing in relational databases, so I would suggest to first select all records and then loop through records to fill your excel with 1000 records each.

Re: Load data into excel , per file includes 1000 records using select statement in abap

former_member1716 — Mon, 07 Oct 2019 09:27:29 GMT

Tell us what is the issue you are facing?

Re: Load data into excel , per file includes 1000 records using select statement in abap

Abinathsiva — Tue, 08 Oct 2019 04:25:24 GMT

Hi do mention your requirement.

topic Re: Load data into excel , per file includes 1000 records using select statement in abap in Application Development and Automation Discussions

Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap

Re: Load data into excel , per file includes 1000 records using select statement in abap