Failed to convert parameter value from a Double to a DateTime - Insert DB

[AaronMark]

Hi to all,
I have a problem importing a file to DB.
Test.xls (4,0 MB)

I need to upload this File to a database.
I created the database table, but I have a failure with the IMPORT command.

Depends on what? the date formatting looks correct.
Can anyone help me?
Bot_Credit_Managment.xaml (11,0 KB)

My table on db:

What am I doing wrong?
Thanks…
Aaron

[Sarath_Sasidharan1 (Sarath Sasidharan)]

Hey you have 05 date fields in your DB, make sure the excel that you are reading also gives you the fields in same date datatype.
else you loop thru the table and convert these five field into a date type before importing to DB

[Sarath_Sasidharan1 (Sarath Sasidharan)]

I ran thru your excel the date values are coming as OADate format Number as below, Do one simple thing replace the workbook read range activity with an Excel Application Scope read range the issue will get resolved.(2nd screenshot)

[Sarath_Sasidharan1 (Sarath Sasidharan)]

[AaronMark]

Hi Sarath,
unfortunately I can’t use the Excel application.
My remote server has no office so I need to find a workaround. If it exists.

[AaronMark]

That is,
are you telling me that the alternative is a Build Data Table and import the data through the For Each command?

[Sarath_Sasidharan1 (Sarath Sasidharan)]

Hey Use a For each row data table activity and convert the date fields to Date.

Use an Assign activity

Left Hand side > Row(“ColumnName”)

RHS >

[AaronMark]

Hi Sarath,
ok, i entered this string.
I don’t understand how to make it work with importing the table to DB though.
After converting the 5 data columns in this way, can I use the INSERT DATA TABLE command?

[Sarath_Sasidharan1 (Sarath Sasidharan)]

yes, once you convert all the date field to date type then the double data type issue will get resolved. Try that first and let’s see what comes after it

[AaronMark]

We are close.
The error is now: Data too long for column ‘Customer_Deactivation_Date’ at row 1",
Obviously I converted 10 characters for the date
and then added hh mm ss.
Can I possibly cut and take only the first 10 characters?

Left(datetime.FromOADate(cdbl(row(“Ends_Date_1B”))), 10)
could this make sense?

[Sarath_Sasidharan1 (Sarath Sasidharan)]

Or simply you can use Date.FromOADate instead of DateTime

[AaronMark]

Hi Sarath…
i solved the problem, maybe related to some formatting in the cells.
Now I have managed to import the whole file.
I hope it will not show up in the future, having to work independently.
Thanks for your invaluable help!
The suggestion you gave me was decisive.