Post reply

Data import from excel

  • August 23, 2011 at 5:12 am

    #142830

    Hi,

    I am trying to import data from excel to SQL server database (2005). How can I import data from excel which having different data type in single column.

    For example 60345621 and 603-45621. The destination column datatype is varchar

    Also how can I import data as it is from excel. In excel if the data is "603 45621", it is inserted in the table as "60345621".

    Any body can help me to get rid from this issue.

    Thanks in advance.

    Animesh

  • August 23, 2011 at 7:05 am

    #1371960

    Use a data conversion transform right after your Excel source and explicitly cast all your columns as your desired destination data type.

    I am not sure what you want in the second part of the question. Do you want to retain the space or delete the space? If you want to retain the space, make sure your data flow and destination uses a data type that allows spaces in the middle such as DT_WSTR or DT_STR. If you want to delete the space use a derived column transform and use the Replace function.

  • August 23, 2011 at 9:34 am

    #1372100

    Thanks Daniel for your reply.

    For first question, I want the data as below

    Excel Data Table Data Desire Table Data

    60345621 60345621 60345621

    603-45621 NULL 603-45621

    For the second question I want the space in the data. I have the data type of that column as varchar, and I am using DT_STR data type and Code Page as 1251 (ANSI - Latin 1) in the data conversion. But when it inserted it's remove the space which I don't want.

    Excel Data Table Data Desire Table Data

    60345621 60345621 60345621

    603 45621 60345621 603 45621

    Please help me to solve this issue.

    Regards

    Animesh

  • August 24, 2011 at 7:31 am

    #1372556

    Make sure you read everything in as text.

    To do that, put IMEX=1 into your connection string to the excel file (look in the properties of the connection manager).

    For an example, check http://www.connectionstrings.com.

    Furthermore, in the registry, set the TypeGuessRows registry setting for the JET provider to 0.

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

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

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