使用 Excel 作为 ODBC 数据库 [英] Using Excel as an ODBC database

问题描述

我想知道，如何在 Excel 中创建一个数据库表，以便它可以与 ODBC 一起使用

I'd like to know, how to create a database table in Excel, so that it may be used with ODBC

我想使用 ODBC，我有两个选择，MS Access 或 Excel，

I want to use ODBC, and I have two options, either MS Access or Excel,

您可能知道，为了将某些 MS Access 文件或 Excel 文件指示为 ODBC 源，您需要遵循:

As you probably know, in order to indicate some MS Access file or Excel file as an ODBC source, you need to follow:

管理工具 -> 数据源 (ODBC) -> 选择用户 DSN -> 从列表中选择Excel 文件"或MS Access 数据库" -> 按配置" -> 最后选择文件(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

嗯，它在 MS Access 上运行良好，我可以连接到文件并查看我在其中创建的所有表

Well, it works fine with MS Access, I can connect to the file and see all tables that I've created inside

但是说到Excel，虽然可以连接文件，但是看不到自己在里面创建的表格

But when it comes to Excel, although I can connect to the file, I can't see the table that I've created inside

我只是在插入"选项卡中使用了表格"，添加了一些标题作为列名，并给出了表格一个有意义的名字.是这样吗?

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?

推荐答案

可以通过多种方式引用 Excel 工作簿中的表格"数据:

There are several ways you can reference "table" data in an Excel workbook:

• 整个工作表.
• 工作表上命名的单元格范围.
• 工作表上未命名的单元格范围.

Microsoft 知识库文章 257819 的使用代码选择 Excel 数据"部分对它们进行了详细说明.

They are explained in detail in the "Select Excel Data with Code" section of the Microsoft Knowledge Base article 257819.

最直接的方法是将数据保存在单独的工作表中，将列名放在第一行(从单元格 A1 开始)，然后将实际数据从第 2 行开始，就像这样

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

为了测试，我创建了一个名为odbcFromExcel"的用户 DSN，指向该工作簿...

To test, I created a User DSN named "odbcFromExcel" that pointed to that workbook...

...然后运行以下 VBScript 来测试连接:

...and then ran the following VBScript to test the connection:

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

结果是

C:UsersGordDocuments\__tmp>cscript /nologo excelTest.vbs
Data row 1, rst("LastName").Value="Thompson"
10 data rows found.

我希望对您的 Excel 连接问题有所帮助.

I hope that helps your Excel connection issue.

作为最后的评论，我不得不说，如果您在 Excel 中执行的操作需要几秒钟"但在 Access 中需要大约 20-25 分钟"，那么我强烈怀疑您正在使用 Access一种非常低效的方式，但这是另一个问题的主题(如果您愿意继续研究的话).

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 very inefficient way, but that's a topic for another question (if you care to pursue it).

如果您想将数据插入 Excel 工作簿，那么这是可能的，但请注意 Excel ODBC 连接的默认设置为只读"，因此您必须单击选项>>"按钮并清除复选框:

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:

一旦完成，下面的代码...

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

...确实会使用提供的数据在 Excel 工作表中追加一个新行.

...will indeed append a new row in the Excel sheet with the data provided.

但是，当您将嗅探器"应用指向 Excel ODBC DSN 时，这仍然无法解决没有可供选择的表格"的问题.

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.

您可以尝试的一件事是创建一个在第 1 行带有列标题的 Excel 工作表，然后选择那些整列并创建一个 Excel定义名称".然后，查看您的嗅探器"应用是否将其识别为您可以选择的表"名称.

One thing you could try would be to create an Excel sheet with column headings in row 1, then select those entire columns and create an Excel "Defined Name". Then, see if your "sniffer" app recognizes that as a "table" name that you can select.

FWIW，我在 Excel 工作簿中将名称 myTable 定义为 =Sheet1!$A:$C，然后我的原始代码 sort 在我使用 SELECT * FROM [myTable] 时起作用:

FWIW, I defined the name myTable as =Sheet1!$A:$C in my Excel workbook, and then my original code sort of worked when I used SELECT * FROM [myTable]:

C:UsersGordDocuments\__tmp>cscript /nologo excelTest.vbs
Data row 1, rst("LastName").Value="Thompson"
1048576 data rows found.

如您所见，它正确检索了第一条记录"，但随后无法识别有效数据的结尾并继续读取工作表中的约 100 万行.

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.

我非常怀疑我是否会为此付出更多努力，因为我同意其他评论，即使用 Excel 作为ODBC 数据库"确实不是一个好主意.

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.

我强烈建议您尝试找出为什么您之前使用 Access 的尝试如此不令人满意.正如我之前所说，在我看来，在与 Access 交互方面非常糟糕.

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 job at interacting with Access.

这篇关于使用 Excel 作为 ODBC 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！