Solved

export data from Excel to SQL Server

Posted on 2011-09-26
34
255 Views
Last Modified: 2012-05-12
Hi,
I have an Excel with data, I want to export this data to my SQL data table, i tried to this using the query like -
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
I got this from - http://support.microsoft.com/kb/321686 
but the problem is it took the datatypes as it can, but I already have a designed table with particular datatypes I want the data to be Exported as I designed the datatable without changing the datatypes of the table. In-fact, it created a new data table but I want the data to exported to the data table that i designed.
How can i do this? Please help me.
Thanks in advance.
0
Comment
Question by:CPSRI
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 14
  • +1
34 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36598042
Use Import/Export manager (SSIS) in SQL Server to pull data in. Point it to a local file and then go through the wizard and point it to a target table.

http://support.microsoft.com/kb/321686

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36598135
Hi,

Have you tried to casting the value?

I mean Insert data using Cast.

Insert into table
(col1,col2)
select cast(col1 as varchar(30)),col2 from excel....

something like that

- Bhavesh
0
 

Author Comment

by:CPSRI
ID: 36598174
thank you for your replies, Isavidge i need to go through the link that you gave me and Brichsoft, can you give me the query clearly with an example, please?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36598196
Hi,

Assume,
You have one table having 5 columns.

col1 varchar
col2 int
col3 numeric(30,4)
col4 varchar(30)
col5 datetime



above is your table structure.

This should be query.

INSERT INTO Table1
(col1,col2,col3,col4,col5)
SELECT col1,
		cast(col2 as Int), -- integer col
		cast(col3 as numeric(30,4)), -- numeric col wid precison
		col4, -- varchar column
		convert(datetime, cast(col5 as varchar(10), 103)) -- Datetime col	
		
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

Open in new window



- Bhavesh
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36598206
Hi,

I would also suggest to use Bulk Insert method because openquery requires some sort of configuartion too.
It works on your server, but if you need to deploy somewhere else then it might creates the problems.
             
http://msdn.microsoft.com/en-us/library/ms188365.aspx
             

- Bhavesh
0
 

Author Comment

by:CPSRI
ID: 36598599
Bhavesh, I tried the query that you gave me but I got this error - Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF.
How can i resolve this?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36598639
Hi,

Before inserting

run
SET IDENTITY_INSERT TableName ON

after inserting


SET IDENTITY_INSERT TableName OFF

-Bhavesh
0
 

Author Comment

by:CPSRI
ID: 36598769
I got one more error like -
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I also attached the query i am trying to execute, can you please go through that?
SET IDENTITY_INSERT TKT_Member ON
INSERT INTO TKT_Member
(MemberID,TKTMemID,DateOfVisited,Gender,Title,Name,FamilyName,DateOfBirth,Age,EmailID,FName,FPhone,FOccupation,
Occupation,Contact,Address,Country,State,City,Area,Zone,MCAName,ZPName,ZPID,MCAID,PinCode,WhomeDoKnwInChurch,
DoYouWantVisit,IWouldLike,MyReligiousBackground,DoYouAttendAnotherChurch,NameOfChurch,Member,NonRegMember,Sanctified,CellGroup,Foundation,SpirtualCamp,SpirtualCampDate,FoundationWeek,FoundationDate,DEName,DateOfTKTMemID,
CommingChurchRegFrom,PlaceOfBirth,OfficePhNo,PermanentAddress,NameOfOffice,PreOfficeEmployed,NameOfEducationInstitute,
PrevEducationalInstitute,AreYouWaterBaptised,SpeekingToungses,HobbiesOrIntrestedArea,BankingWith,AnyAreaWouldYouLikeInvolvedINMinistry,
Qualification,MaritalStatus,NameOfSpouse,DateOfMarriage,SpousePlaceOfWork,SpouseContactNum,SpouseEmailID,SpouseDOB,
NumOfChildreans,ChildName1,ChildDOB1,EducationOrOffice1,ChildName2,ChildDOB2,EducationOrOffice2,ChildName3,ChildDOB3,EducationOrOffice3,
ChildName4,ChildDOB4,EducationOrOffice4,ChildName5,ChildDOB5,EducationOrOffice5)
SELECT cast(MemberID as int),
cast(TKTMemID as Int), -- integer col
convert(datetime, cast(DateOfVisited as varchar(10)), 103), -- Datetime col
CAST(Gender as nvarchar(10)),
cast (Title as nvarchar(50)),
cast (Name  as nvarchar(50)),
cast (FamilyName as nvarchar(50)),
convert(datetime, cast(DateOfBirth as varchar(10)), 103), -- Datetime col
cast(Age as Int),
cast (EmailID as nvarchar(50)),
cast (FName  as nvarchar(50)),
cast (FPhone  as nvarchar(50)),
cast (FOccupation  as nvarchar(50)),
cast (Occupation  as nvarchar(50)),
cast (Contact  as varchar(50)),
cast (Address  as varchar(50)),
cast (Country  as varchar(50)),
cast (State  as varchar(50)),
cast (City  as varchar(50)),
cast (Area  as varchar(50)),
cast (Zone  as varchar(50)),
cast (MCAName  as varchar(50)),
cast (ZPName  as nvarchar(50)),
convert(uniqueidentifier, cast(ZPID as varchar(10)), 103),
convert(uniqueidentifier, cast(MCAID as varchar(10)), 103),
cast (PinCode  as nvarchar(50)),
cast (WhomeDoKnwInChurch  as nvarchar(50)),
cast (DoYouWantVisit  as nvarchar(50)),
cast (IWouldLike  as nvarchar(50)),
cast (MyReligiousBackground  as nvarchar(50)),
cast (DoYouAttendAnotherChurch  as nvarchar(50)),
cast (NameOfChurch  as nvarchar(50)),
cast (Member  as nvarchar(50)),
cast (NonRegMember  as nvarchar(50)),
cast (Sanctified  as nvarchar(50)),
cast (CellGroup  as nvarchar(50)),
cast (Foundation  as nvarchar(50)),
cast (SpirtualCamp  as nvarchar(50)),
convert(datetime, cast(SpirtualCampDate as varchar(10)), 103), 
cast (FoundationWeek  as nvarchar(50)),
convert(datetime, cast(FoundationDate as varchar(10)), 103), 
cast (DEName  as nvarchar(50)),
convert(datetime, cast(DateOfTKTMemID as varchar(10)), 103), 
convert(datetime, cast(CommingChurchRegFrom as varchar(10)), 103), 
cast (PlaceOfBirth  as nvarchar(50)),
cast (OfficePhNo  as nvarchar(15)),
cast (PermanentAddress  as nvarchar(500)),
cast (NameOfOffice  as nvarchar(50)),
cast (PreOfficeEmployed  as nvarchar(50)),
cast (NameOfEducationInstitute  as nvarchar(50)),
cast (PrevEducationalInstitute  as nvarchar(50)),
cast (AreYouWaterBaptised  as nvarchar(5)),
cast (SpeekingToungses  as nvarchar(5)),
cast (HobbiesOrIntrestedArea  as nvarchar(500)),
cast (BankingWith  as nvarchar(50)),
cast (AnyAreaWouldYouLikeInvolvedINMinistry  as nvarchar(50)), 
cast (Qualification  as nvarchar(50)),
cast (MaritalStatus  as nvarchar(50)),
cast (NameOfSpouse  as nvarchar(50)),
convert(datetime, cast(DateOfMarriage as varchar(10)), 103),
cast (SpousePlaceOfWork  as nvarchar(50)),
cast (SpouseContactNum  as nvarchar(15)),
cast (SpouseEmailID  as nvarchar(50)),
convert(datetime, cast(SpouseDOB as varchar(10)), 103),
cast (NumOfChildreans  as nvarchar(50)),
cast (ChildName1  as nvarchar(50)),
convert(datetime, cast(ChildDOB1 as varchar(10)), 103),
cast (EducationOrOffice1  as nvarchar(50)),
cast (ChildName2  as nvarchar(50)),
convert(datetime, cast(ChildDOB2 as varchar(10)), 103),
cast (EducationOrOffice2  as nvarchar(50)),
cast (ChildName3  as nvarchar(50)),
convert(datetime, cast(ChildDOB3 as varchar(10)), 103),
cast (EducationOrOffice3  as nvarchar(50)),
cast (ChildName4  as nvarchar(50)),
convert(datetime, cast(ChildDOB4 as varchar(10)), 103),
cast (EducationOrOffice4  as nvarchar(50)),
cast (ChildName5  as nvarchar(50)),
convert(datetime, cast(ChildDOB5 as varchar(10)), 103),
cast (EducationOrOffice5  as nvarchar(50))

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\AllZonesData-ProcessedForUploadToDB.xls;Extended Properties=Excel 8.0')...[Database$]
SET IDENTITY_INSERT TKT_Member OFF

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36598794
can you share the excel sheet?

it seems problem in date.
0
 

Author Comment

by:CPSRI
ID: 36598838
here i attached the Excel with one row..but in original file approx i have 2000 rows.
AllZonesData-ProcessedForUploadT.xls
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36598886
All datetime column should contain "DD-MM-YYYY" OR "MM-DD-YYYY" format.
in given row "CommingChurchRegFrom" contains "Year 2004"

This can't be converted.
0
 

Author Comment

by:CPSRI
ID: 36598922
can we change our query to insert null value if such a kind of data exists?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36598993
Hi,


You can use IsDate function.
case when isDate(col)=1 then col else null end

but isDate function strictly check the format of date.

This are the valid formats.

http://www.sql-server-helper.com/tips/date-formats.aspx
0
 

Author Comment

by:CPSRI
ID: 36599048
I tried it but again i am getting one error,
can you explain me with my query please?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36599094
hi,

i will explain, can you re-post your final query
0
 

Author Comment

by:CPSRI
ID: 36599108
here i attached the code..
SET IDENTITY_INSERT TKT_Member ON
INSERT INTO TKT_Member
(MemberID,TKTMemID,DateOfVisited,Gender,Title,Name,FamilyName,DateOfBirth,Age,EmailID,FName,FPhone,FOccupation,
Occupation,Contact,Address,Country,State,City,Area,Zone,MCAName,ZPName,ZPID,MCAID,PinCode,WhomeDoKnwInChurch,
DoYouWantVisit,IWouldLike,MyReligiousBackground,DoYouAttendAnotherChurch,NameOfChurch,Member,NonRegMember,Sanctified,CellGroup,Foundation,SpirtualCamp,SpirtualCampDate,FoundationWeek,FoundationDate,DEName,DateOfTKTMemID,
CommingChurchRegFrom,PlaceOfBirth,OfficePhNo,PermanentAddress,NameOfOffice,PreOfficeEmployed,NameOfEducationInstitute,
PrevEducationalInstitute,AreYouWaterBaptised,SpeekingToungses,HobbiesOrIntrestedArea,BankingWith,AnyAreaWouldYouLikeInvolvedINMinistry,
Qualification,MaritalStatus,NameOfSpouse,DateOfMarriage,SpousePlaceOfWork,SpouseContactNum,SpouseEmailID,SpouseDOB,
NumOfChildreans,ChildName1,ChildDOB1,EducationOrOffice1,ChildName2,ChildDOB2,EducationOrOffice2,ChildName3,ChildDOB3,EducationOrOffice3,
ChildName4,ChildDOB4,EducationOrOffice4,ChildName5,ChildDOB5,EducationOrOffice5)
SELECT cast(MemberID as int),
cast(TKTMemID as Int), -- integer col
convert(datetime, cast(DateOfVisited as varchar(10)), 103), -- Datetime col
CAST(Gender as nvarchar(10)),
cast (Title as nvarchar(50)),
cast (Name  as nvarchar(50)),
cast (FamilyName as nvarchar(50)),
convert(datetime, cast(DateOfBirth as varchar(10)), 103), -- Datetime col
cast(Age as Int),
cast (EmailID as nvarchar(50)),
cast (FName  as nvarchar(50)),
cast (FPhone  as nvarchar(50)),
cast (FOccupation  as nvarchar(50)),
cast (Occupation  as nvarchar(50)),
cast (Contact  as varchar(50)),
cast (Address  as varchar(50)),
cast (Country  as varchar(50)),
cast (State  as varchar(50)),
cast (City  as varchar(50)),
cast (Area  as varchar(50)),
cast (Zone  as varchar(50)),
cast (MCAName  as varchar(50)),
cast (ZPName  as nvarchar(50)),
convert(uniqueidentifier, cast(ZPID as varchar(10)), 103),
convert(uniqueidentifier, cast(MCAID as varchar(10)), 103),
cast (PinCode  as nvarchar(50)),
cast (WhomeDoKnwInChurch  as nvarchar(50)),
cast (DoYouWantVisit  as nvarchar(50)),
cast (IWouldLike  as nvarchar(50)),
cast (MyReligiousBackground  as nvarchar(50)),
cast (DoYouAttendAnotherChurch  as nvarchar(50)),
cast (NameOfChurch  as nvarchar(50)),
cast (Member  as nvarchar(50)),
cast (NonRegMember  as nvarchar(50)),
cast (Sanctified  as nvarchar(50)),
cast (CellGroup  as nvarchar(50)),
cast (Foundation  as nvarchar(50)),
cast (SpirtualCamp  as nvarchar(50)),
convert(datetime, cast(SpirtualCampDate as varchar(10)), 103), 
cast (FoundationWeek  as nvarchar(50)),
convert(datetime, cast(FoundationDate as varchar(10)), 103), 
cast (DEName  as nvarchar(50)),
convert(datetime, cast(DateOfTKTMemID as varchar(10)), 103), 
convert(datetime, cast(CommingChurchRegFrom as varchar(10)), 103), 
cast (PlaceOfBirth  as nvarchar(50)),
cast (OfficePhNo  as nvarchar(15)),
cast (PermanentAddress  as nvarchar(500)),
cast (NameOfOffice  as nvarchar(50)),
cast (PreOfficeEmployed  as nvarchar(50)),
cast (NameOfEducationInstitute  as nvarchar(50)),
cast (PrevEducationalInstitute  as nvarchar(50)),
cast (AreYouWaterBaptised  as nvarchar(5)),
cast (SpeekingToungses  as nvarchar(5)),
cast (HobbiesOrIntrestedArea  as nvarchar(500)),
cast (BankingWith  as nvarchar(50)),
cast (AnyAreaWouldYouLikeInvolvedINMinistry  as nvarchar(50)), 
cast (Qualification  as nvarchar(50)),
cast (MaritalStatus  as nvarchar(50)),
cast (NameOfSpouse  as nvarchar(50)),
convert(datetime, cast(DateOfMarriage as varchar(10)), 103),
cast (SpousePlaceOfWork  as nvarchar(50)),
cast (SpouseContactNum  as nvarchar(15)),
cast (SpouseEmailID  as nvarchar(50)),
convert(datetime, cast(SpouseDOB as varchar(10)), 103),
cast (NumOfChildreans  as nvarchar(50)),
cast (ChildName1  as nvarchar(50)),
convert(datetime, cast(ChildDOB1 as varchar(10)), 103),
cast (EducationOrOffice1  as nvarchar(50)),
cast (ChildName2  as nvarchar(50)),
convert(datetime, cast(ChildDOB2 as varchar(10)), 103),
cast (EducationOrOffice2  as nvarchar(50)),
cast (ChildName3  as nvarchar(50)),
convert(datetime, cast(ChildDOB3 as varchar(10)), 103),
cast (EducationOrOffice3  as nvarchar(50)),
cast (ChildName4  as nvarchar(50)),
convert(datetime, cast(ChildDOB4 as varchar(10)), 103),
cast (EducationOrOffice4  as nvarchar(50)),
cast (ChildName5  as nvarchar(50)),
convert(datetime, cast(ChildDOB5 as varchar(10)), 103),
cast (EducationOrOffice5  as nvarchar(50))

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\AllZonesData-ProcessedForUploadToDB.xls;Extended Properties=Excel 8.0')...[Database$]
SET IDENTITY_INSERT TKT_Member OFF

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36599151
i did it for first two columns.

follow same for rest of fields.
INSERT INTO TKT_Member
(MemberID,TKTMemID,DateOfVisited,Gender,Title,Name,FamilyName,DateOfBirth,Age,EmailID,FName,FPhone,FOccupation,
Occupation,Contact,Address,Country,State,City,Area,Zone,MCAName,ZPName,ZPID,MCAID,PinCode,WhomeDoKnwInChurch,
DoYouWantVisit,IWouldLike,MyReligiousBackground,DoYouAttendAnotherChurch,NameOfChurch,Member,NonRegMember,Sanctified,CellGroup,Foundation,SpirtualCamp,SpirtualCampDate,FoundationWeek,FoundationDate,DEName,DateOfTKTMemID,
CommingChurchRegFrom,PlaceOfBirth,OfficePhNo,PermanentAddress,NameOfOffice,PreOfficeEmployed,NameOfEducationInstitute,
PrevEducationalInstitute,AreYouWaterBaptised,SpeekingToungses,HobbiesOrIntrestedArea,BankingWith,AnyAreaWouldYouLikeInvolvedINMinistry,
Qualification,MaritalStatus,NameOfSpouse,DateOfMarriage,SpousePlaceOfWork,SpouseContactNum,SpouseEmailID,SpouseDOB,
NumOfChildreans,ChildName1,ChildDOB1,EducationOrOffice1,ChildName2,ChildDOB2,EducationOrOffice2,ChildName3,ChildDOB3,EducationOrOffice3,
ChildName4,ChildDOB4,EducationOrOffice4,ChildName5,ChildDOB5,EducationOrOffice5)
SELECT cast(MemberID as int),
cast(TKTMemID as Int), -- integer col
case when isDate(DateOfVisited) = 1 then convert(datetime, cast(DateOfVisited as varchar(10)), 103) else null end,-- Datetime col
CAST(Gender as nvarchar(10)),
cast (Title as nvarchar(50)),
cast (Name  as nvarchar(50)),
cast (FamilyName as nvarchar(50)),
case when isDate(DateOfBirth) = 1 then convert(datetime, cast(DateOfBirth as varchar(10)), 103) else null end, -- Datetime col
cast(Age as Int),
cast (EmailID as nvarchar(50)),
cast (FName  as nvarchar(50)),
cast (FPhone  as nvarchar(50)),
cast (FOccupation  as nvarchar(50)),
cast (Occupation  as nvarchar(50)),
cast (Contact  as varchar(50)),
cast (Address  as varchar(50)),
cast (Country  as varchar(50)),
cast (State  as varchar(50)),
cast (City  as varchar(50)),
cast (Area  as varchar(50)),
cast (Zone  as varchar(50)),
cast (MCAName  as varchar(50)),
cast (ZPName  as nvarchar(50)),
convert(uniqueidentifier, cast(ZPID as varchar(10)), 103),
convert(uniqueidentifier, cast(MCAID as varchar(10)), 103),
cast (PinCode  as nvarchar(50)),
cast (WhomeDoKnwInChurch  as nvarchar(50)),
cast (DoYouWantVisit  as nvarchar(50)),
cast (IWouldLike  as nvarchar(50)),
cast (MyReligiousBackground  as nvarchar(50)),
cast (DoYouAttendAnotherChurch  as nvarchar(50)),
cast (NameOfChurch  as nvarchar(50)),
cast (Member  as nvarchar(50)),
cast (NonRegMember  as nvarchar(50)),
cast (Sanctified  as nvarchar(50)),
cast (CellGroup  as nvarchar(50)),
cast (Foundation  as nvarchar(50)),
cast (SpirtualCamp  as nvarchar(50)),
convert(datetime, cast(SpirtualCampDate as varchar(10)), 103), 
cast (FoundationWeek  as nvarchar(50)),
convert(datetime, cast(FoundationDate as varchar(10)), 103), 
cast (DEName  as nvarchar(50)),
convert(datetime, cast(DateOfTKTMemID as varchar(10)), 103), 
convert(datetime, cast(CommingChurchRegFrom as varchar(10)), 103), 
cast (PlaceOfBirth  as nvarchar(50)),
cast (OfficePhNo  as nvarchar(15)),
cast (PermanentAddress  as nvarchar(500)),
cast (NameOfOffice  as nvarchar(50)),
cast (PreOfficeEmployed  as nvarchar(50)),
cast (NameOfEducationInstitute  as nvarchar(50)),
cast (PrevEducationalInstitute  as nvarchar(50)),
cast (AreYouWaterBaptised  as nvarchar(5)),
cast (SpeekingToungses  as nvarchar(5)),
cast (HobbiesOrIntrestedArea  as nvarchar(500)),
cast (BankingWith  as nvarchar(50)),
cast (AnyAreaWouldYouLikeInvolvedINMinistry  as nvarchar(50)), 
cast (Qualification  as nvarchar(50)),
cast (MaritalStatus  as nvarchar(50)),
cast (NameOfSpouse  as nvarchar(50)),
convert(datetime, cast(DateOfMarriage as varchar(10)), 103),
cast (SpousePlaceOfWork  as nvarchar(50)),
cast (SpouseContactNum  as nvarchar(15)),
cast (SpouseEmailID  as nvarchar(50)),
convert(datetime, cast(SpouseDOB as varchar(10)), 103),
cast (NumOfChildreans  as nvarchar(50)),
cast (ChildName1  as nvarchar(50)),
convert(datetime, cast(ChildDOB1 as varchar(10)), 103),
cast (EducationOrOffice1  as nvarchar(50)),
cast (ChildName2  as nvarchar(50)),
convert(datetime, cast(ChildDOB2 as varchar(10)), 103),
cast (EducationOrOffice2  as nvarchar(50)),
cast (ChildName3  as nvarchar(50)),
convert(datetime, cast(ChildDOB3 as varchar(10)), 103),
cast (EducationOrOffice3  as nvarchar(50)),
cast (ChildName4  as nvarchar(50)),
convert(datetime, cast(ChildDOB4 as varchar(10)), 103),
cast (EducationOrOffice4  as nvarchar(50)),
cast (ChildName5  as nvarchar(50)),
convert(datetime, cast(ChildDOB5 as varchar(10)), 103),
cast (EducationOrOffice5  as nvarchar(50))

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\AllZonesData-ProcessedForUploadToDB.xls;Extended Properties=Excel 8.0')...[Database$]
SET IDENTITY_INSERT TKT_Member OFF

Open in new window

0
 

Author Comment

by:CPSRI
ID: 36599361
I changed my query like attached, but still i am getting the same error like -
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Please help me, i totally hanged over here with this issue.
SET IDENTITY_INSERT TKT_Member ON
INSERT INTO TKT_Member
(MemberID,TKTMemID,DateOfVisited,Gender,Title,Name,FamilyName,DateOfBirth,Age,EmailID,FName,FPhone,FOccupation,
Occupation,Contact,Address,Country,State,City,Area,Zone,MCAName,ZPName,ZPID,MCAID,PinCode,WhomeDoKnwInChurch,
DoYouWantVisit,IWouldLike,MyReligiousBackground,DoYouAttendAnotherChurch,NameOfChurch,Member,NonRegMember,Sanctified,CellGroup,Foundation,SpirtualCamp,SpirtualCampDate,FoundationWeek,FoundationDate,DEName,DateOfTKTMemID,
CommingChurchRegFrom,PlaceOfBirth,OfficePhNo,PermanentAddress,NameOfOffice,PreOfficeEmployed,NameOfEducationInstitute,
PrevEducationalInstitute,AreYouWaterBaptised,SpeekingToungses,HobbiesOrIntrestedArea,BankingWith,AnyAreaWouldYouLikeInvolvedINMinistry,
Qualification,MaritalStatus,NameOfSpouse,DateOfMarriage,SpousePlaceOfWork,SpouseContactNum,SpouseEmailID,SpouseDOB,
NumOfChildreans,ChildName1,ChildDOB1,EducationOrOffice1,ChildName2,ChildDOB2,EducationOrOffice2,ChildName3,ChildDOB3,EducationOrOffice3,
ChildName4,ChildDOB4,EducationOrOffice4,ChildName5,ChildDOB5,EducationOrOffice5)
SELECT cast(MemberID as int),
cast(TKTMemID as Int), -- integer col
case when isDate(DateOfVisited)=1 then
convert(datetime, cast(DateOfVisited as varchar(10)), 103) else null end, -- Datetime col
CAST(Gender as nvarchar(10)),
cast (Title as nvarchar(50)),
cast (Name as nvarchar(50)),
cast (FamilyName as nvarchar(50)),
case when isDate(DateOfBirth)=1 then
convert(datetime, cast(DateOfBirth as varchar(10)), 103) else null end, -- Datetime col
cast(Age as Int),
cast (EmailID as nvarchar(50)),
cast (FName as nvarchar(50)),
cast (FPhone as nvarchar(50)),
cast (FOccupation as nvarchar(50)),
cast (Occupation as nvarchar(50)),
cast (Contact as varchar(50)),
cast (Address as varchar(500)),
cast (Country as varchar(50)),
cast (State as varchar(50)),
cast (City as varchar(50)),
cast (Area as varchar(50)),
cast (Zone as varchar(50)),
cast (MCAName as varchar(50)),
cast (ZPName as nvarchar(50)),
convert(uniqueidentifier, cast(ZPID as varchar(10)), 103),
convert(uniqueidentifier, cast(MCAID as varchar(10)), 103),
cast (PinCode as nvarchar(50)),
cast (WhomeDoKnwInChurch as nvarchar(50)),
cast (DoYouWantVisit as nvarchar(50)),
cast (IWouldLike as nvarchar(50)),
cast (MyReligiousBackground as nvarchar(50)),
cast (DoYouAttendAnotherChurch as nvarchar(50)),
cast (NameOfChurch as nvarchar(50)),
cast (Member as nvarchar(50)),
cast (NonRegMember as nvarchar(50)),
cast (Sanctified as nvarchar(50)),
cast (CellGroup as nvarchar(50)),
cast (Foundation as nvarchar(50)),
cast (SpirtualCamp as nvarchar(50)),
case when isDate(SpirtualCampDate)=1 then
convert(datetime, cast(SpirtualCampDate as varchar(10)), 103) else null end,
cast (FoundationWeek as nvarchar(50)),
case when isDate(FoundationDate)=1 then
convert(datetime, cast(FoundationDate as varchar(10)), 103) else null end,
cast (DEName as nvarchar(50)),
case when isDate(DateOfTKTMemID)=1 then
convert(datetime, cast(DateOfTKTMemID as varchar(10)), 103) else null end,
case when isDate(CommingChurchRegFrom)=1 then
convert(datetime, cast(CommingChurchRegFrom as varchar(10)), 103) else null end,
cast (PlaceOfBirth as nvarchar(50)),
cast (OfficePhNo as nvarchar(15)),
cast (PermanentAddress as nvarchar(500)),
cast (NameOfOffice as nvarchar(50)),
cast (PreOfficeEmployed as nvarchar(50)),
cast (NameOfEducationInstitute as nvarchar(50)),
cast (PrevEducationalInstitute as nvarchar(50)),
cast (AreYouWaterBaptised as nvarchar(5)),
cast (SpeekingToungses as nvarchar(5)),
cast (HobbiesOrIntrestedArea as nvarchar(500)),
cast (BankingWith as nvarchar(50)),
cast (AnyAreaWouldYouLikeInvolvedINMinistry as nvarchar(50)),
cast (Qualification as nvarchar(50)),
cast (MaritalStatus as nvarchar(50)),
cast (NameOfSpouse as nvarchar(50)),
case when isDate(DateOfMarriage)=1 then
convert(datetime, cast(DateOfMarriage as varchar(10)), 103) else null end ,
cast (SpousePlaceOfWork as nvarchar(50)),
cast (SpouseContactNum as nvarchar(15)),
cast (SpouseEmailID as nvarchar(50)),
case when isDate(SpouseDOB)=1 then
convert(datetime, cast(SpouseDOB as varchar(10)), 103) else null end,
cast (NumOfChildreans as nvarchar(50)),
cast (ChildName1 as nvarchar(50)),
case when isDate(ChildDOB1)=1 then
convert(datetime, cast(ChildDOB1 as varchar(10)), 103) else null end,
cast (EducationOrOffice1 as nvarchar(50)),
cast (ChildName2 as nvarchar(50)),
case when isDate(ChildDOB2)=1 then
convert(datetime, cast(ChildDOB2 as varchar(10)), 103) else null end,
cast (EducationOrOffice2 as nvarchar(50)),
cast (ChildName3 as nvarchar(50)),
case when isDate(ChildDOB3)=1 then
convert(datetime, cast(ChildDOB3 as varchar(10)), 103) else null end,
cast (EducationOrOffice3 as nvarchar(50)),
cast (ChildName4 as nvarchar(50)),
case when isDate(ChildDOB4)=1 then
convert(datetime, cast(ChildDOB4 as varchar(10)), 103) else null end,
cast (EducationOrOffice4 as nvarchar(50)),
cast (ChildName5 as nvarchar(50)),
case when isDate(ChildDOB5)=1 then
convert(datetime, cast(ChildDOB5 as varchar(10)), 103) else null end,
cast (EducationOrOffice5 as nvarchar(50))

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\AllZonesData-ProcessedForUploadToDB.xls;Extended Properties=Excel 8.0')...[Database$]
SET IDENTITY_INSERT TKT_Member OFF

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36599476
hi...

pls post the table structure....
0
 

Author Comment

by:CPSRI
ID: 36599650
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36599737
no no...
table structure of TKT_Member.

I mean columns with datatypes..
0
 

Author Comment

by:CPSRI
ID: 36600043
ok ok .. here it is attached.
New-Text-Document--2-.txt
0
 

Author Comment

by:CPSRI
ID: 36600887
Bhavesh, attached the create table script so that you can know all the details, please give a reply at earliest, i need to finish it ..please
CREATE TABLE [dbo].[TKT_Member](
	[MemberID] [int] IDENTITY(1,1) NOT NULL,
	[TKTMemID] [int] NULL,
	[DateOfVisited] [datetime] NULL,
	[Gender] [nvarchar](10) NULL,
	[Title] [nvarchar](50) NULL,
	[Name] [nvarchar](50) NULL,
	[FamilyName] [nvarchar](50) NULL,
	[DateOfBirth] [datetime] NULL,
	[Age] [int] NULL,
	[EmailID] [nvarchar](50) NULL,
	[FName] [nvarchar](50) NULL,
	[FPhone] [nvarchar](15) NULL,
	[FEmailID] [nvarchar](50) NULL,
	[FOccupation] [nvarchar](50) NULL,
	[Occupation] [nvarchar](50) NULL,
	[Contact] [varchar](50) NULL,
	[Address] [varchar](50) NULL,
	[Country] [varchar](50) NULL,
	[State] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[Area] [varchar](50) NULL,
	[Zone] [varchar](50) NULL,
	[MCAName] [varchar](50) NULL,
	[ZPName] [varchar](50) NULL,
	[ZPID] [uniqueidentifier] NULL,
	[MCAID] [uniqueidentifier] NULL,
	[PinCode] [nvarchar](50) NULL,
	[WhomeDoKnwInChurch] [nvarchar](50) NULL,
	[DoYouWantVisit] [nvarchar](50) NULL,
	[IWouldLike] [nvarchar](50) NULL,
	[MyReligiousBackground] [nvarchar](50) NULL,
	[DoYouAttendAnotherChurch] [nvarchar](50) NULL,
	[NameOfChurch] [nvarchar](50) NULL,
	[Member] [nvarchar](50) NULL,
	[NonRegMember] [nvarchar](50) NULL,
	[Sanctified] [nvarchar](50) NULL,
	[CellGroup] [nvarchar](50) NULL,
	[Foundation] [nvarchar](50) NULL,
	[SpirtualCamp] [nvarchar](50) NULL,
	[SpirtualCampDate] [datetime] NULL,
	[FoundationWeek] [nvarchar](50) NULL,
	[FoundationDate] [datetime] NULL,
	[DEName] [nvarchar](50) NULL,
	[DateOfTKTMemID] [datetime] NULL,
	[CommingChurchRegFrom] [datetime] NULL,
	[PlaceOfBirth] [nvarchar](50) NULL,
	[OfficePhNo] [nvarchar](15) NULL,
	[PermanentAddress] [nvarchar](500) NULL,
	[NameOfOffice] [nvarchar](50) NULL,
	[PreOfficeEmployed] [nvarchar](50) NULL,
	[NameOfEducationInstitute] [nvarchar](50) NULL,
	[PrevEducationalInstitute] [nvarchar](50) NULL,
	[AreYouWaterBaptised] [nvarchar](5) NULL,
	[SpeekingToungses] [nvarchar](5) NULL,
	[HobbiesOrIntrestedArea] [nvarchar](500) NULL,
	[BankingWith] [nvarchar](50) NULL,
	[AnyAreaWouldYouLikeInvolvedINMinistry] [nvarchar](50) NULL,
	[Qualification] [nvarchar](50) NULL,
	[MaritalStatus] [nvarchar](50) NULL,
	[NameOfSpouse] [nvarchar](50) NULL,
	[DateOfMarriage] [datetime] NULL,
	[SpousePlaceOfWork] [nvarchar](50) NULL,
	[SpouseContactNum] [nvarchar](15) NULL,
	[SpouseEmailID] [nvarchar](50) NULL,
	[SpouseDOB] [datetime] NULL,
	[NumOfChildreans] [nvarchar](50) NULL,
	[ChildName1] [nvarchar](50) NULL,
	[ChildDOB1] [datetime] NULL,
	[EducationOrOffice1] [nvarchar](50) NULL,
	[ChildName2] [nvarchar](50) NULL,
	[ChildDOB2] [datetime] NULL,
	[EducationOrOffice2] [nvarchar](50) NULL,
	[ChildName3] [nvarchar](50) NULL,
	[ChildDOB3] [datetime] NULL,
	[EducationOrOffice3] [nvarchar](50) NULL,
	[ChildName4] [nvarchar](50) NULL,
	[ChildDOB4] [datetime] NULL,
	[EducationOrOffice4] [nvarchar](50) NULL,
	[ChildName5] [nvarchar](50) NULL,
	[ChildDOB5] [datetime] NULL,
	[EducationOrOffice5] [nvarchar](50) NULL,
 CONSTRAINT [PK_TKT_Member] PRIMARY KEY CLUSTERED 
(
	[MemberID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Open in new window

0
 

Author Comment

by:CPSRI
ID: 36600999
Bhavesh, i observed one prob in my query i missed one column in the query, let me try with the correction in the columns that might solve my prob, if not ill get back to you..
0
 

Author Comment

by:CPSRI
ID: 36601300
i did the correction but query has terminated with the same issue, did you get any solution?
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36707717
SELECT Field1, Field2, Cast(Filed3, datatype) As Filed3  INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
0
 

Author Comment

by:CPSRI
ID: 36707815
thank you patelalpesh, as I already with the query that Bhavesh gave me can you help with that query please? anyway ill try with your query also but i spent whole day on that, thats why i am asking thats it, if you can thats ok..if not ill get back after your query execution
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36707822
Hi,

can you share your complete excel sheet....?
0
 

Author Comment

by:CPSRI
ID: 36707837
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 36707899
Hi,

i changed the query.

now run it again, you should be get error something like.


Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.


for that, you might need to increase the size of columns.

Change the path of excel sheet & its name.
INSERT INTO TKT_Member
(MemberID,TKTMemID,DateOfVisited,Gender,Title,Name,FamilyName,DateOfBirth,Age,EmailID,FName,FPhone,FOccupation,
Occupation,Contact,Address,Country,State,City,Area,Zone,MCAName,ZPName,ZPID,MCAID,PinCode,WhomeDoKnwInChurch,
DoYouWantVisit,IWouldLike,MyReligiousBackground,DoYouAttendAnotherChurch,NameOfChurch,Member,NonRegMember,Sanctified,CellGroup,Foundation,SpirtualCamp,SpirtualCampDate,FoundationWeek,FoundationDate,DEName,DateOfTKTMemID,
CommingChurchRegFrom,PlaceOfBirth,OfficePhNo,PermanentAddress,NameOfOffice,PreOfficeEmployed,NameOfEducationInstitute,
PrevEducationalInstitute,AreYouWaterBaptised,SpeekingToungses,HobbiesOrIntrestedArea,BankingWith,AnyAreaWouldYouLikeInvolvedINMinistry,
Qualification,MaritalStatus,NameOfSpouse,DateOfMarriage,SpousePlaceOfWork,SpouseContactNum,SpouseEmailID,SpouseDOB,
NumOfChildreans,ChildName1,ChildDOB1,EducationOrOffice1,ChildName2,ChildDOB2,EducationOrOffice2,ChildName3,ChildDOB3,EducationOrOffice3,
ChildName4,ChildDOB4,EducationOrOffice4,ChildName5,ChildDOB5,EducationOrOffice5)
SELECT cast(MemberID as int),
cast(TKTMemID as Int), -- integer col
case when isDate(DateOfVisited)=1 then
convert(datetime, DateOfVisited, 103) else null end, -- Datetime col
CAST(Gender as nvarchar(10)),
cast (Title as nvarchar(50)),
cast (Name as nvarchar(50)),
cast (FamilyName as nvarchar(50)),
case when isDate(DateOfBirth)=1 then
convert(datetime, DateOfBirth, 103) else null end, -- Datetime col
cast(Age as Int),
cast (EmailID as nvarchar(50)),
cast (FName as nvarchar(50)),
cast (FPhone as nvarchar(50)),
cast (FOccupation as nvarchar(50)),
cast (Occupation as nvarchar(50)),
cast (Contact as varchar(50)),
cast (Address as varchar(500)),
cast (Country as varchar(50)),
cast (State as varchar(50)),
cast (City as varchar(50)),
cast (Area as varchar(50)),
cast (Zone as varchar(50)),
cast (MCAName as varchar(50)),
cast (ZPName as nvarchar(50)),
convert(uniqueidentifier, cast(ZPID as varchar(10)), 103),
convert(uniqueidentifier, cast(MCAID as varchar(10)), 103),
cast (PinCode as nvarchar(50)),
cast (WhomeDoKnwInChurch as nvarchar(50)),
cast (DoYouWantVisit as nvarchar(50)),
cast (IWouldLike as nvarchar(50)),
cast (MyReligiousBackground as nvarchar(50)),
cast (DoYouAttendAnotherChurch as nvarchar(50)),
cast (NameOfChurch as nvarchar(50)),
cast (Member as nvarchar(50)),
cast (NonRegMember as nvarchar(50)),
cast (Sanctified as nvarchar(50)),
cast (CellGroup as nvarchar(50)),
cast (Foundation as nvarchar(50)),
cast (SpirtualCamp as nvarchar(50)),
case when isDate(SpirtualCampDate)=1 then
convert(datetime, SpirtualCampDate, 103) else null end,
cast (FoundationWeek as nvarchar(50)),
case when isDate(FoundationDate)=1 then
convert(datetime, FoundationDate, 103) else null end,
cast (DEName as nvarchar(50)),
case when isDate(DateOfTKTMemID)=1 then
convert(datetime, DateOfTKTMemID , 103) else null end,
case when isDate(CommingChurchRegFrom)=1 then
convert(datetime, CommingChurchRegFrom , 103) else null end,
cast (PlaceOfBirth as nvarchar(50)),
cast (OfficePhNo as nvarchar(15)),
cast (PermanentAddress as nvarchar(500)),
cast (NameOfOffice as nvarchar(50)),
cast (PreOfficeEmployed as nvarchar(50)),
cast (NameOfEducationInstitute as nvarchar(50)),
cast (PrevEducationalInstitute as nvarchar(50)),
cast (AreYouWaterBaptised as nvarchar(5)),
cast (SpeekingToungses as nvarchar(5)),
cast (HobbiesOrIntrestedArea as nvarchar(500)),
cast (BankingWith as nvarchar(50)),
cast (AnyAreaWouldYouLikeInvolvedINMinistry as nvarchar(50)),
cast (Qualification as nvarchar(50)),
cast (MaritalStatus as nvarchar(50)),
cast (NameOfSpouse as nvarchar(50)),
case when isDate(DateOfMarriage)=1 then
convert(datetime, DateOfMarriage , 103) else null end ,
cast (SpousePlaceOfWork as nvarchar(50)),
cast (SpouseContactNum as nvarchar(15)),
cast (SpouseEmailID as nvarchar(50)),
case when isDate(SpouseDOB)=1 then
convert(datetime, SpouseDOB , 103) else null end,
cast (NumOfChildreans as nvarchar(50)),
cast (ChildName1 as nvarchar(50)),
case when isDate(ChildDOB1)=1 then
convert(datetime, ChildDOB1 , 103) else null end,
cast (EducationOrOffice1 as nvarchar(50)),
cast (ChildName2 as nvarchar(50)),
case when isDate(ChildDOB2)=1 then
convert(datetime, ChildDOB2 , 103) else null end,
cast (EducationOrOffice2 as nvarchar(50)),
cast (ChildName3 as nvarchar(50)),
case when isDate(ChildDOB3)=1 then
convert(datetime, ChildDOB3 , 103) else null end,
cast (EducationOrOffice3 as nvarchar(50)),
cast (ChildName4 as nvarchar(50)),
case when isDate(ChildDOB4)=1 then
convert(datetime, ChildDOB4 , 103) else null end,
cast (EducationOrOffice4 as nvarchar(50)),
cast (ChildName5 as nvarchar(50)),
case when isDate(ChildDOB5)=1 then
convert(datetime, ChildDOB5 , 103) else null end,
cast (EducationOrOffice5 as nvarchar(50))

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test1.xls;Extended Properties=Excel 8.0')...[Database$]

Open in new window

0
 

Author Comment

by:CPSRI
ID: 36708011
now i got this error

Cannot insert the value NULL into column 'MemberID', table 'Northwind.dbo.TKT_Member'; column does not allow nulls. INSERT fails.

but i dont have any null values in my excel for the field MemberID
0
 

Author Comment

by:CPSRI
ID: 36708106
Yes, now i got it, thank you so much for the help, thank you bhavesh. Let me finish it all the data and ill close this question. Once again thank you so much.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36708318

Happy to help you...  :-)
0
 

Author Closing Comment

by:CPSRI
ID: 36851726
thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question