Get date (dd.mm.yyyy) from excel and paste it to SAP field

Hello everyone :slight_smile:,

I have some difficulties, writing dates from my excel spreadsheet into an SAP field.
I need to type the dates from the columns “Startzeitraum” and “Endzeitraum” to the SAP field “Eckstarttermin”. I tried to declare variables and assign them to the rows, but I need the date format dd.MM.yyyy for SAP. I attached my code and the SAP transaction (COOIS).


2021-12-06 11_11_43-Evo's mit Vorgabezeit  Kappa - Excel

Hi @Agnetha

Use the below format to convert!

Assign Your var =DateTime.ParseExact(row("columnname ").ToString.Substring(0, 10),“dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture).toString("dd/MM/yyyy)

Regards

Depeneding on how the date is read in into the datatable from wxcel you can use
CDate
or ParseExact - when more control is needed:
grafik

@Agnetha

Check below link for your reference

Convert Date

Hope this will help you

Thanks

Hi @pravin_calvin,
I’m so sorry, I wrote the wrong format in my description… I need the format “dd.MM.yyyy” and NOT “dd/MM/yyyy”!
If i try to change the order of your code (DateTime.ParseExact(row("columnname ").ToString.Substring(0, 10),“dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture).toString(“dd.MM.yyyy”). I says that string is not as a valid date time

Hi @Agnetha

Just try removing the Substring(0,10) From the above format and try using below!

(DateTime.ParseExact(row("columnname ").ToString,“ dd.MM.yyyy ”, System.Globalization.CultureInfo.InvariantCulture).toString(“ dd.MM.yyyy ”).

Regards

@Agnetha
just check the present content within datatable:

  • set a breakpoint
  • debug and get paused
  • open immediate panel and type in
    YourDataTableVar.Roes(0)(YourColNameOrIndex).toString

just let us know the output and we can help on selecting the appropriiate conversion method

1 Like

It still says invalid data time format

DateTime.ParseExact(CurrentRow(“STARTZEITRAUM”).ToString,“ dd.MM.yyyy ”, System.Globalization.CultureInfo.InvariantCulture).toString(“ dd.MM.yyyy ”)

Hi @Agnetha

As @ppr Suggested try to print or watch the format that is coming from the excel and give us a screen shot for understanding the date format from the excel!

Regards

Hi @pravin_calvin and @ppr,
sorry for the delay! It outputs:
Kalenderwoche_Evos.Rows(0)(“STARTZEITRAUM”).ToString
“10/25/2021 00:00:00”
I

Hi @Agnetha

If its outputs like above the below expression would work!

DateTime.ParseExact(row("columnname “).ToString.Substring(0, 10),“MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).toString(“dd.MM.yyyy”)

Regards

It worked. Thanks a lot :slight_smile:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.