database 使用 Excel 作为 ODBC 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15844633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Excel as an ODBC database
提问by mangusta
I'd like to know, how to create a database table in Excel, so that it may be used with ODBC
我想知道，如何在 Excel 中创建一个数据库表，以便它可以与 ODBC 一起使用
I want to use ODBC, and I have two options, either MS Access or Excel,
我想使用 ODBC，我有两个选项，MS Access 或 Excel，
As you probably know, in order to indicate some MS Access file or Excel file as an ODBC source, you need to follow:
您可能知道，为了将某些 MS Access 文件或 Excel 文件指示为 ODBC 源，您需要遵循：
Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose either 'Excel Files' or 'MS Access Database' from the list -> Press 'Configure' -> finally choose the file (MS Access or Excel) as ODBC source
管理工具 -> 数据源 (ODBC) -> 选择用户 DSN -> 从列表中选择“Excel 文件”或“MS Access 数据库” -> 按“配置” -> 最后选择文件（MS Access 或 Excel）作为ODBC 源
Well, it works fine with MS Access, I can connect to the file and see all tables that I've created inside
嗯，它在 MS Access 上运行良好，我可以连接到文件并查看我在其中创建的所有表
But when it comes to Excel, although I can connect to the file, I can't see the table that I've created inside
但是说到Excel，虽然可以连接到文件，但是看不到我在里面创建的表
I just used 'Table' in 'Insert' tab, added some headers as column names, and gave the table a meaningful name. Is that the way to do it?
我只是在“插入”选项卡中使用了“表格”，添加了一些标题作为列名，并为表格指定了一个有意义的名称。这是这样做的方式吗？
回答by Gord Thompson
There are several ways you can reference "table" data in an Excel workbook:
您可以通过多种方式引用 Excel 工作簿中的“表格”数据：
- An entire worksheet.
- A named range of cells on a worksheet.
- An unnamed range of cells on a worksheet.
- 整个工作表。
- 工作表上的命名单元格范围。
- 工作表上未命名的单元格区域。
They are explained in detail in the "Select Excel Data with Code" section of the Microsoft Knowledge Base article 257819.
Microsoft 知识库文章257819 的“使用代码选择 Excel 数据”部分对它们进行了详细说明。
The most straightforward way is to keep the data on a separate sheet, put column names in the first row (starting in cell A1), and then have the actual data start in row 2, like this
最直接的方法是将数据保存在单独的工作表上，将列名放在第一行（从单元格 A1 开始），然后将实际数据从第 2 行开始，就像这样
To test, I created a User DSN named "odbcFromExcel" that pointed to that workbook...
为了进行测试，我创建了一个名为“odbcFromExcel”的用户 DSN，指向该工作簿...
...and then ran the following VBScript to test the connection:
...然后运行以下 VBScript 来测试连接：
Option Explicit
Dim con, rst, rowCount
Set con = CreateObject("ADODB.Connection")
con.Open "DSN=odbcFromExcel;"
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT * FROM [Sheet1$]", con
rowCount = 0
Do While Not rst.EOF
rowCount = rowCount + 1
If rowCount = 1 Then
Wscript.Echo "Data row 1, rst(""LastName"").Value=""" & rst("LastName").Value & """"
End If
rst.MoveNext
Loop
Wscript.Echo rowCount & " data rows found."
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
The results were
结果是
C:\Users\Gord\Documents\__tmp>cscript /nologo excelTest.vbs
Data row 1, rst("LastName").Value="Thompson"
10 data rows found.
I hope that helps your Excel connection issue.
我希望能帮助您解决 Excel 连接问题。
As a final comment I have to say that if you are doing something that takes "several seconds" to do in Excel but "takes around 20-25 min" to do in Access then I strongly suspect that you are using Access in a veryinefficient way, but that's a topic for another question (if you care to pursue it).
作为最后的评论，我不得不说，如果您在 Excel 中执行的操作需要“几秒钟”，但在 Access 中“需要大约 20-25 分钟”，那么我强烈怀疑您使用 Access 的效率非常低方式，但这是另一个问题的主题（如果您愿意继续研究的话）。
EDIT
编辑
If you want to INSERT data into an Excel workbook then that is possible, but be aware that the default setting for an Excel ODBC connection is "Read Only" so you have to click the "Options>>" button and clear that checkbox:
如果您想将数据插入 Excel 工作簿，那么这是可能的，但请注意 Excel ODBC 连接的默认设置是“只读”，因此您必须单击“选项>>”按钮并清除该复选框：
Once that's done, the following code...
完成后，以下代码...
Option Explicit
Dim con
Set con = CreateObject("ADODB.Connection")
con.Open "DSN=odbcFromExcel;"
con.Execute "INSERT INTO [Sheet1$] (ID, LastName, FirstName) VALUES (11, 'Dumpty', 'Humpty')"
con.Close
Set con = Nothing
Wscript.Echo "Done."
...will indeed append a new row in the Excel sheet with the data provided.
...确实会在 Excel 工作表中添加一个新行，其中包含提供的数据。
However, that still doesn't address the problem of no "Tables" being available for selection when you point your "sniffer" app at an Excel ODBC DSN.
但是，这仍然不能解决当您将“嗅探器”应用程序指向 Excel ODBC DSN 时没有可供选择的“表格”的问题。
One thing you could try would be to create an Excel sheet with column headings in row 1, then select those entire columnsand create an Excel "Defined Name". Then, see if your "sniffer" app recognizes that as a "table" name that you can select.
您可以尝试的一件事是在第 1 行创建一个带有列标题的 Excel 工作表，然后选择这些整列并创建一个 Excel“定义名称”。然后，查看您的“嗅探器”应用程序是否将其识别为您可以选择的“表”名称。
FWIW, I defined the name myTableas =Sheet1!$A:$Cin my Excel workbook, and then my original code sort ofworked when I used SELECT * FROM [myTable]:
FWIW，我定义的名字myTable为=Sheet1!$A:$C我的Excel工作簿，然后我的原代码排序，当我用工作SELECT * FROM [myTable]：
C:\Users\Gord\Documents\__tmp>cscript /nologo excelTest.vbs
Data row 1, rst("LastName").Value="Thompson"
1048576 data rows found.
As you can see, it retrieved the first "record" correctly, but then it didn't recognize the end of the valid data and continued to read the ~1 million rows in the sheet.
如您所见，它正确检索了第一条“记录”，但随后无法识别有效数据的结尾并继续读取工作表中的约 100 万行。
I doubt very much that I will be putting any more effort into this because I agree with the other comments that using Excel as an "ODBC database" is really not a very good idea.
我非常怀疑我是否会为此付出更多努力，因为我同意其他评论，即使用 Excel 作为“ODBC 数据库”确实不是一个好主意。
I strongly suggest that you try to find out why your earlier attempts to use Access were so unsatisfactory. As I said before, it sounds to me like something was doing a really bad jobat interacting with Access.
我强烈建议您尝试找出为什么您之前使用 Access 的尝试如此不令人满意。正如我之前所说，在我看来，在与 Access 交互方面做得非常糟糕。
回答by Alyssa Goldberg
I had a similar problem with some data recently. The way I managed to get around it was to select the data as a range A1:XY12345, then use the Define Name tool to name the range. When you connect to the Excel workbook via ODBC, this named range will appear as a "table," while ranges that you actually defined (per Excel) as a table, do not.
我最近在处理一些数据时遇到了类似的问题。我设法绕过它的方法是将数据选择为 A1:XY12345 范围，然后使用“定义名称”工具命名该范围。当您通过 ODBC 连接到 Excel 工作簿时，此命名区域将显示为“表格”，而您实际定义（每个 Excel）为表格的区域则不会。
回答by Maneli
You just need to select as many as required columns from first row of your excel file and then give a name to it on the edit box left to the formula bar. Of course you give a name to each column of the file too!
您只需要从 Excel 文件的第一行中选择尽可能多的列，然后在编辑栏左侧的编辑框中为其命名。当然，您也可以为文件的每一列命名！
