将Excel电子表格列导入SQL Server数据库

I have an Excel spreadsheet that I want to import select columns into my SQL Server 2008 database table. The wizard didn't offer that option.

我有一个Excel电子表格，我想将select列导入我的SQL Server 2008数据库表。向导没有提供这个选项。

Do any easy code options exist?

是否存在任何简单的代码选项?

15 个解决方案

#1

Once connected to Sql Server 2005 Database, From Object Explorer Window, right click on the database which you want to import table into. Select Tasks -> Import Data. This is a simple tool and allows you to 'map' the incoming data into appropriate table. You can save the scripts to run again when needed.

一旦连接到Sql Server 2005数据库，从Object Explorer窗口，右键单击要导入表的数据库。选择Tasks ->导入数据。这是一个简单的工具，允许您将传入的数据映射到适当的表中。您可以保存脚本，以便在需要时再次运行。

#2

Microsoft suggest several methods:

微软建议几个方法:

SQL Server Data Transformation Services (DTS)
SQL Server数据转换服务(DTS)
Microsoft SQL Server 2005 Integration Services (SSIS)
微软SQL Server 2005集成服务(SSIS)
SQL Server linked servers
SQL Server连接服务器
SQL Server distributed queries
SQL Server分布式查询
ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
ActiveX数据对象(ADO)和SQL服务器的Microsoft OLE DB提供程序
ADO and the Microsoft OLE DB Provider for Jet 4.0
ADO和Microsoft OLE DB提供的Jet 4.0

If the wizard (DTS) isn't working (and I think it should) you could try something like this http://www.devasp.net/net/articles/display/771.html which basically suggests doing something like

如果向导(DTS)不起作用(我认为应该起作用)，您可以尝试如下内容:http://www.devasp.net/articles/display/771.html，它基本上建议您执行以下操作

INSERT INTO [tblTemp] ([Column1], [Column2], [Column3], [Column4])

SELECT A.[Column1], A.[Column2], A.[Column3], A.[Column4]
FROM OPENROWSET 
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Excel.xls;HDR=YES', 'select * from [Sheet1$]') AS A;

#3

This may sound like the long way around, but you may want to look at using Excel to generate INSERT SQL code that you can past into Query Analyzer to create your table.

这听起来像是一段很长的路，但是您可能想看看如何使用Excel来生成插入SQL代码，您可以通过查询分析器来创建您的表。

Works well if you cant use the wizards because the excel file isn't on the server

如果由于excel文件不在服务器上，所以无法使用向导，则可以正常工作

#4

You could use OPENROWSET, something like:

你可以使用OPENROWSET，比如:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
  'Excel 8.0;IMEX=1;HDR=NO;DATABASE=C:\FILE.xls', 'Select * from [Sheet1$]'

Just make sure the path is a path on the server, not your local machine.

只要确保路径是服务器上的路径，而不是本地机器上的路径。

#5

go
sp_configure 'show advanced options',1  
reconfigure with override  
go  
sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure with override  
go
SELECT * into temptable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Documents and Settings\abhisharma\Desktop\exl\ImportExcel2SQLServer\ImportExcel2SQLServer\example.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

select * from temptable

#6

Another option is to use VBA in Excel, and write a macro to parse the spreadsheet data and write it into SQL.

另一个选项是在Excel中使用VBA，并编写一个宏来解析电子表格数据并将其写入SQL。

One example is here: http://www.ozgrid.com/forum/showthread.php?t=26621&page=1

这里有一个例子:http://www.ozgrid.com/forum/showthread.php?

Sub InsertARecord() 
Dim cnt As ADODB.Connection 
Dim rst As ADODB.Recordset 
Dim stCon As String, stSQL As String 
Set cnt = New ADODB.Connection 
Set rst = New ADODB.Recordset 

stCon = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=JOEY" 
cnt.ConnectionString = stCon 
stSQL = "INSERT INTO MyTable (Price)" 
stSQL = stSQL & "VALUES (500)" 

cnt.Open 
rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText 

If CBool(rst.State And adStateOpen) = True Then rst.Close 
Set rst = Nothing 
If CBool(cnt.State And adStateOpen) = True Then cnt.Close 
Set cnt = Nothing 

End Sub

#7

By 'the wiz' I'm assuming you're talking about the 'SQL Server Import and Export Wizard'. (I'm also pretty new so I don't understand most questions, much less most answers, but I think I get this one). If so couldn't you take the spreadsheet, or a copy of it, delete the columns you don't want imported and then use the wizard?

