数据导入-Excel到SQL Azure吗? [英] Data Import - Excel to SQL Azure?

问题描述

有没有可用的工具可以轻松地将数据从Excel工作表(甚至是Access)导入到SQL Azure?我可以编写一个C#脚本(我假设现在是最好的脚本)，但我想我会检查一下是否还有其他工具.我有一个简单的Excel工作表要导入，但是它有成千上万的记录，宁愿使用自动化工具来完成.

Are there any available tools that make importing data from an Excel sheet (or even Access) to SQL Azure easy? I could write a C# script (and I'm assuming that the best available right now) but I thought I'd check in case there were other tools out there. I have as simple Excel sheet to import but its tens-of-thousands of records and would rather do it with an automated tool.

帮助?

推荐答案

选项1，复制和粘贴

这是一个非常粗糙的解决方案，但是我以前使用过它，但是数据量较小.只需在Excel中选择行和列，确保它们与数据库表架构相关联.复制到剪贴板.在Visual Studio中打开与数据库的连接，选择最后一个空行并粘贴.

This a very crude solution but I have used it before, admittedly with smaller volumes of data. Simply select the rows and columns in Excel, making sure they correlate with your database table schema. Copy to the Clipboard. Open up a connection to your database in Visual Studio select the last empty row and paste.

就像我说的那样，这是一个非常粗糙的解决方案，但是如果可行，并且可以节省您的时间，那么这很重要.首先尝试选择少量行，并且不要忘记排除任何自动递增的列.如果首先转换为CSV，则将删除可能无法导入到表模式中的所有Excel格式.老实说，我不知道该方法如何在更大的数据集上执行，显然它将取决于Excel文档中的数据，列数等.您可能需要将数据分块移动，但很可能是比其他方法更快.

Like I said, a very crude solution but if it works and saves you time that's what matters. Try with a small selection of rows first and don't forget to exclude any auto-incrementing columns. If you convert to CSV first you will nuke any Excel formatting that may not import into your table schema. I have to be honest I don't know how this method will perform with larger datasets, obviously it will depend on the data in the Excel document, number of columns etc. You may need to move the data in chunks but it could well be quicker than other methods.

选项2，CSV到数据表

第二种方法需要一些编码，但可以让您更好地控制如何将数据映射到数据表.基本上，它涉及从Excel将文档转换为CSV，将行循环读入DataTable对象并插入到SQL数据库表中.

The second method requires some coding but will give you more control over how the data is mapped to the data table. It basically involves converting the document to a CSV from Excel, reading into a DataTable object looping the rows and inserting to the SQL Database table.

Dim dt As DataTable = New DataTable("myData")
Dim DataFile As FileInfo = New FileInfo("c:\myspreadsheet.csv")

Dim MyConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & DataFile.Directory.FullName & "';Extended Properties='text;HDR=NO';Jet OLEDB:Engine Type=96;")
Dim oledataAdapter As OleDbDataAdapter
oledataAdapter = New OleDbDataAdapter("SELECT * FROM [" & DataFile.Name & "]", MyConnection)

oledataAdapter.Fill(dt) 'Bind the csv to the data table

'LOOP AND INSERT HERE...
For Each DataRowObj As DataRow In dt.Rows

Next

这篇关于数据导入-Excel到SQL Azure吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！