If you have well formatted data in an excel sheet then you definitely can import those data to your SQL Server table. By saying well formatted data, I mean- data should have arranged in columnar structure like a SQL Server table, which has data arranged per columns. Each of the columns is should have data of similar data type. Again, you have to take certain pre-cautions while creating table for it like-
1. While creating a field in the table find out the maximum value or, length of the data present in the corresponding column in the Excel sheet.
2.Trim the blank spaces from both the ends of the data(Although in SQL Server if a field is declared as NVARCHAR,then it automatically truncates the leading blank spaces). For this you can use LTRIM() and RTRIM() functions of SQL Server on those fields, which are supposed to have blank spaces.
3.Define the field to allow NULL, if you are expecting the corresponding column to have NULL values.
4.If you don’t want any blank spaces in your data then you can use REPLACE(columnName,’ ‘,”)
Now once you created the table, then you go for import data and your SQL script will be like-
INSERT INTO TableName SELECT F1,F2,,F3 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=NO;DATABASE=D:\Data.xls', 'Select * from [sheet1$]')
You can use WHERE, ORDER BY, GROUP BY etc with the above statement as per your requirement.