Tool to Populate a DataBase Table directly via a flat file

[Former Member]

Introduction

We use Database tables frequently for look-up in our ABAP logic. Maintaining records in table is a manual process by updating table record by record . Available program for such updates are static in nature .

Following approach allow you to create a generic program from updating any database table.

How To Use :

• Go to TCODE SE38 .
• Create a program Z_FLATFILE_UPLOAD as shown Below

• Copy Paste the below code

*&---------------------------------------------------------------------*
*& Z_FLATFILE_UPLOAD.
*& TITLE         : Program to upload a flat file from path provided
*& CREATED BY    : Jaya Tiwari
*&---------------------------------------------------------------------*

REPORT z_flatfile_upload.
*----------------------------------------------------------------------*
* data declarations
*----------------------------------------------------------------------*
TYPES: BEGIN OF lty_ttab,
rec TYPE STRING,
END OF lty_ttab.

DATA: lt_itab TYPE STANDARD TABLE OF lty_ttab WITH HEADER LINE.
DATA: lv_file_str TYPE string,
lv_column TYPE n LENGTH 4.
*Dynamic Table and Work area
FIELD-SYMBOLS :
<lfs_table>  TYPE ANY TABLE ,
<lfs_watable> .

DATA : lv_tabname TYPE dd02l-tabname . " DataBase Table Name
DATA : lt_tble TYPE REF TO data ,
lt_line TYPE REF TO data .
"Structure for Reading Field Names of DataBase Table
TYPES : BEGIN OF lty_dd03vv,
tabname  TYPE tabname ,
fieldname  TYPE fieldname ,
position  TYPE tabfdpos ,
END OF lty_dd03vv .
"Internal Table and work area for Reading Field Names of DataBase Table
DATA : lt_dd03vv TYPE STANDARD TABLE OF lty_dd03vv WITH HEADER LINE ,
lwa_dd03vv LIKE LINE OF lt_dd03vv .
DATA : lv_fieldname  TYPE fieldname ,
lv_counter(2) TYPE n VALUE 0.

FIELD-SYMBOLS: <lfs_field_from>.
*----------------------------------------------------------------------*
* selection screen design
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK bi WITH FRAME TITLE  txt_t1 .
SELECTION-SCREEN BEGIN OF LINE .
SELECTION-SCREEN COMMENT (25) txt_file .
PARAMETERS: lp_file TYPE localfile.
SELECTION-SCREEN END OF LINE .

SELECTION-SCREEN BEGIN OF LINE .
SELECTION-SCREEN COMMENT (25) txt_tabl .
PARAMETERS: lp_tble LIKE lv_tabname.
SELECTION-SCREEN END OF LINE .
SELECTION-SCREEN SKIP 1 .

SELECTION-SCREEN BEGIN OF LINE .
PARAMETERS : cb_hdr AS CHECKBOX  DEFAULT 'X' MODIF ID m1.
SELECTION-SCREEN COMMENT (70) txt_warn MODIF ID m1.
SELECTION-SCREEN END OF LINE .


SELECTION-SCREEN END OF BLOCK bi.
*----------------------------------------------------------------------*
* At selection screen for field
*----------------------------------------------------------------------*
INITIALIZATION .
txt_file = 'File Path  '.
txt_t1  = 'File Upload Details '.
txt_tabl = 'Target table Name '.
txt_warn = 'Ignore Header Line in file Uploading .'.
*----------------------------------------------------------------------*
* Read File Path
*----------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR lp_file.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
static    = 'X'
CHANGING
file_name = lp_file.
*----------------------------------------------------------------------*
* Validation
*----------------------------------------------------------------------*
AT SELECTION-SCREEN ON BLOCK bi .
IF  lp_tble IS INITIAL.
MESSAGE 'Target Table Name Cannot be Empty.' TYPE 'E'.
ENDIF.

*----------------------------------------------------------------------*
* Start of selection
*----------------------------------------------------------------------*

START-OF-SELECTION.
lv_file_str = lp_file.

*FM to read file content from path provided
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename                = lv_file_str
TABLES
data_tab                = lt_itab
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.
MESSAGE 'File could not be uploaded' TYPE 'E'.
ENDIF.

