Import data from Excel and paste into sql tables

Question

I am importing data from excel and pasting it into sql table. One of the fields in my Excel file is filled with a time value. I want to import this time value as a string into sql table. When I do this, I get a weird value in my sql table. Excel value: 07:00 , and after pasting as a row into the sql table, the time values ​​look like this: 0.29166666667 .

The reason for importing it as a string value is that you should be able to define Days in one field. For example: D2 10:30 . When I import this type of value, it is inserted correctly.

can anyone help?

Answer 1

Excel stores dates and times as numeric values ​​from 0 to 0.99999999 + x days.

0.29166666667 will be like 00.01.1900 07:00:00 , which seems correct in your case.

So, you will need to use some reformatting or conversion of this value before using it as direct line input.

In VBA, you can use Format(myValue,"DD-MM-YYYY hh:mm:ss") .

The equivalent function of the worksheet will be TEXT(A1,"DD-MM-YYYY hh:mm:ss") .

The format code depends on your regional settings. You might want to try something like this Format(myTime, "Long Time") if you prefer to use time-forced time formats.

Since you have not published any code, I am not sure how you import your excel data. But I would say that the fastest way to get the best results is to create a new column using the TEXT formula with reference to the previous time column and use this new formatted column as input for your sql-db.