通过“wiz”，我假设您正在讨论的是“SQL服务器导入和导出向导”。(我也是个新手，所以我不懂大多数问题，更不用说大多数答案了，但我想我懂了。)如果是这样，难道你就不能取电子表格或它的副本，删除不想导入的列，然后使用向导吗?

I've always found the ability to do what I need with it and I'm only on SQL Server 2000 (not sure how other versions differ).

我总是能够使用它做我需要做的事情，而且我只使用SQL Server 2000(不确定其他版本有什么不同)。

Edit: In fact I'm looking at it now and I seem to be able to choose which columns I want to map to which rows in an existing table. On the 'Select Source Tables and Views' screen I check the datasheet I'm using, select the 'Destination' then click the 'Edit...' button. From there you can choose the Excel column and the table column to map it to.

编辑:事实上，我现在正在看它，而且我似乎能够选择我想要映射到现有表中的哪些列。在“选择源表和视图”屏幕上，我检查我正在使用的数据表，选择“目的地”然后点击“编辑…””按钮。从那里，您可以选择要映射到的Excel列和表列。

#8

If you would like a visual tool with Desktop interface including validation .. you may well like this Excel tool. You can also use the tool to create multi user data-edit tasks, or even paste data to SQL server from any source..

如果你想要一个视觉工具与桌面界面包括验证。您可能很喜欢这个Excel工具。您还可以使用该工具创建多用户数据编辑任务，甚至可以从任何来源将数据粘贴到SQL服务器。

How to Validate and Import Excel spreadsheet to SQL Server database:

如何验证和导入Excel电子表格到SQL Server数据库:

http://leansoftware.net/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx

#9

Microsoft Access is another option. You could have a Access database locally on your machine that you import the excel spreadsheets into (wizards available) and link to the the SQL Server database tables via ODBC.

微软Access是另一个选择。您可以在您的机器上本地有一个访问数据库，您可以将excel电子表格导入(向导可用)，并通过ODBC连接到SQL Server数据库表。

You could then design a query in access that appends data from the Excel spreadsheet to the SQL Server Tables.

然后，您可以设计一个查询访问，该查询将Excel电子表格中的数据附加到SQL Server表中。

#10

The best tool i've ever used is http://tools.perceptus.ca/text-wiz.php?ops=7 Did you try it?

我所使用过的最好的工具是http://tools.sentus.ca/text -wiz.php?你试过了吗?

#11

I think it will help you

我想这对你有帮助

Visit http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/78f8e980-0bb6-4a48-b1b1-5f65bca1d511

访问http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/78f8e980-0bb6-4a48-b1b1-5f65bca1d511

#12

The import wizard does offer that option. You can either use the option to write your own query for the data to import, or you can use the copy data option and use the "Edit Mappings" button to ignore columns you do not want to import.

导入向导确实提供了这个选项。您可以使用该选项来编写自己的查询来导入数据，或者您可以使用copy data选项并使用“Edit Mappings”按钮来忽略您不想导入的列。

#13

Excel + SQLCMD + Perl = exceltomssqlinsert

Excel + SQLCMD + Perl = exceltomssqlinsert。

and you can use your Excel as frond-end to MSSQL db ... Note the truncate table at the beginning of each generated sql insert file ...

你可以使用你的Excel作为MSSQL db的前端…注意在每个生成的sql插入文件开头的truncate表……

#14

I have used DTS (now known as SQL server Import and Export Wizard). I used the this tutorial which worked great for me even in Sql 2008 and excel 2010 (14.0)

我使用了DTS(现在称为SQL server导入和导出向导)。我使用了本教程，它在Sql 2008和excel 2010中都很有用(14.0)

I hope this helps

我希望这有助于

-D

- d

#15

First of all, try the 32 Bit Version of the Import Wizard. This shows a lot more supported import formats.

首先，尝试导入向导的32位版本。这显示了更多支持的导入格式。

Background: All depends on your Office (Runtimes Engines) installation.

背景:这一切都取决于您的办公室(运行时引擎)的安装。

If you dont't have Office 2007 or greater installed, the Import Wizard (32 Bit) only allows you to import Excel 97-2003 (.xls) files.

如果没有安装Office 2007或更高版本，导入向导(32位)只允许您导入Excel 97-2003 (.xls)文件。

If you have the Office 2010 and geater (comes also in 64 Bit, not recommended) installed, the Import Wizard also supports Excel 2007+ (.xlsx) files.

如果您已经安装了Office 2010和geater(也安装了64位，不建议安装)，导入向导还支持Excel 2007+ (.xlsx)文件。

To get an overview on the runtimes see 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

要获得运行时的概述，请参阅“microsoft.ac . oledb .12.0”提供程序没有在本地机器上注册

#1

#2