Solved

export data from Excel to SQL Server

Posted on 2011-09-26
34
227 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
  • 18
  • 14
  • +1
34 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi,

Before inserting

run
SET IDENTITY_INSERT TableName ON

after inserting


SET IDENTITY_INSERT TableName OFF

-Bhavesh
0
 

Author Comment

by:CPSRI
Comment Utility
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
Comment Utility
can you share the excel sheet?

it seems problem in date.
0
 

Author Comment

by:CPSRI
Comment Utility
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
Comment Utility
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
Comment Utility
can we change our query to insert null value if such a kind of data exists?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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
Comment Utility
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
Comment Utility
hi,

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

Author Comment

by:CPSRI
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:CPSRI
Comment Utility
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
Comment Utility
hi...

pls post the table structure....
0
 

Author Comment

by:CPSRI
Comment Utility
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
no no...
table structure of TKT_Member.

I mean columns with datatypes..
0
 

Author Comment

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

Author Comment

by:CPSRI
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi,

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

Author Comment

by:CPSRI
Comment Utility
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

Happy to help you...  :-)
0
 

Author Closing Comment

by:CPSRI
Comment Utility
thank you
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now