Post reply

Import excel data to sql server using ssis 2005 with derived column and substring with example

  • March 16, 2009 at 6:20 am

    #131826

    Please give with example

    I had excel file Person.XLS

    SNo PersonName/FatherName/RationCardNo

    1 rajaiah,s/o anjaiah,R100

    2 RAMARAO,s/o Somaiah,R101

    3 Laxmaiah,s/o anjaiah,R102

    4 anjaiah,s/o ramaiah,R103

    How to import the data from excel file to sqlserver 2005(PersonDet table)

    using with separate comma(By Using Derived Column and Substring) using ssis2005

    In sql server 2005 I had PersonDet table

    PersonDet

    ---------

    SNo

    Personname

    FatherName

    RCNo

  • March 16, 2009 at 7:29 am

    #960206

    Can you confirm that your Excel file contains only 2 columns? If so, that's an unusual format, but there should be a way ...

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • March 16, 2009 at 8:40 am

    #960275

    my excel file contains only two columns

  • March 16, 2009 at 9:24 am

    #960342

    Add a Derived Column transformation to break up column two as you need.

    The expression for column 1 will be something like this:

    SUBSTRING([PersonName/FatherName/RationCardNo],1,FINDSTRING([PersonName/FatherName/RationCardNo],",",1) - 1)

    That will extract all the text from position 1 to the position of the first comma, but not including the comma.

    I'm sure that you can work out the others.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • February 16, 2012 at 2:32 am

    #1447712

    ..

  • February 16, 2012 at 2:35 am

    #1447714

    rajendra prasad-377679 (2/16/2012)

    hi brother this is very very simple , for to do this u have to do little bit work in Excel.. just follow my steps..

    1.copy the content in Excel file and go to data--text to columns--select the delimited options-->next-->select the COMMA--give next --and give finish..now u get the perfect format in excel .save the file format in old version 97-2003 format

    if u need column heading pls include manually or else leave it and save it in your path .

    note : if you include heading you no need to create table in sql server

    if you are not include the heading u have to create the table structure..i am going to do easy format.my data contain heading in excel ..

    now go to Sql server 2005-- Right click the database --choose the tasks-->importdata--next--choose the datasource-->microsoft excel--browse your file--check the first row has acolumn name---next-->choose destination as SQL native client--sever name --username and password--next --select copy data from one or more tables or views--select the sheet no which the data contain--and select the destination

    [ReportServer].[dbo].[Sheet1$]--edit as [ReportServer].[dbo].[mydata]--next--check execute immediately--finish.

    u have got one message the executon was successful..

    go to sql server 2005 and check the table ..

    EG : select * from reportserver.dbo.mydata

    thank you ...

    Note: you are responding to a three-year-old post.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • February 16, 2012 at 2:36 am

    #1447715

    ..

Viewing 7 posts - 1 through 6 (of 6 total)

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