Importing data from excel and inserting into sql tables

Question

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 ?

Be careful here. You may see 07:00 on the screen in Excel, but that's just the formatting. How exactly are you picking this value up to be inserted into SQL? I'm willing to bet that you're using VBA and picking up the range's value? If so, debug your code and stop immediately after you've selected that value into a VBA variable, then have a look at what you're really inserting into SQL. — MatBailie
– MatBailie, Commented Nov 19, 2012 at 9:20

Jook · Accepted Answer · 2012-11-19 09:16:45Z

3

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.

edited Nov 19, 2012 at 9:16

answered Nov 19, 2012 at 9:10

Jook

4,7523 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lahib Over a year ago

Thank you for the answer. The Excel files are automatically generated so i would not be able to create the TEXT field as a reference to the time field. Im importing the using a OleDBConnection, OleDBCommand and a sql string.

Collectives™ on Stack Overflow

Importing data from excel and inserting into sql tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related