Post reply

Import text cell value from Excel to sql

  • December 7, 2015 at 3:46 am

    #305935

    Hi,

    Can someone please advice on how to import a value for a date column from excel to sql.

    Currently, when I try to import the time values greater than 24 hours from excel, the values get converted to the format such as '01/01/1900 20:50:06' in sql. The values less than or equal to 24 hours are displayed as they are like for e.g. 23:15:25.

    I just want the text value in the excel cell to be imported across as is and not get converted. The datatype for the associated column in sql is nvarchar.

    Any suggestions would be appreciated. Thanks.

  • December 7, 2015 at 5:45 am

    #1844342

    HI Mays,

    Same Issue was there when I'd work on the Excel to SQL and vice versa.

    This is ('01/01/1900 ) default time stamp.

    TRY This: - Select the column which you want in excel and Right click on that and click on format cells select the number tab then click on category as date further select type. Convert it into required format() then load the data into sql.

    If loading data from Sql to Excel then use this, Convert into Nvarchar and use ISNULL() function orelse case statement.

    Thanks,

    Cyed

  • December 7, 2015 at 7:49 am

    #1844385

    msbisyed (12/7/2015)

    HI Mays,

    Same Issue was there when I'd work on the Excel to SQL and vice versa.

    This is ('01/01/1900 ) default time stamp.

    TRY This: - Select the column which you want in excel and Right click on that and click on format cells select the number tab then click on category as date further select type. Convert it into required format() then load the data into sql.

    If loading data from Sql to Excel then use this, Convert into Nvarchar and use ISNULL() function orelse case statement.

    Thanks,

    Cyed

    Thanks for your reply. I tried what you suggested but it doesn't work and I am still stuck with the same default sql date format of 01/01/1900.

    The issue is I have values like 100:15:20 (where 100 is hours) in the excel time column which when imported to sql give the output like 01/01/1900 04:15:20. Other values like 21:02:10 get imported across exactly in sql. The issue only occurs when the values exceed 24 hours.

    Do you know if this can be resolved through SSIS by using some kind of transformation ? Thanks.

  • December 7, 2015 at 11:14 pm

    #1844549

    pwalter83 (12/7/2015)

    msbisyed (12/7/2015)

    HI Mays,

    Same Issue was there when I'd work on the Excel to SQL and vice versa.

    This is ('01/01/1900 ) default time stamp.

    TRY This: - Select the column which you want in excel and Right click on that and click on format cells select the number tab then click on category as date further select type. Convert it into required format() then load the data into sql.

    If loading data from Sql to Excel then use this, Convert into Nvarchar and use ISNULL() function orelse case statement.

    Thanks,

    Cyed

    Thanks for your reply. I tried what you suggested but it doesn't work and I am still stuck with the same default sql date format of 01/01/1900.

    The issue is I have values like 100:15:20 (where 100 is hours) in the excel time column which when imported to sql give the output like 01/01/1900 04:15:20. Other values like 21:02:10 get imported across exactly in sql. The issue only occurs when the values exceed 24 hours.

    Do you know if this can be resolved through SSIS by using some kind of transformation ? Thanks.

    What most people don't know is that the durations stored in Excel are still stored as Dates/Times but have been formatted to look otherwise. Unfortunately, SQL Server doesn't have such formatting and so such imported durations come out looking like dates and times. You have do use a formula to do your own conversion for display. Like this...

    DECLARE @SomeDateTime DATETIME;

    SELECT @SomeDateTime = '1900-01-05 04:15:20';

    SELECT [HHHHH:MM:SS] = CONVERT(VARCHAR(10),DATEDIFF(hh,0,@SomeDateTime)) --Converts the hours

    + RIGHT(CONVERT(CHAR(8),@SomeDateTime,108),6) --Converts mins & secs

    ;

    That give the following "display" result.

    HHHHH:MM:SS

    ----------------

    100:15:20

    (1 row(s) affected)

    You can convert a whole column of such durations simply by adding a FROM clause and changing @SomeDateTime in the formula above to the column name that contains the duration.

    I also do NOT recommend storing the formatted duration. Leave it as a DateTime datatype so that you can do other date math without having to do yet more conversions just to get it to be a DATETIME that you can calculate other duration math with.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 8, 2015 at 2:47 am

    #1844588

    Jeff Moden (12/7/2015)

    pwalter83 (12/7/2015)

    msbisyed (12/7/2015)

    HI Mays,

    Same Issue was there when I'd work on the Excel to SQL and vice versa.

    This is ('01/01/1900 ) default time stamp.

    TRY This: - Select the column which you want in excel and Right click on that and click on format cells select the number tab then click on category as date further select type. Convert it into required format() then load the data into sql.

    If loading data from Sql to Excel then use this, Convert into Nvarchar and use ISNULL() function orelse case statement.

    Thanks,

    Cyed

    Thanks for your reply. I tried what you suggested but it doesn't work and I am still stuck with the same default sql date format of 01/01/1900.

    The issue is I have values like 100:15:20 (where 100 is hours) in the excel time column which when imported to sql give the output like 01/01/1900 04:15:20. Other values like 21:02:10 get imported across exactly in sql. The issue only occurs when the values exceed 24 hours.

    Do you know if this can be resolved through SSIS by using some kind of transformation ? Thanks.

    What most people don't know is that the durations stored in Excel are still stored as Dates/Times but have been formatted to look otherwise. Unfortunately, SQL Server doesn't have such formatting and so such imported durations come out looking like dates and times. You have do use a formula to do your own conversion for display. Like this...

    DECLARE @SomeDateTime DATETIME;

    SELECT @SomeDateTime = '1900-01-05 04:15:20';

    SELECT [HHHHH:MM:SS] = CONVERT(VARCHAR(10),DATEDIFF(hh,0,@SomeDateTime)) --Converts the hours

    + RIGHT(CONVERT(CHAR(8),@SomeDateTime,108),6) --Converts mins & secs

    ;

    That give the following "display" result.

    HHHHH:MM:SS

    ----------------

    100:15:20

    (1 row(s) affected)

    You can convert a whole column of such durations simply by adding a FROM clause and changing @SomeDateTime in the formula above to the column name that contains the duration.

    I also do NOT recommend storing the formatted duration. Leave it as a DateTime datatype so that you can do other date math without having to do yet more conversions just to get it to be a DATETIME that you can calculate other duration math with.

    Thanks Jeff. Actually I am using 'Import data' functionality to copy data from excel to sql. Do you know how to incorporate the conversion code you mentioned within the Import Data functionality ? Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply