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: Yesterday @ 7:32 AM
Points: 100, Visits: 1,090
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 @ 6:37 AM
Points: 5,110, Visits: 11,914
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.
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: Yesterday @ 7:32 AM
Points: 100, Visits: 1,090
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 @ 6:37 AM
Points: 5,110, Visits: 11,914
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.
Post #676631
Posted Thursday, February 16, 2012 2:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 12:28 AM
Points: 14, Visits: 95
..
Post #1252943
Posted Thursday, February 16, 2012 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 5,110, Visits: 11,914
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.
Post #1252945
Posted Thursday, February 16, 2012 2:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 12:28 AM
Points: 14, Visits: 95
..
Post #1252946
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse