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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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