How to XL sheet data Load into SQLServer Table

Posted by Jayakumarv under Sql Server on 2/18/2011 | Points: 10 | Views : 1610 | Status : [Member] | Replies : 5
i have 1000 xl sheet,each sheet have 35 columns,
i need to load this data's into sql server table,please give me any idea's.




Responses

Posted by: Karthikanbarasan on: 2/18/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Hi,

check this link... same way you can create a loop and do your requirement

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

Thanks
Karthik
www.f5Debug.net

Jayakumarv, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 2/18/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

You can use the following way to load data to SQL Server from Excel sheet.
CREATE TABLE #Table1

(
ID INT,
Col1 VARCHAR(50),
Col2 VARCHAR(50)
)

INSERT #Table1
SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OleDB.4.0',
'Data Source=c:\Excel Files\Book1.xls; extended Properties=Excel 8.0')...sheet1$

SELECT * FROM #Table1

DROP TABLE #Table1
But, If you want to load data from multiple sheets then, you can use dynamic script like Loop through each sheet and load the data...

Note:
1. You don't need any Linked Server for this operation
2. when doing this operation, Excel file should be CLOSED.

(OR)

Use can use...
Right Click on Database --> Tasks --> Import Data -->

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jayakumarv, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 2/18/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Hi pandian,

Nice one... have a doubt here

SELECT * FROM OPENDATASOURCE(

'Microsoft.Jet.OleDB.4.0',
'Data Source=c:\Excel Files\Book1.xls; extended Properties=Excel 8.0')...sheet1$

SELECT * FROM #Table1



OPENDATASOURCE -- is this a table name or what exactly this mean?

Thanks
Karthik
www.f5Debug.net

Jayakumarv, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 2/18/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Karthik

OPENDATASOURCE is a SQL Server system function, Used to access various resources.

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jayakumarv, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Madhu.b.rokkam on: 2/18/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Thanks Pandian for sharing this...

Thanks and Regards
Madhu

Jayakumarv, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response