*----------------------------------------------------------------------*
* Dynamic Internal Table and Work Area creation
*----------------------------------------------------------------------*

IF lp_tble IS NOT INITIAL.

lv_tabname = lp_tble.
"Check If table exist in Data Dictionary and no of columns in table

SELECT COUNT(*)
FROM dd03vv
INTO  lv_counter
WHERE tabname = lv_tabname
AND as4local = 'A' .

IF sy-subrc = 0 AND lv_counter GT 0 .

"Dynamic Internal Table Defination
CREATE DATA lt_tble TYPE TABLE OF (lp_tble).
ASSIGN lt_tble->* TO <lfs_table> .

"Dynamic Work Area Defination
CREATE DATA lt_line LIKE LINE OF <lfs_table>.
ASSIGN lt_line->* TO <lfs_watable> .



SELECT
tabname
fieldname
position
FROM dd03vv
INTO TABLE lt_dd03vv
WHERE tabname = lv_tabname
AND as4local = 'A' .

SORT lt_dd03vv BY  position .
*----------------------------------------------------------------------*
* Assign Data to Internal Table via Work Area
*----------------------------------------------------------------------*
LOOP AT lt_itab.
" Ignore header Row if Checkbox is checked
IF cb_hdr = 'X'.

cb_hdr = ''.
ELSE.
"Read Field Name from Internal Table one by one
DO  lv_counter TIMES.
READ TABLE lt_dd03vv INTO lwa_dd03vv
WITH KEY position = sy-index BINARY SEARCH .
lv_fieldname =  lwa_dd03vv-fieldname .

"Assign Data to each field
ASSIGN COMPONENT lv_fieldname OF STRUCTURE
<lfs_watable> TO <lfs_field_from>.

ENDDO.

"Insert row into Dynamic Internal table
INSERT <lfs_watable> INTO TABLE  <lfs_table> .
ENDIF.
ENDLOOP.
*----------------------------------------------------------------------*
***------- Modify Database tabe with Internal table -------***
*   If record already exist it will be update ,
*   else new entry will get created
*----------------------------------------------------------------------*

MODIFY (lp_tble) FROM TABLE  <lfs_table>.
IF sy-subrc = 0.
WRITE : 'Table ',lp_tble ,'Successfully Updated'.
ENDIF.
ELSE .

MESSAGE 'Table not found in Data Dictionary .' TYPE 'E'.

ENDIF.

ENDIF.
*----------------------------------------------------------------------*
* End Of Program
*----------------------------------------------------------------------*

• Save and Activate your code .
• Execute Program  ,Following selection screen will appear .Provide file path and table name as input .Also uncheck check box if there is no header row.

• Test Data :

ztest1	ztest2	ztest3
A	DD	ADADSADA
B	DD	adadada
C	DD	sccada
D	DD	adadada
E	DD	12121313
F	DD	12121
G	DD	445.67
H	DD	saas
I	DD	rwrw
J	DD	zsczczc
K	DD	awaqswqw
L	DD	qqqqqqqqqqqqqqqqqqqqqqqqqqq

• Pass Inputs like  shown in above snapshot and execute .On success following message will appear .

• Check database table via TCODE SE16  ,It will display loaded records.

• To Update Table Data change the Data in file and load again .

*** For client specific tables value in column MANDT will automatically gets populated so no need to create a separate column in flat file for the same .

This program only allows you to add new records or update ( based on key ) existing records in a table .Delete functionality is purposefully opted out, as this can accidentally affect important tables .However if someone want to add delete functionality in program, I would suggest to put some authorization constraint as who or which table can use the program to delete data  .A custom table can be created to store authorized users or tables that can use deletion functionality and before deletion this table will be referred by program .

Limitations

1. There is limitation of data length for internal table reading  flat file of 262143 characters.
2. Program can be executed for one table at a time .

Appendix :

Loading a Flat file and placing at Application Server

This program will allow you to load your flat file at specific path in application server .

Here we are providing 3 static paths which can be configured once (Path exist in AL11- SAP Directory) and then can be used just by selecting specific radio button .

