inforakesha

A fine WordPress.com site

importing excel data to Table in SQLserver

Leave a comment

STEP:1- First install AccessDatabaseEngine.exe .

STEP:2-

sp_configure ‘show advanced options’, 1;

GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO

STEP3:(some time)
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 0
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 0
GO

STEP4:-

SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;Database=C:\Users\rakesh.kumar\Desktop\Clubbed FBL5N\Sheet1.xlsx’,’select * from [FBL5N$]’)

where FBL5N-is the sheet name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s