transfer data from Excel to SQL Server

Question

Hi all,

I have an Excel Spreadsheet that contains all my data that I need to put into an SQL Server database. I am fairly new o ASP.NET and have never had to export from Excel to SQL Server before.

My Excel spreadsheets looks like this

Trade Heading -> ArtID -> BusinessName -> AdStyleCode -> Address -> Suburb

In SQL Server I have created a table named "Listings" which is in this format

intListingID -> intCategoryID -> BusinessName - ArtID -> intAdCode ->Address -> Suburb

What would be the best way to export the data from Excel and then import it into SQLServer 2005.

Thanks...

Answer 1

You can do this easily using SSIS, you can refer to these two links for full details.

1. Link 1
2. Link 2

[EDIT]

If you have Express then you can try the below commands to setup a linked server and get the data

EXEC sp_addlinkedserver ExcelData,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:\MyData.xls', NULL, 'Excel 5.0;'
GO

Then you can select the data into your tables

INSERT INTO Listings ...
SELECT column1 AS intListingID, <put all columns here> FROM ExcelData...Data
GO

For other options check this link

Answer 2

Microsoft have an article on this.