If User want to use different path other than listed one  ,Other radio button will allow him to pass it manually .

Program Output :

Double Click on output screen to navigate through SAP Directory to see file output .

Sample File Output

Sample Code :

*&-----------------------------------------------------------------------------------------*
*& Report:      z_flatfile_load_al11
*& Title :      Program will load a flat file and place it at application server
*&              at specified path
*& Created BY:  Jaya Tiwari
*&-----------------------------------------------------------------------------------------*

REPORT z_flatfile_load_al11.
*----------------------------------------------------------------------------------------*
* data declarations
*----------------------------------------------------------------------------------------*

" To Read CSV file data from user's workstation
TYPES: BEGIN OF lty_ttab,
rec TYPE string,
END OF lty_ttab.

DATA: lt_itab TYPE STANDARD TABLE OF lty_ttab  WITH HEADER LINE .

DATA: lv_file_str TYPE string ,   "file Path
lv_system TYPE c LENGTH 15 , "system name
lv_count TYPE n LENGTH 5 .

DATA : lv_counter(2) TYPE n VALUE 0 ,
lv_tabname TYPE tabname.

"For Application server file
DATA: lv_msg_text(50),
lv_string(20000) TYPE c,
lv_filename TYPE rlgrap-filename.

"For Process chain last execution logs
DATA : lv_status TYPE c LENGTH 70 ,
lv_datum TYPE c LENGTH  50.
DATA: lv_ans(1) TYPE c.

DATA : path_name(150) TYPE c. "AL11 Path Name

DATA len  LIKE sy-fdpos.

DATA len1 LIKE sy-fdpos.
DATA char.
DATA screentype(4).
DATA shift_ind LIKE sy-index.

DATA l_delimiter TYPE c.
DATA l_lines     TYPE i.
DATA ls_split    TYPE string.
DATA lt_split    LIKE TABLE OF ls_split.

*----------------------------------------------------------------------*
* selection screen design
*----------------------------------------------------------------------*

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE  txt_t1 .
SELECTION-SCREEN BEGIN OF LINE .
SELECTION-SCREEN COMMENT (25) txt_file .
PARAMETERS: lp_file TYPE localfile . "file path
SELECTION-SCREEN END OF LINE .

SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE  txt_t2 .

"Monthly
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: gf_rdbt1 RADIOBUTTON GROUP g1 USER-COMMAND usr DEFAULT 'X'.
SELECTION-SCREEN COMMENT (70) gf_rdcm1    .
SELECTION-SCREEN END OF LINE.
"One Time
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: gf_rdbt2 RADIOBUTTON GROUP g1 .
SELECTION-SCREEN COMMENT (70)  gf_rdcm2   .
SELECTION-SCREEN END OF LINE.
"Weekly
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: gf_rdbt3 RADIOBUTTON GROUP g1  .
SELECTION-SCREEN COMMENT (70)  gf_rdcm3    .
SELECTION-SCREEN END OF LINE.

"User Provided Path
SELECTION-SCREEN BEGIN OF LINE.
PARAMETERS: gf_rdbt4 RADIOBUTTON GROUP g1 .
SELECTION-SCREEN COMMENT (23)  gf_rdcm4   .
PARAMETERS: lp_fil2 LIKE rlgrap-filename MODIF ID rd2 .
SELECTION-SCREEN END OF LINE.

*SELECTION-SCREEN SKIP 1 .

SELECTION-SCREEN END OF BLOCK b2.




*----------------------------------------------------------------------*
* At selection screen for field
*----------------------------------------------------------------------*
INITIALIZATION .

txt_file = 'Input File Path   '.
txt_t1   = 'User Selected File Path '.
txt_t2   = 'Application Server Path to Write File  '.
gf_rdcm1 = '  /Path_1/monthly'.
gf_rdcm2 = '  /Path_2/onetime'.
gf_rdcm3 = '  /Path_3/weekly'.
gf_rdcm4 = '  Other'.

*----------------------------------------------------------------------*
* Read File Path
*----------------------------------------------------------------------*
AT SELECTION-SCREEN OUTPUT.

IF  gf_rdbt1 = 'X' OR  gf_rdbt2 = 'X' OR  gf_rdbt3 = 'X'.
LOOP AT SCREEN.
IF screen-group1 = 'RD2'.
screen-input = 0.
MODIFY SCREEN.
ENDIF.
ENDLOOP.
ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR lp_file.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
static    = 'X'
CHANGING
file_name = lp_file.


AT LINE-SELECTION.
CALL TRANSACTION 'AL11' .

*----------------------------------------------------------------------*
* Start of selection
*----------------------------------------------------------------------*

START-OF-SELECTION.
SPLIT lp_file AT '.' INTO lv_file_str lv_filename .
TRANSLATE lv_filename TO UPPER CASE .

*Important Points :

* Program will accept only file with .CSV extension . Please modify the code if you need to load other file types .

IF lv_filename = 'CSV' .

CLEAR : lv_filename .
"Get Filename
SPLIT lv_file_str AT '\' INTO TABLE lt_split.
DESCRIBE TABLE lt_split LINES l_lines.
* file name
LOOP AT lt_split INTO ls_split.
IF sy-tabix EQ l_lines.
lv_filename  = ls_split.
ENDIF.
ENDLOOP.

lv_file_str = lp_file .
IF lv_file_str IS NOT INITIAL.
*Read CSV File
PERFORM load_file .
*    Create / overwrite a file on app server from internal table
PERFORM write_file .
ELSE.
MESSAGE 'File Path Needed .' TYPE 'W'.
ENDIF.
ELSE.
MESSAGE '.CSV File Needed .' TYPE 'W'.
ENDIF.

FREE :lv_system ,lt_itab,lv_filename ,lt_split ,l_lines ,lv_file_str.


*&---------------------------------------------------------------------*
*&      Form  load_file
*&---------------------------------------------------------------------*
*      load flat file from user system into internal table
*----------------------------------------------------------------------*
FORM load_file .

*FM to read file content from path provided
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename                = lv_file_str
TABLES
data_tab                = lt_itab
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.
MESSAGE 'File could not be uploaded' TYPE 'E'.
ENDIF.

ENDFORM .                    "load_file

*&---------------------------------------------------------------------*
*&      Form  write_file
*&---------------------------------------------------------------------*
*      write content of file loaded into application server
*----------------------------------------------------------------------*
FORM write_file .

IF     gf_rdbt1 = 'X'.
CONCATENATE '/Path_1/monthly/' lv_filename  '.CSV' INTO lv_filename .
ELSEIF gf_rdbt2 = 'X'.
CONCATENATE '/Path_2/onetime/' lv_filename  '.CSV' INTO lv_filename .
ELSEIF gf_rdbt3 = 'X'.
CONCATENATE '/Path_3/weekly/' lv_filename  '.CSV' INTO lv_filename .
ELSEIF gf_rdbt4 = 'X'.
IF lp_fil2 EQ '.\' OR lp_fil2 EQ './'.
CONCATENATE  lp_fil2  lv_filename  '.CSV' INTO lv_filename .
ELSEIF  lp_fil2 CP '*/*'.
CONCATENATE  lp_fil2 '/' lv_filename  '.CSV' INTO lv_filename .
ELSEIF lp_fil2 CP '*\*'.
CONCATENATE  lp_fil2 '\' lv_filename  '.CSV' INTO lv_filename .
ENDIF .
ENDIF.

CONDENSE lv_filename NO-GAPS .
OPEN DATASET lv_filename FOR OUTPUT IN TEXT MODE
ENCODING DEFAULT MESSAGE lv_msg_text.

IF sy-subrc NE 0.
WRITE: 'File' ,lv_filename ,' cannot be opened. Reason:', lv_msg_text.
EXIT.
ELSE.
*Transfer data to file at application server
LOOP AT lt_itab.
TRANSFER lt_itab-rec TO lv_filename.
ENDLOOP.
WRITE : 'File available at Application Server as: ' ,lv_filename  .
CLOSE DATASET lv_filename.
REFRESH :lt_itab .

ENDIF.
ENDFORM .                    "write_file

*----------------------------------------------------------------------*
* End Of Program
*----------------------------------------------------------------------*