topic Re: TEXT_CONVERT_XLS_TO_SAP wrong date format. in Application Development and Automation Discussions

TEXT_CONVERT_XLS_TO_SAP wrong date format.

former_member432660 — Thu, 14 Oct 2010 11:21:31 GMT

all experts, pls help

in my report i am using FM "SAP_CONVERT_TO_XLS_FORMAT" to download the desired data in excel file which works correctly. but as per my requirement , we need to upload the same edited excel for batch updation. In this one of the date column in excel (BSAK-AUGDT) in excel value is '04/05/2005' but when i upload the excel Using "TEXT_CONVERT_XLS_TO_SAP" the date which is stored in Itab is in wrong format i.e. "05.05.0040". kindly resolve at the earliest

Thanks.

Re: TEXT_CONVERT_XLS_TO_SAP wrong date format.

Former Member — Fri, 15 Oct 2010 04:32:35 GMT

Mandar,

May be the date format in excel should be yyyymmdd

Thanks

Bala Duvvuri

Re: TEXT_CONVERT_XLS_TO_SAP wrong date format.

Former Member — Fri, 15 Oct 2010 06:03:03 GMT

Hi,

change date field as char10.

eg.

types: begin of ty_test,

a type c,

b yupe c,

c type char10, " this is the field for date it ur internal table must be type d, change it to char10 it will be resolved

End of ty_test.

data : itab type table of ty_test.

call function 'SAP_CONVERT_TO_XLS_FORMAT'

.............

tables I_TAB_SAP_DATA = itab[] .

.........

this should solve ur issue.

Re: TEXT_CONVERT_XLS_TO_SAP wrong date format.

former_member225631 — Fri, 15 Oct 2010 07:00:51 GMT

Change the values '04/05/2005' to '04.05.2005' in excel and upload. You can get correct value in internal table.

Re: TEXT_CONVERT_XLS_TO_SAP wrong date format.

former_member432660 — Wed, 20 Oct 2010 10:20:49 GMT

Finaly i got the solution for the above problem.

I have upload the excel file to the internal table using TEXT_CONVERT_XLS_TO_SAP. in the ITAB associated with this FM i took AUGDT (date field) as CHAR10. once I got the data in an internal table, I ve manually correct it using string offset.

Eg.

loop at itab_upload into wa_upload.

wa_bsak-bukrs = wa_upload-bukrs .

str = wa_upload-augdt. " date field in CHAR10 form.

concatenate str6(4) str3(2) str+0(2) into test_date. " which give me YYYYMMDD

wa_bsak-augdt = test_date. " Corrected Date.

wa_bsak-lifnr = wa_upload-lifnr.

wa_bsak-name1 = wa_upload-name1.

wa_bsak-augbl = wa_upload-augbl .

wa_bsak-dmbtr = wa_upload-dmbtr.

wa_bsak-monat = wa_upload-monat .

wa_bsak-gjahr = wa_upload-gjahr.

wa_bsak-belnr = wa_upload-belnr.

wa_bsak-sgtxt = wa_upload-sgtxt.

append wa_bsak to itab_bsak.

endloop.

Thanks to ALL

Regards:

Mandar G Kambli.

Edited by: Mandar G. Kambli on Oct 20, 2010 12:21 PM

Re: TEXT_CONVERT_XLS_TO_SAP wrong date format.

asik_shameem — Wed, 20 Oct 2010 12:11:16 GMT

Hello Mandar,

That is not the right way.

Actually the FM TEXT_CONVERT_XLS_TO_SAP takes the date format from the user profile T-Code SU3.

Change the date format in user profile according to your excel sheet or vice-versa.

Re: TEXT_CONVERT_XLS_TO_SAP wrong date format.

former_member432660 — Fri, 22 Oct 2010 03:52:42 GMT

i ve tried it, i ve changed the date format from SU3 but it didnt solve the wrong date format problem. ultimately i have to use the above method.

Thanks.

Re: TEXT_CONVERT_XLS_TO_SAP wrong date format.

Former Member — Mon, 25 Oct 2010 07:40:23 GMT

Hello,

If you got ur answer then close the thread.