如何在Excel表上运行SQL查询? [英] How to run a SQL query on an Excel table?
问题描述
我正试图从所有姓氏字段按A-Z排序的另一个表中创建一个子表，该表的电话号码字段不为null.我可以使用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(和OS)的一部分，有两个感兴趣的提供程序:旧的"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.
===其他说明===
=== Further notes ===
默认情况下，假定Excel数据源的第一行包含可用作字段名称的列标题.如果不是这种情况，则必须关闭此设置，否则第一行数据消失"以用作字段名称.这是通过将可选的HDR= setting添加到连接字符串的扩展属性来完成的.无需指定的默认值为HDR=Yes.如果没有列标题，则需要指定HDR=No.提供商将您的字段命名为F1，F2等.
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中的数据的上方和左侧输入worksheeet标题.
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 worksheeet 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.
C REATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal的数据类型(值得尝试).
Data types (worth trying) for 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;.将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:\MyFolder\MyWorkbook.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 )
(More details at http://www.connectionstrings.com/excel)
有关更多信息，请参见 http://msdn. microsoft.com/zh-CN/library/ms141683(v=sql.90).aspx ，并位于
More information at http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx, and at 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屋！
