• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

export data from Excel to SQL Server

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
CPSRI
Asked:
CPSRI
  • 18
  • 14
  • +1
1 Solution
 
Lee SavidgeCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
CPSRIAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
CPSRIAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Before inserting

run
SET IDENTITY_INSERT TableName ON

after inserting


SET IDENTITY_INSERT TableName OFF

-Bhavesh
0
 
CPSRIAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
can you share the excel sheet?

it seems problem in date.
0
 
CPSRIAuthor Commented:
here i attached the Excel with one row..but in original file approx i have 2000 rows.
AllZonesData-ProcessedForUploadT.xls
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
CPSRIAuthor Commented:
can we change our query to insert null value if such a kind of data exists?
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
CPSRIAuthor Commented:
I tried it but again i am getting one error,
can you explain me with my query please?
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

i will explain, can you re-post your final query
0
 
CPSRIAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
CPSRIAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
hi...

pls post the table structure....
0
 
CPSRIAuthor Commented:
0
 
Bhavesh ShahLead AnalysistCommented:
no no...
table structure of TKT_Member.

I mean columns with datatypes..
0
 
CPSRIAuthor Commented:
ok ok .. here it is attached.
New-Text-Document--2-.txt
0
 
CPSRIAuthor Commented:
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
 
CPSRIAuthor Commented:
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
 
CPSRIAuthor Commented:
i did the correction but query has terminated with the same issue, did you get any solution?
0
 
Alpesh PatelAssistant ConsultantCommented:
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
 
CPSRIAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
Hi,

can you share your complete excel sheet....?
0
 
CPSRIAuthor Commented:
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
CPSRIAuthor Commented:
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
 
CPSRIAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

Happy to help you...  :-)
0
 
CPSRIAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 18
  • 14
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now