如何在 Excel 表上运行 SQL 查询？

Question

我正在尝试从另一个表中创建一个子表，该表包含按 AZ 排序的所有姓氏字段，其中电话号码字段不为空。我可以用 SQL 很容易地做到这一点，但我不知道如何在 Excel 中运行 SQL 查询。我很想将数据导入 postgresql 并在那里查询，但这似乎有点过分。

对于我正在尝试做的事情，SQL 查询 SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname 可以解决问题。这似乎太简单了，因为它不是 Excel 本身无法做到的。如何在 Excel 中运行这样的 SQL 查询？

原文由 Vap0r 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

有很多很好的方法可以完成这项工作，其他人已经提出了这些建议。按照“通过 SQL 轨道获取 Excel 数据”，这里有一些提示。

1. Excel 具有 “数据连接向导” ，它允许您从另一个数据源或什至在同一个 Excel 文件中导入或链接。

2. 作为 Microsoft Office（和操作系统）的一部分，有两个感兴趣的提供程序：旧的“Microsoft.Jet.OLEDB”和最新的“Microsoft.ACE.OLEDB”。在设置连接时查找它们（例如使用数据连接向导）。

3. 一旦连接到 Excel 工作簿，工作表或区域就相当于表或视图。工作表的表名是工作表的名称，其后附加一个美元符号（“$”），并用方括号（“[”和“]”）括起来；范围，它只是范围的名称。要将未命名的单元格区域指定为记录源，请将标准 Excel 行/列表示法附加到方括号中工作表名称的末尾。

4. 本机 SQL 将（或多或少）是 Microsoft Access 的 SQL。 （过去，它被称为 JET SQL；但是 Access SQL 已经发展，我相信 JET 已被弃用。）

5. 例如，阅读工作表： SELECT * FROM [Sheet1$]

6. 例如，读取一个范围： SELECT * FROM MyRange

7. 例如，读取未命名的单元格范围： SELECT * FROM [Sheet1$A1:B10]

8. 有许多书籍和网站可以帮助您了解这些细节。

补充说明

默认情况下，假定 Excel 数据源的第一行包含可用作字段名称的列标题。如果不是这种情况，您必须关闭此设置，否则您的第一行数据“消失”以用作字段名称。这是通过将可选的 HDR= setting 添加到连接字符串的扩展属性来完成的。不需要指定的默认值为 HDR=Yes 。如果没有列标题，则需要指定 HDR=No ；提供商将您的字段命名为 F1、F2 等。

关于指定工作表的注意事项：提供程序假定您的数据表以指定工作表上最上方、最左侧的非空白单元格开始。换句话说，您的数据表可以毫无问题地从第 3 行 C 列开始。但是，例如，您不能在单元格 A1 中的数据上方和左侧键入工作表标题。

关于指定范围的注意事项：当您将工作表指定为记录源时，如果空间允许，提供程序会在工作表中现有记录的下方添加新记录。当您指定一个范围（已命名或未命名）时，Jet 还会在空间允许的情况下在该范围内现有记录的下方添加新记录。但是，如果您重新查询原始范围，则生成的记录集不包括范围外新添加的记录。

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:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0; 。对 Microsoft Excel 5.0 和 7.0 (95) 工作簿使用 Excel 5.0 源数据库类型，对 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿使用 Excel 8.0 源数据库类型。

连接到“最新”Excel（文件扩展名为 xlsx 的文件）： 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";

（更多详情请访问 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

Microsoft JET 4 详细信息位于 http://support.microsoft.com/kb/275561

原文由 rskar 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

这里接受的答案是旧技术，不应该尝试。

早在编写此问题时，Power Query 并不是一个众所周知的选项，除非您使用最新版本的 Office 并将其作为单独的加载项安装，否则它不可用。

现在，Power Query 包含在 Excel 中，默认情况下用于获取数据。这是做到这一点的正确方法。它简单、快速且有效。

这是 Power Query 中问题的答案。如果您在复制此内容时需要帮助，请搜索“Power Query 入门”。开始使用 Power Query 后，您会发现这是非常基本且易于使用高级编辑器完成的操作：

 let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"lastname", type text}, {"firstname", type text}, {"phonenumber", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([phonenumber] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"lastname", "firstname", "phonenumber"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"lastname", Order.Ascending}})
in
    #"Sorted Rows"

原文由 TheRizza 发布，翻译遵循 CC BY-SA 4.0 许可协议