How to XL sheet data Load into SQLServer Table

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

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

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

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

Thanks Pandian for sharing this...

Thanks and Regards
Madhu

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