Post reply

Improper data while importing from excel to sql

  • February 3, 2014 at 11:53 pm

    #283123

    Hi,

    Attached Herewith is the test file,which has a column test,

    I want to import this file in sql 2008 r2.

    But after importing the data is not appropriate,

    For eg,

    1284/1 imported as null

    2011052003069 imported as 20122+e00

    I had tried by converting the datatype in varchar,numeric.

    It seems to be the Datatype issue,

    but I am not able to get it correctly.

    Please guide me in this,

    I required this urgently.

    Thanks in Advance !!

  • February 3, 2014 at 11:57 pm

    #1685742

    Read this article, it has the solution to your problem:

    What’s the deal with Excel & SSIS?[/url]

    (tip: IMEX and TypeGuessRows)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 4, 2014 at 2:11 am

    #1685755

    Hi Koen,

    This is really a greate article,

    but according to it,I done me setting to

    TypeGuessRows

    ImportMixedTypes

    IMEX=1

    but according solution part II in this article,

    I had made this column as general,

    But problem now is data 2011053006233 is imported as 2.0133970249e+012

    rest of thing are ok.

    Thanks in Advance!!

  • February 4, 2014 at 2:36 am

    #1685757

    avdhut.k (2/4/2014)

    but according solution part II in this article,

    I had made this column as general,

    But problem now is data 2011053006233 is imported as 2.0133970249e+012

    Excel will detect this as a numeric column, but since it is such a large number it uses scientific notation.

    Try to read it as text (set the column as text) and convert it in the SSIS data flow to the correct data type.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 4, 2014 at 3:28 am

    #1685767

    Thanks Koen

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

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