xxxxxxxxxx
# For file excel 2007 version (*.xlsx)
INSERT INTO MyTable
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\test.xlsx', [Customer$])
# For file excel 97-2003 version (*.xls)
INSERT INTO MyTable
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\test.xls', [Customer$])
xxxxxxxxxx
declare @SQL nvarchar(max) = '
CREATE TABLE #TempTable
( [Field1] nvarchar(max) NULL,
[Field2] nvarchar(max) NULL,
[Field3] nvarchar(max) NULL );
BULK INSERT #TempTable FROM ''<FullPath>\FileName.csv'' WITH --if the path is in the network - need to write the Full-path of the drive
(
KEEPIDENTITY,
FIELDTERMINATOR = '','',
MAXERRORS = 10000,
KEEPNULLS,
ROWTERMINATOR=''\n'',
FIRSTROW = 2,
CODEPAGE = ''1255''
);
select * from #TempTable
Insert into TableNameInDB(Field1,Field2,Field3)
select * from #TempTable
'
EXEC sp_executesql @SQL
xxxxxxxxxx
From your SQL Server Management Studio, you open Object Explorer, go to your database where you want to load the data into, right click, then pick Tasks > Import Data.
This opens the Import Data Wizard, which typically works pretty well for importing from Excel.
xxxxxxxxxx
Step 1 – Create a Project.
Step 2 – Create a Connection to your SQL Server Database.
Step 3 – Create a Table.
Step 4 – Create an Excel Connection.
Step 5 – Create a Data Flow Task.
Step 6 – Creating the Excel Source.
Step 7 – Removing Rubbish Data.
Step 8 – Piping the 'OK Data' into a SQL Server Table.