Converting values from excel into SAP format

[Former Member]

Hi,

I have a requirement to create a BDC session through a program.

The program uploads the .CSV format excel sheet and then put into an internal table.then processes this internal table.

Now, the user can put the values in excel in any way or notation.

Date format can be many as well as amount format.

There are many values which are changes in excel sheet like too large numbers are converted into exponential values.

How do i convert these values (amount, date, exponential values) in the notation configured in the user profile.

Please help..!!

Regards,

Richa Aggarwal

Edited by: richa.aggarwal1988 on Nov 8, 2011 6:07 AM

[Former Member]

Hi ,

Just expand width of the excel sheet column amount vlaues automarically comes into decimal format.

[Former Member]

You can't expect a program (or even a human) to know that 03/02/2011 is March, 2nd or Feb, 3rd of 2011 unless the date format is specified. There should be consistency in the way data is entered by business user. I would suggest to ask them use an excel sheet that has automatic checks to convert fields into well defined formats.

[Former Member]

Thanks for the reply.

Expanding the excel sheet will not help because it can be uploaded in the program only when it is closed.

@Vishnu : Can there be a possibility to check the format which is set for the user in his profile and then do the conversions accordingly?

[Former Member]

To convert to user format, first you need to know which format the input value is.

If it is a date you can use SPLIT command and reformat it to YYYYMMDD assign it to a date type field and use WRITE command to write to BDC variable. WRITE command will automatically convert to date format as per user profile, but to use WRITE command you need to have a date type variable (TYPE D or LIKE sy-datum).

WRITE gv_rundate TO gv_today.

Functions like CONVERT_DATE_TO_INTERNAL assume that your input date is already formatted as per user profile date format parameter in SU01, so they won't work if your input date is different.

[Former Member]

Vishnu : But i may not always know that in which format the user is entering the date. it can be user of decimal or slash or hiphen. Then it can be any like DDMMYYYY or MMDDYYYY or YYYYMMDD

[Former Member]

So tell me what is 03022011 for you, is it MMDDYYYY or DDMMYYYY?

If you can tell what the user meant by that date value, then the program can too interpret what the user intended

[Former Member]

Yes there are conversions available. For e.g for Date FM used is CONVERT_DATE_TO_INTERNAL

similarily u can see for time also

[Former Member]

Hi,

The solutionwas found.

In excel sheet all the amount type fields should have the category as 'General' but not as number.

Steps to change category :

Right Click on the cell -> Format cells-> category as 'General'

In program also, the currency fields should be declared as CHAR type.

Thanks.

Regards,

Richa Aggarwal