Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Import excel data to sql server using ssis 2005 with derived column and substring with example Expand / Collapse
Author
Message
Posted Monday, March 16, 2009 6:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:17 PM
Points: 128, Visits: 1,127
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

Post #676417
Posted Monday, March 16, 2009 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
Can you confirm that your Excel file contains only 2 columns? If so, that's an unusual format, but there should be a way ...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #676473
Posted Monday, March 16, 2009 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:17 PM
Points: 128, Visits: 1,127
my excel file contains only two columns
Post #676549
Posted Monday, March 16, 2009 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #676631
Posted Thursday, February 16, 2012 2:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:48 AM
Points: 14, Visits: 96
..
Post #1252943
Posted Thursday, February 16, 2012 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1252945
Posted Thursday, February 16, 2012 2:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:48 AM
Points: 14, Visits: 96
..
Post #1252946
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse