如何在 Excel 表上运行 SQL 查询? [英] How to run a SQL query on an Excel table?
问题描述
我正在尝试从另一个表中创建一个子表，该表包含按 A-Z 排序的所有姓氏字段，这些字段的电话号码字段不为空.我可以用 SQL 很容易地做到这一点，但我不知道如何在 Excel 中运行 SQL 查询.我很想将数据导入 postgresql 并在那里查询，但这似乎有点过分.
I'm trying to create a sub-table from another table of all the last name fields sorted A-Z which have a phone number field that isn't null. I could do this pretty easy with SQL, but I have no clue how to go about running a SQL query within Excel. I'm tempted to import the data into postgresql and just query it there, but that seems a little excessive.
对于我正在尝试做的事情，SQL 查询 SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname 可以解决问题.Excel本身无法做到的事情似乎太简单了.如何在 Excel 中运行这样的 SQL 查询?
For what I'm trying to do, the SQL query SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname would do the trick. It seems too simple for it to be something that Excel can't do natively. How can I run a SQL query like this from within Excel?
推荐答案
有很多很好的方法可以完成这项工作，其他人已经提出了建议.继通过 SQL 获取 Excel 数据"之后，这里有一些提示.
There are many fine ways to get this done, which others have already suggestioned. Following along the "get Excel data via SQL track", here are some pointers.
Excel 具有数据连接向导"，它允许您从另一个数据源或什至在同一个 Excel 文件中导入或链接.
Excel has the "Data Connection Wizard" which allows you to import or link from another data source or even within the very same Excel file.
作为 Microsoft Office(和操作系统)的一部分，有两个感兴趣的提供商:旧的Microsoft.Jet.OLEDB"和最新的Microsoft.ACE.OLEDB".在设置连接时寻找它们(例如使用数据连接向导).
As part of Microsoft Office (and OS's) are two providers of interest: the old "Microsoft.Jet.OLEDB", and the latest "Microsoft.ACE.OLEDB". Look for them when setting up a connection (such as with the Data Connection Wizard).
一旦连接到 Excel 工作簿，工作表或区域就相当于表格或视图.工作表的表名是工作表的名称，附加一个美元符号($")，并用方括号(["和]")括起来；的范围，它只是范围的名称.要将未命名的单元格范围指定为记录源，请将标准 Excel 行/列符号附加到方括号中的工作表名称末尾.
Once connected to an Excel workbook, a worksheet or range is the equivalent of a table or view. The table name of a worksheet is the name of the worksheet with a dollar sign ("$") appended to it, and surrounded with square brackets ("[" and "]"); of a range, it is simply the name of the range. To specify an unnamed range of cells as your recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets.
本机 SQL 将(或多或少)是 Microsoft Access 的 SQL.(过去，它被称为 JET SQL；但是 Access SQL 已经发展，我相信 JET 是不推荐使用的旧技术.)
The native SQL will (more or less be) the SQL of Microsoft Access. (In the past, it was called JET SQL; however Access SQL has evolved, and I believe JET is deprecated old tech.)
示例，读取工作表:SELECT * FROM [Sheet1$]
示例，读取范围:SELECT * FROM MyRange
示例，读取未命名的单元格范围:SELECT * FROM [Sheet1$A1:B10]
Example, reading an unnamed range of cells: SELECT * FROM [Sheet1$A1:B10]
有许多书籍和网站可以帮助您解决这些细节问题.
There are many many many books and web sites available to help you work through the particulars.
补充说明
默认情况下，假定 Excel 数据源的第一行包含可用作字段名称的列标题.如果不是这种情况，您必须关闭此设置，否则您的第一行数据将消失".用作字段名称.这是通过将可选的 HDR= setting 添加到连接字符串的扩展属性来完成的.不需要指定的默认值是 HDR=Yes.如果没有列标题，则需要指定HDR=No；提供商将您的字段命名为 F1、F2 等.
Further notes
By default, it is assumed that the first row of your Excel data source contains column headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes. If you do not have column headings, you need to specify HDR=No; the provider names your fields F1, F2, etc.
有关指定工作表的注意事项:提供程序假定您的数据表从指定工作表上最上方、最左侧的非空白单元格开始.换句话说，您的数据表可以从第 3 行 C 列开始，没有问题.但是，例如，您不能在单元格 A1 中数据的上方和左侧键入工作表标题.
A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheet title above and to the left of the data in cell A1.
指定范围的注意事项:当您将工作表指定为记录源时，提供商会在空间允许的情况下在工作表中的现有记录下方添加新记录.当您指定一个范围(命名或未命名)时，Jet 还会在空间允许的范围内在现有记录下方添加新记录.但是，如果您重新查询原始范围，则结果记录集不包括范围外的新添加记录.
A caution about specifying ranges: When you specify a worksheet as your recordsource, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range.
CREATE TABLE 的数据类型(值得一试):Short、Long、Single、Double、Currency、DateTime、Bit、Byte、GUID、BigBinary、LongBinary、VarBinary、LongText、VarChar、Decimal.
连接到旧技术"Excel(扩展名为 xls 的文件):Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MyFolderMyWorkbook.xls;Extended Properties=Excel 8.0;.将 Excel 5.0 源数据库类型用于 Microsoft Excel 5.0 和 7.0 (95) 工作簿，将 Excel 8.0 源数据库类型用于 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿.
Connecting to "old tech" Excel (files with the xls extention): Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MyFolderMyWorkbook.xls;Extended Properties=Excel 8.0;. Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97), 9.0 (2000) and 10.0 (2002) workbooks.
连接到最新"Excel(文件扩展名为 xlsx 的文件):Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties=Excel 12.0 Xml;HDR=YES;"
Connecting to "latest" Excel (files with the xlsx file extension): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"
将数据视为文本:IMEX 设置将所有数据视为文本.Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1";
Treating data as text: IMEX setting treats all data as text. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
(更多详情见 http://www.connectionstrings.com/excel)
更多信息位于 http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx 和 http://support.microsoft.com/kb/316934
通过 VBA 通过 ADODB 连接到 Excel，详见 http://support.microsoft.com/kb/257819
Connecting to Excel via ADODB via VBA detailed at http://support.microsoft.com/kb/257819
Microsoft JET 4 详细信息位于 http://support.microsoft.com/kb/275561
Microsoft JET 4 details at http://support.microsoft.com/kb/275561
这篇关于如何在 Excel 表上运行 SQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
