blocks|key|1452373|text|连接到Sql+Server+2005数据库后，在对象资源管理器窗口中，右键单击要将表导入其中的数据库。选择任务->导入数据。这是一个简单的工具，允许您将传入的数据“映射”到适当的表中。您可以保存脚本，以便在需要时再次运行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1452374|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1667317|text|这听起来可能有点长，但是您可能希望考虑使用Excel+to+generate+INSERT+SQL代码，您可以将这些代码传递到Query+Analyzer来创建表。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1667318|如果您无法使用向导，因为excel文件不在服务器上，则效果很好|1667319|entityMap|0|LINK|mutability|MUTABLE|url|http://tgrignon.wordpress.com/2007/03/07/using-excel-to-generate-inserts-for-sql-server/^0|L|O|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

This may sound like the long way around, but you may want to look at using <a href="http://tgrignon.wordpress.com/2007/03/07/using-excel-to-generate-inserts-for-sql-server/" rel="noreferrer">Excel to generate INSERT</a> SQL code that you can past into Query Analyzer to create your table.

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

blocks|key|1452413|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1452414|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1452491|text|另一种选择是在Excel中使用VBA，并编写宏来解析电子表格数据并将其写入SQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1452492|这里有一个例子：http://www.ozgrid.com/forum/showthread.php?t=26621&page=1|offset|length|1452493|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|code-block|syntax|javascript|1452494|entityMap|0|LINK|mutability|MUTABLE|url|http://www.ozgrid.com/forum/showthread.php?t=26621&page=1^0|0|8|1L|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@$D|U|E|V|1|W]]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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

One example is here: <a href="http://www.ozgrid.com/forum/showthread.php?t=26621&amp;page=1" rel="nofollow">http://www.ozgrid.com/forum/showthread.php?t=26621&amp;page=1</a>

<pre><code>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 &amp; "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
</code></pre>

blocks|key|1452254|text|我假设你说的是“SQL+Server导入和导出向导”。(我也是个新手，所以大多数问题我都不懂，更不用说大多数答案了，但我想我懂这个了)。如果是这样的话，您不能使用电子表格或其副本，删除您不想导入的列，然后使用向导吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1452255|我总是能够使用它做我需要的事情，而且我只使用SQL+Server+2000+(不确定其他版本有何不同)。|1452256|编辑:事实上，我现在正在研究它，我似乎能够选择我想要映射到现有表中的哪些行。在“选择源表和视图”屏幕上，检查我正在使用的数据表，选择“目标”，然后单击“编辑...”。按钮。在此，您可以选择Excel列和要将其映射到的表列。|1452257|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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?

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

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.

blocks|key|1667608|text|如果你想要一个带有桌面界面的可视化工具，包括验证..您可能会喜欢这个Excel工具。您还可以使用该工具创建多用户数据编辑任务，甚至将任何来源的数据粘贴到SQL+server。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1667609|如何验证Excel电子表格并将其导入SQL+Server数据库：|1667610|http://leansoftware.net/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx|offset|length|1667611|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|3J|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@$F|R|G|S|1|T]]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|E]]]]

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

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

<a href="http://leansoftware.net/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx" rel="nofollow">http://leansoftware.net/en-us/help/excel-database-tasks/worked-examples/how-to-import-excel-spreadsheet-to-sql-server-data.aspx</a>

blocks|key|1452343|text|Microsoft+Access是另一种选择。您可以在本地计算机上有一个Access数据库，您可以将excel电子表格导入到该数据库中(向导可用)和link+to+the+the+SQL+Server+database+tables+via+ODBC。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1452344|然后，您可以在access中设计一个查询，将Excel电子表格中的数据追加到SQL+Server表中。|1452345|entityMap|0|LINK|mutability|MUTABLE|url|http://office.microsoft.com/en-us/access/HA102004941033.aspx#2^0|22|1F|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

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 <a href="http://office.microsoft.com/en-us/access/HA102004941033.aspx#2" rel="nofollow noreferrer">link to the the SQL Server database tables via ODBC</a>.

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

blocks|key|1452550|text|我用过的最好的工具是http://tools.perceptus.ca/text-wiz.php?ops=7，你试过吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1452551|entityMap|0|LINK|mutability|MUTABLE|url|http://tools.perceptus.ca/text-wiz.php?ops=7^0|A|18|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

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

blocks|key|1667588|text|我想这会对你有帮助的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1667589|访问http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/78f8e980-0bb6-4a48-b1b1-5f65bca1d511|offset|length|1667590|entityMap|0|LINK|mutability|MUTABLE|url|http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/78f8e980-0bb6-4a48-b1b1-5f65bca1d511^0|0|2|2V|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

I think it will help you

Visit <a href="http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/78f8e980-0bb6-4a48-b1b1-5f65bca1d511" rel="nofollow">http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/78f8e980-0bb6-4a48-b1b1-5f65bca1d511</a>

blocks|key|1667226|text|导入向导确实提供了该选项。您可以使用选项为要导入的数据编写自己的查询，也可以使用copy+data选项并使用"Edit+Mappings“按钮忽略不想导入的列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1667227|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1667438|text|Excel+%2B+SQLCMD+%2B+Perl+=+exceltomssqlinsert|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1667439|你可以使用你的Excel作为MSSQL数据库的前端...注意每个生成的sql插入文件开头的截断表...|1667440|entityMap|0|LINK|mutability|MUTABLE|url|http://code.google.com/p/exceltomssqlinsert/^0|O|I|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

Excel + SQLCMD + Perl = <a href="http://code.google.com/p/exceltomssqlinsert/" rel="nofollow">exceltomssqlinsert</a>

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

blocks|key|1667554|text|我使用过DTS+(现在称为SQL+server导入和导出向导)。我使用了this+tutorial，即使在SQL2008和excel+2010+(14.0)中，它对我也很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1667555|我希望这能帮到你|1667556|-D|1667557|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sql-server-performance.com/2008/how-to-import-data-from-excel-2007/1/^0|15|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

I have used DTS (now known as SQL server Import and Export Wizard). I used the this <a href="http://www.sql-server-performance.com/2008/how-to-import-data-from-excel-2007/1/" rel="nofollow">tutorial</a> which worked great for me even in Sql 2008 and excel 2010 (14.0)

I hope this helps

-D

blocks|key|1452641|text|首先，尝试使用32位版本的导入向导。这显示了更多受支持的导入格式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1452642|背景:所有这些都取决于您的Office+(运行时引擎)安装。|1452643|如果未安装Office2007或更高版本，则导入向导(32位)仅允许您导入Excel97-2003+(.xls)文件。|1452644|如果您安装了Office2010和geater+(也有64位版本，不推荐)，导入向导还支持Excel+2007%2B+(.xlsx)文件。|1452645|要获得有关运行时的概述，请参阅'Microsoft.ACE.OLEDB.12.0'+provider+is+not+registered+on+the+local+machine|offset|length|1452646|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/6649363/microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/32247214#32247214^0|0|0|0|0|F|22|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@$J|Y|K|Z|1|10]]|A|$]]|$1|L|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]]]

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

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.

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.

To get an overview on the runtimes see <a href="https://stackoverflow.com/questions/6649363/microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/32247214#32247214">&#39;Microsoft.ACE.OLEDB.12.0&#39; provider is not registered on the local machine</a>

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.

Do any easy code options exist?

Import Excel spreadsheet columns into SQL Server database

我有一个Excel电子表格，我要将选择列导入到SQL Server 2008数据库表中。向导没有提供该选项。有没有什么简单的代码选项？

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

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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