Best Way to Import Excel Data into SQL Server Table

[JohnBates]

Hi experts,

I have an Excel file that I want to import into a SQL Server 2000 table.

I know a DTS package is one option. Is it realistic to think a DTS package built by the wizard will be able to do this? Or should I go ahead and learn T-SQL and do it with my own stored procedure ?

I'm hoping to get some advice from some of you that have done this.

Thanks, John

 

[dan1967]

One thing that worked for me in the past is to create a linked server to the Excel file:

exec sp_addlinkedserver
	@server= 'ExcelSource',
	@provider= 'Microsoft.Jet.OLEDB.4.0',
	@srvproduct= 'Jet 4.0',
	@datasrc= 'c:\FileName.xls'
go
{enter your input code here}
go
exec sp_dropserver @server= 'ExcelSource'
go

You will need admin rights to the server you are performing this on.


Dan.

 

[dan1967]

Here is another way I have done it using the Bulk Insert command:

BULK INSERT TableName
FROM 'c:\FileName.txt'
	WITH	(
	FIELDTERMINATOR = '\t',
	ROWTERMINATOR = '\n'
		)
GO

I did this using a tab delimited text file. The rowterminator value '\n' is a newline feed.

Dan.