Good morning, I have been going around for several days with this issue of processing an .XLS file from the background, and with the file hosted on the application server, and I have not found a way that suits my requirement, and my client’s partner is not willing to convert the file to a format such as .CSV or .XLSX, which can be processed from the background.
At the moment, the only thing that is working for me is the execution of a python script, called from my ABAP code, but I have some concerns, and probably in the end this method will not be accepted. In any case, I leave my idea here, and I would like, if you would be very kind, to help me to clear up my doubts about whether there is some security problem that I am not considering, or some other that could become an inconvenience. Thank you very much in advance.
The following steps were reproduced on a miniSAP test system:
Configuring transaction SM69
This transaction executes commands as if it were the server terminal. In the “Operating System Command” parameter we put python3 (or python depending on the version(s) installed on the server), and in “Parameter for Operating System Command” we would put the parameters that would be executed with the call to python3, in our case the path of the script, and separated by a space, the path of the .XLS file. In our case we will leave this blank, but if we wanted to test the configuration from the SM69 we could fill in that field to be able to execute it right there. We also check the “Additional Parameters Allowed” checkbox.
Call from ABAP Code:
In the following ABAP report we execute the command previously configured in SM69 with the FM `SXPG_STEP_COMMAND_START`, we pass the script path and .XLS file path parameters, and if OK, we look for the CSV file created in the script to read and process it.
In my case, the created file did not have read permissions, so I had to create a new command in SM69 to change the permissions and be able to read them, but this depends on how the server is configured, it may not be necessary.
*& Report YTEST_XLS_READ
*&———————————————————————*
*&
*&———————————————————————*
REPORT ytest_xls_read.
PARAMETERS: p_ok type string.
DATA lv_parameters(255) TYPE c.
DATA lv_chmod(255) TYPE c.
DATA lv_xls_file TYPE string.
DATA lv_csv_file TYPE string.
DATA lv_script_file TYPE string.
DATA: lt_log TYPE STANDARD TABLE OF btcxpglog.
DATA: lv_line TYPE string.
lv_xls_file = ‘/usr/sap/NPL/D00/data/BigTable.xls’. “Supuesta dirección del XLS
lv_csv_file = ‘/usr/sap/NPL/D00/data/BigTable.csv’. “Supuesta dirección del CSV
lv_script_file = ‘/usr/sap/NPL/D00/data/reader.py’. “Supuesta dirección del script
“Concatenamos los parametros que deseamos, en nuestro la ruta del script y la del archivo XLS
lv_parameters = |{ lv_script_file } { lv_xls_file }|.
START-OF-SELECTION.
CALL FUNCTION ‘SXPG_STEP_COMMAND_START’
EXPORTING
commandname = ‘ZXLS_TO_CSV’
additional_parameters = lv_parameters
operatingsystem = sy-opsys “Linux
stdincntl = ‘R’
stdoutcntl = ‘M’
stderrcntl = ‘M’
tracecntl = ‘0’
termcntl = ‘C’
TABLES
log = lt_log “Log que luego podemos iterar para mostrar por SPOOL
EXCEPTIONS
command_not_found = 1
parameter_expected = 2
parameters_too_long = 3
security_risk = 4
wrong_check_call_interface = 5
no_permission = 6
unknown_error = 7
communication_error = 8
system_error = 9
cannot_get_rfc_dests = 10
job_update_failed = 11
job_does_not_exist = 12
program_start_error = 13
too_many_parameters = 14
OTHERS = 15.
IF sy-subrc <> 0.
ELSE.
“Paso OPCIONAL, en el caso de que el archivo generado no tenga permisos de lectura
lv_chmod = |777 { lv_csv_file }|.
CALL FUNCTION ‘SXPG_STEP_COMMAND_START’
EXPORTING
commandname = ‘ZCHMOD’
additional_parameters = lv_chmod
operatingsystem = sy-opsys
stdincntl = ‘R’
stdoutcntl = ‘M’
stderrcntl = ‘M’
tracecntl = ‘0’
termcntl = ‘C’
TABLES
log = lt_log
EXCEPTIONS
command_not_found = 1
parameter_expected = 2
parameters_too_long = 3
security_risk = 4
wrong_check_call_interface = 5
no_permission = 6
unknown_error = 7
communication_error = 8
system_error = 9
cannot_get_rfc_dests = 10
job_update_failed = 11
job_does_not_exist = 12
program_start_error = 13
too_many_parameters = 14
OTHERS = 15.
“Lectura del CSV para luego procesarlo como sea necesario
IF sy-subrc IS INITIAL.
OPEN DATASET lv_csv_file FOR INPUT IN TEXT MODE ENCODING DEFAULT.
DO 10 times.
IF sy-subrc IS INITIAL.
READ DATASET lv_csv_file INTO lv_line.
WRITE lv_line.
ELSE.
EXIT.
ENDIF.
ENDDO.
CLOSE DATASET lv_csv_file.
ENDIF.
ENDIF.
Finally I attach my python script that will be executed and convert my .XLS file to .CSV
import sys
import pandas as pd
def convert_xls_to_csv(xls_file_path):
try:
# Verifica si el archivo .xls existe
if not os.path.exists(xls_file_path):
print(f”El archivo {xls_file_path} no existe.”)
return
# Verifica que el archivo tenga extensión .xls
if not xls_file_path.endswith(“.xls”):
print(“El archivo no tiene la extensión .xls.”)
return
# Lee el archivo .xls
xls_data = pd.read_excel(xls_file_path, engine=”xlrd”)
# Genera la ruta del archivo .csv (misma ruta que el .xls)
csv_file_path = xls_file_path.replace(“.xls”, “.csv”)
# Convierte el archivo a formato .csv
xls_data.to_csv(csv_file_path, index=False)
print(f”El archivo se ha convertido y guardado como {csv_file_path}.”)
except Exception as e:
print(f”Ocurrió un error al convertir el archivo: {str(e)}”)
if __name__ == “__main__”:
# Verifica que se haya pasado un argumento (ruta del archivo .xls)
if len(sys.argv) != 2:
print(“Uso: python3 script.py /ruta/al/archivo/BigTable.xls”)
else:
# Toma el archivo .xls desde el parámetro pasado
file_path = sys.argv[1]
convert_xls_to_csv(file_path)
I find it simple, but it works, it can convert the file from the background. I appreciate comments on security issues that I may not be considering, technical issues, and anything else that can help me improve.
Thank you very much