05-19-2008 2:58 PM
Dear Friends,
I am uploading a xls file into internal table using TEXT_CONVERT_XLS_TO_SAP.
The file strusture is as below.
Row1: Customer City
Row 2: Customer City
Row2: 2000001 ABD
My requirement is to skip the first two row details, as they are just the field titles and i need not consider the first two row's.
I should pick the data from Third row onwards which is actual data.
I have to use this TEXT_CONVERT_XLS_TO_SAP FM only.
I have passed the
i_line_header = 'X'
in the function module it is deleting only one header hower to remove the other header what i have to do , because the FM is throughing an error conversion_failed.
Please help me .
.
Thanks
madhuri
05-19-2008 5:42 PM
Hi,
You can do it as follows:
TYPE-POOLS:truxs.
DATA:li_tab_raw_data TYPE truxs_t_text_data.
DATA: BEGIN OF i_tab OCCURS 0,
fld1(6) TYPE c,
END OF i_tab.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR =
I_LINE_HEADER =
i_tab_raw_data = li_tab_raw_data
i_filename = 'C:\temp\test1_xls.xls'
TABLES
i_tab_converted_data = i_tab
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
LOOP AT i_tab.
IF sy-tabix EQ 1 OR sy-tabix EQ 2. "for first 2 rows
CONTINUE.
ENDIF.
ENDLOOP.
Or you can use following FM
ALSM_EXCEL_TO_INTERNAL_TABLE
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
filename = file
i_begin_col = 1
i_begin_row = 3 "to start from 3rd row
i_end_col = 2 "depends on # of col's
i_end_row = 65000
tables
intern = iexcel
exceptions
inconsistent_parameters = 1
upload_ole = 2
others = 3.
Thanks,
Vikram
05-19-2008 5:05 PM
Remove the line_header parameter and bring all the file in the internal table, then just in the loop of the internal table jump the first to lines.
LOOP AT GT_FILE.
IF SY-TABIX EQ 1 OR SY-TABIX EQ 2.
CONTINUE.
ENDIF.
...
ENDLOOP.
Regards,
Isaac Melendez
05-20-2008 5:53 AM
Dear Isaac Melendez,
I have commented and the i_line_header as below
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_raw " WORK TABLE
i_filename = p_file
TABLES
i_tab_converted_data = it_datatab[] "ACTUAL DATA
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
WRITE:/ 'Not able to open file'.
ENDIF.
then the function module has returned as conversion failed,
so for testing purpose with the same .xls file , i have removed one heading out of two headings and executed this function module ( by passingthe parameter
i_line_header = 'X' ) , then iam able to get data into the internal table it_datatab Successfully .
So could you please let me know where exactly iam going wrong in the scenario where iam not able remove the headings of the flat file in my coding .
regards
madhuri
05-19-2008 5:23 PM
Hi Madhuri,
I think you can delete the header rows from the xls file itself before uploading.
05-19-2008 5:42 PM
Hi,
You can do it as follows:
TYPE-POOLS:truxs.
DATA:li_tab_raw_data TYPE truxs_t_text_data.
DATA: BEGIN OF i_tab OCCURS 0,
fld1(6) TYPE c,
END OF i_tab.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR =
I_LINE_HEADER =
i_tab_raw_data = li_tab_raw_data
i_filename = 'C:\temp\test1_xls.xls'
TABLES
i_tab_converted_data = i_tab
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
LOOP AT i_tab.
IF sy-tabix EQ 1 OR sy-tabix EQ 2. "for first 2 rows
CONTINUE.
ENDIF.
ENDLOOP.
Or you can use following FM
ALSM_EXCEL_TO_INTERNAL_TABLE
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
filename = file
i_begin_col = 1
i_begin_row = 3 "to start from 3rd row
i_end_col = 2 "depends on # of col's
i_end_row = 65000
tables
intern = iexcel
exceptions
inconsistent_parameters = 1
upload_ole = 2
others = 3.
Thanks,
Vikram
05-20-2008 6:00 AM
Dear Vikram,
I have done in my coding as below
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_raw " WORK TABLE
i_filename = p_file
TABLES
i_tab_converted_data = it_datatab[] "ACTUAL DATA
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc 0.
WRITE:/ 'Not able to open file'.
ENDIF.
then the function module has returned as conversion failed,
so as per your answer you have said to get first into the internal table the function module itself returning as conversion failed ..........so iam not able to get into the internal table if iam commenting the parameter I_LINE_HEADER = 'X'.
however i have made one test where by removing one header details directly in my flat file and executed this FM by
passing I_LINE_HEADER = 'X' then it is skiping one header and successfully getting data into the internal table it_datatab.
so could you please let me know how to skip the two headers which are there in my flat file ?
However i canot use the function module ALSM_EXCEL_TO_INTERNAL_TABLE i have to only use
TEXT_CONVERT_XLS_TO_SAP .
Thanks & regards
madhuri.
05-20-2008 6:30 AM
Hi Madhuri,
Why are u sticking to TEXT_CONVERT_XLS_TO_SAP FM when there are somany FMs like KCD_EXCELL_OLE_TO_INT_CONVERT
ALSM_EXCEL_TO_INTERNAL_TABLE
are available which serves the same purpose.
Even in this case u can simply delete the header records after upload. First upload the data to ur itab. Next delete the records of header details. Number of records to be deleted will be fixed based on ur input file structure.
I think u can use FM ALSM_EXCEL_TO_INTERNAL_TABLE. But u have to convert the itab again with this FM. It will upload the data based on row ID. If u have 2 fields in ur file then u will get 2 records in ur itab for each record in ur file.
eg:
ROWID: 1 COLID:1 DATA....
ROWID:1 COLID:2 DATA...
Thanks,
Vinod.