CODEWITHSUNDEEP
sqlsql-serverexcel
Lahib
Lahib

Reputation: 1365

Importing data from excel and inserting into sql tables

I am Importing data from excel and inserting into a sql table. One of the fields in my Excel file is populated with a time value. I want to import that time value as a string into my sql table. When i do that i get a weird value in my sql table. Excel value is: 07:00 and after inserting that as a string into the sql table the time values looks like this: 0,29166666667.

The reason for importing it as a string value is that you have to be able to define Days in the same field. Like this : D2 10:30. When i import this kind of values it is inserted correctly.

can anyone help ?

Upvotes: 4

Views: 1596

Answers (1)

Jook
Jook

Reputation: 4682

Excel stores dates and times as number-values from 0 to 0.99999999 +x days.

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

So, you would have to use some reformatting or conversion of this value, before using it as a direct string-input.

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

The equivalent worksheet function would 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 preffer to use excel-defined time-formats.

Because you did not post any code, I am not sure about how you import your excel-data. But I would say, the fastest way to get better results, would be setting up a new column, using the TEXT formula with a reference to the previously time-column and use this new formatted column as input for your sql-db.

Upvotes: 3

Related Questions