将数据从Excel传输到SQL Server [英] transfer data from Excel to SQL Server

问题描述

我有一个Excel电子表格，其中包含我需要放入SQL Server数据库的所有数据。我相当新的ASP.NET，从来没有从Excel导出到SQL Server之前。

我的Excel电子表格看起来像这样

交易标题 - > ArtID - > BusinessName - > AdStyleCode - >地址 - >郊区

在SQL Server中，我创建了一个名为Listings 这是格式

intListingID - > intCategoryID - > BusinessName - ArtID - > intAdCode - >地址 - >郊区

从Excel导出数据，然后将其导入到SQLServer 2005中是最好的方法。

谢谢...

解决方案

您可以轻松地使用SSIS，您可以参考这两个链接了解详细信息。

1. 链接1


2. 链接2

如果您有Express，则可以尝试以下命令设置链接服务器并获取数据

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

然后你可以选择数据到你的表

  INSERT INTO Listings ... 
 SELECT column1 AS intListingID，< put all columns here> FROM ExcelData ...数据
 GO 
  

对于其他选项，请检查此链接

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...

解决方案

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

这篇关于将数据从Excel传输到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！