first of all, need enable the features by running following query:
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
command "reconfigure" is use to install the configuration settings u changed.
After that, run the following query to import data from excel file:
SELECT *
INTO databasename.schema.tablename
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=filepath',
'SELECT * FROM [sheet1$]')
This query will automatically help to create the table with given table name.
