Upload multiple excel files from local pc directory

[Former Member]

In our requirement, There are almost 20 excel files in a directory of local pc. At run time, we will not be knowing the file names of these excel files but the columns of these excel files are same and known to us.Also path of the directory is with us.

We need to upload all these excel file into variuos internal tables for BDC Processing at later stage. Can anybody suggest how to accomplish this?

[Former Member]

Hi Rohit,

Use the Function moudle 'WS_FILENAME_GET' to get the path of the local directory.

At run time the import parameter 'FILENAME' of the above shown FM have the current path which you selected.

Pass this value to the parameter 'FILENAME' of the function module ''WS_UPLOAD' to upload the data from the excel sheet at the path you selected to your internal table.

By this you can get the data of one excel sheet to your internal table. To upload more then one excel file, Place two push button in your selection screen.At the event 'AT USER-COMMAND' check the function code, if it belongs to first button then call the above mentioned function modules else try to excecute your BDC code. Don't forget to change the internal table name at each time.

With Regards,

Muruganantham.E

[Former Member]

You need something like this

**************************************************************************

• Variables definition*

**************************************************************************

*Files located in the directory

DATA gt_files TYPE TABLE OF sdokpath WITH HEADER LINE.

DATA gt_dirs TYPE TABLE OF sdokpath.

DATA: g_cont_tra(6) TYPE n,

g_cont_emp(5) TYPE n. "Numero de trabajadores

*Resumen de totales

DATA: g_ba01(11) TYPE n,

g_ba02(11) TYPE n.

*Longitud directorio

DATA l_long TYPE i.

**************************************************************************

• Internal tables definition*

**************************************************************************

*Fichero Excel

DATA: gt_fichero1 TYPE TABLE OF wa_file1 WITH HEADER LINE.

*Fichero FAN

DATA: gt_fichero2 TYPE TABLE OF wa_tfan WITH HEADER LINE.

START-OF-SELECTION.

*Leemos los ficheros del directorio para luego crear los ficheros FAN

CALL FUNCTION 'TMP_GUI_DIRECTORY_LIST_FILES'

EXPORTING

directory = p_file1

filter = '.xls'*

TABLES

file_table = gt_files

dir_table = gt_dirs

EXCEPTIONS

cntl_error = 1

OTHERS = 2.

IF sy-subrc <> 0.

*Error finding files

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

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

ENDIF.

LOOP AT gt_files.

PERFORM leer_excel.

ENDLOOP.

END-OF-SELECTION.

**&----

---

**

*& Form LEER_EXCEL

**&----

---

**

FORM leer_excel .

DATA: it_raw TYPE truxs_t_text_data, "Variable necesaria para la función

p_fichero LIKE rlgrap-filename."Tipo de fichero necesario

• Tipo de ruta necesaria*

CONCATENATE p_file1 gt_files INTO p_fichero.

• REPLACE '.xls' WITH '.fan' INTO p_fichero.*

• Función que carga un ficher excel en una tabla interna*

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =*

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = p_fichero

TABLES

i_tab_converted_data = gt_fichero1[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE i000(su)

WITH 'Error al leer el archivo Excel.'

'Compruebe que el archivo no esta abierto'

'y revise que la estructura'

'es la adecuada'.

STOP.

ENDIF.

ENDFORM. " LEER_EXCEL

[Former Member]

RFC 'RZL_READ_DIR_LOCAL is helpful in getting all the file names under known directory path.

Passing the .xls file names to the function module 'ALSM_EXCEL_TO_INTERNAL_TABLE' one by one in the logic have uploaded all the internal tables accordingly.