Skip to Content

·            Abstract

                  Transferring excel sheet data to notepad dynamically.

·            Executive Summary

          This Generic utility program will transfer/move content of excel sheet to notepad in PDF readable format i.e. no comma, no tab delimited format, so that data can be read from notepad via size of the fields. User need to provide position of column needed in notepad, starting/ending position of specific columns in notepad, size of column in notepad, column number required from excel, default value if any in table ZOUTPUT_FORMAT(as shown below). Based on the data of ZOUTPUT_FORMAT. Via this utility user can transfer any number of columns from excel at required position in notepad without changing the program i.e. without help of technical Person.

1.0   Functionality

                 Consider we have 50 fields in excel sheet and we need to transfer only 25 fields, each field may have different sequence of display in notepad and all fields may or may not have same size. User need to provide position of column needed in notepad, starting/ending position of specific columns in notepad, size of column in notepad, column number required from excel, default value if any in table ZOUTPUT_FORMAT(as shown below).

Selection Screen : – Here you can provide path for input excel file and output notepad file. Even if you have not provided the output file name, it will automatically create file at specified file path.

/wp-content/uploads/2012/07/ss1_117450.jpg

After executing, you’ll get below message in status bar.

/wp-content/uploads/2012/07/ss2_117451.jpg

ZOUTPUT_FORMAT:

We need to create custom tables with following fields as in the screen shot below.

/wp-content/uploads/2012/07/db1_117455.jpg

Field description  for Table ZOUTTAB_FORMAT.

Client :- Client field is mandatory field in table ZOUTPUT_FORMAT.

Column_pos_txt :- This field provides the position of selected fields of excel sheet in notepad.

Column_pos_xls :- This field contains excel sheet fields which will appear in notepad. Out of 33 fields of current scenario of excel sheet we are choosing only 23 fields which is finally going to appear in notepad. We have chosen 1,2,4,5,6,7,10,11,12,14,15,16,17,18,19,20,21,22,27,30,31,32,34th field from excel sheet at different positions in notepad. Please refer column COLUMN_POS_XLS in above diagram. For their respective position in notepad, please refer column COLUMN_POS_TXT in above diagram.

Start_p  :-  Starting position of field in notepad .

End_p  :-  End position of field in notepad.

Column_size  :-  Size of field in notepad.

Default_val  :- User can insert default values for each column.

ASSUMPTIONS:

       1.We have assumed that max no. of row available in excel sheet is ‘9999’,if there are more than ‘9999’ records, then increase value of c_end_row constant used for reading excel sheet end row.

2.  Value of c_start_col is provided in program zgeneric_utility is 2, you must change it as 1 if header / label is not provided in excel sheet. Otherwise 1st record from excel sheet will not get transferred tonotepad.

3. User must be careful providing size of the fields / columns in transparent table ‘ZOUTPUT_FORMAT’, if size provided is less than actual field of records than it will truncate the field value and if size is greater than actual it will print space afterword.

4.  Maximum cell length of excel is 50. Anything greater than that will get truncated in notepad.

Coding:

1. select all the data from the custom table ZOUTPUT_FORMAT.

2.Upload the excel file.We can use FM”ALSM_EXCEL_TO_INTERNAL_TABLE”, to convert excel data to internal table

Logic for converting excel to note pad:

1.Get the total number of records in the internal table(excel sheet datas) and also get total number of records from the custom table.

2.Process all the records of excel sheet using do-enddo statement

DESCRIBE TABLE g_t_excel LINES linno_excel.

DESCRIBE TABLE g_t_excel LINES linno_excel.

DESCRIBE TABLE itab LINES linno_itab.

READ TABLE itab INDEX linno_itab.

  DO g_t_excel-row TIMES.

row2 = row2 + 1. “Counter

    DO itab-column_pos_txt TIMES.

      READ TABLE itab INDEX sy-index.

      READ TABLE g_t_excel WITH KEY row = row2 col = itab-column_pos_xls

      offset = itab-start_p – 1.

      IF sy-subrc <> 0.

        g_t_input1-var+offset(itab-col_size) = itab-default_val.

      ELSE.

        g_t_input1-var+offset(itab-col_size) = g_t_excel-value.

      ENDIF.

    ENDDO.

    APPEND g_t_input1.

    CLEAR g_t_input1.

  ENDDO.

Download file:

Using GUI_DOWNLOAD we can download file to notepad. Pass the file name and file type as ‘ASC’. Tables= g_t_input1.

Sample input file:

/wp-content/uploads/2012/07/input_117457.jpg

Sample output file:

/wp-content/uploads/2012/07/output_117461.jpg

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply