How to run an SQL query on an Excel spreadsheet?

Question

I am trying to create a nested table from another table of all surname fields sorted by AZ that have a phone number field that is not null. I could do this quite easily using SQL, but I have no idea how to execute an SQL query in Excel. I feel like importing data into postgresql and just querying it there, but that seems a bit overkill.

For what I'm trying to do, SQL query SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname will help. This seems too simple for Excel to not be able to do this initially. How can I execute such a SQL query from Excel?

Answer 1

There are many great ways to do this, as others have already stated. Following the "Get Excel Data Through SQL Track", here are a few pointers.

• Excel has a "Data Connection Wizard" that allows you to import or link from another data source or even to the same Excel file.

• Microsoft Office (and the OS) has two providers of interest: the old "Microsoft.Jet.OLEDB" and the last "Microsoft.ACE.OLEDB". Look for them when setting up the connection (for example, using the data connection wizard).

• Once connected to an Excel workbook, the worksheet or range is equivalent to a table or view. The worksheet table name is the name of the worksheet with the dollar sign ("$") attached to it and surrounded by square brackets ("[" and "]"); range, it's just the name of the range. To specify an unnamed range of cells as the recording source, add the standard Excel column / column notation to the end of the sheet name in square brackets.

• Native SQL will (more or less be) Microsoft Access SQL. (It used to be called JET SQL, but Access SQL has evolved, and I believe that JET has deprecated old tech.)

• Example: reading a worksheet: SELECT * FROM [Sheet1 $]

• Example: reading range: SELECT * FROM MyRange

• Example: reading an unnamed range of cells: SELECT * FROM [Sheet1 $ A1: B10]

• There are many many books and websites to help you get the details.

=== Further notes ===

By default, it is assumed that the first row of the Excel data source contains column headers that can be used as field names. If this is not the case, you should disable this option, or your first row of data "disappears" to be used as field names. This is done by adding the additional parameter HDR = to the extended properties of the connection string. The default value that does not need to be specified is HDR = Yes. If you do not have column headers, you need to specify HDR = No; the provider calls your fields F1, F2, etc.

Warning for specifying worksheets. The provider assumes that your data table begins with the most recent, leftmost, non-empty cell on the specified sheet. In other words, your data table can start at row 3, column C without a problem. However, you cannot, for example, print the workheeet header above and to the left of the data in cell A1.

Range warning. When you specify a worksheet as the source of a record, the provider adds new records below existing records in the worksheet, as space permits. When you specify a range (named or unnamed), Jet also adds new entries under existing entries in the range, as space permits. However, if you request a source range, the resulting record set does not include newly added records outside the range.

Data types (worth a try) for CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, bits, bytes, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.

Connection to the "old technology" Excel (files with the xls extension): Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0; . Use the original Excel 5.0 database type for Microsoft Excel books 5.0 and 7.0 (95) and use the original Excel 8.0 database type for Microsoft Excel books 8.0 (97), 9.0 (2000), and 10.0 (2002).

Connection to the "last" Excel (files with the xlsx extension): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"

Processing data as text: IMEX installation processes all data as text. Provider = Microsoft.ACE.OLEDB.12.0; Data Source = Excel2007file.xlsx; Advanced Properties = "Excel 12.0 Xml; HDR = YES; IMEX = 1";

(For more details see http://www.connectionstrings.com/excel )

Additional information at http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx and http://support.microsoft.com/kb/316934

Connecting to Excel via ADODB via VBA, described in detail at http://support.microsoft.com/kb/257819

Microsoft JET 4 information at http://support.microsoft.com/kb/275561

Answer 2

You can do this initially as follows:

• Select a table and use Excel to sort by name
• Create advanced filter criteria with 2 rows in 1 column, say in E1 and E2, where E1 is empty and E2 contains the formula =C6="" where C6 is the first data cell of the telephone number column.
• Select the table and use the advanced filter, copy to the range using the criteria range in E1: E2 and indicate where you want to copy the output to

If you want to do this programmatically, I suggest you use Macro Recorder to record the above steps and view the code.

Answer 3

TL; DR; Excel does it all natively - use filters and tables

( http://office.microsoft.com/en-gb/excel-help/filter-data-in-an-excel-table-HA102840028.aspx )

You can open excel program through oledb connection and execute SQL in tables on sheet.

But you can do whatever you ask to just filter without formulas.

• click anywhere the data you are looking for
• go to data on the ribbon bar
• select "Filter" around the middle and looks like a funnel
  • You will have arrows on the hard side of each cell in the first row of the table.
• click the arrow on the phone number and uncheck the boxes (last option)
• click the arrow on the last name and select az ordering (top option)

there is a game around .. some things to note:

• you can select the filtered rows and paste them somewhere else.
• in the status bar on the left you will see how many lines match the filtering criteria of the total number of lines. (for example, 308 of 313 records found).
• you can filter by color in excel 2010 on wards
• Sometimes I create computed columns that provide statuses or cleared versions of data that you can then filter or sort by these points. (e.g., as formulas in other answers).

Do this with filters if you are not going to do it a lot or you want to automate the import of data somewhere or something like that, but for completeness:

C # parameter:

  OleDbConnection ExcelFile = new OleDbConnection( String.Format( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES\"", filename)); ExcelFile.Open(); 

A convenient place to start is to take a look at the diagram, as there may be more than you think:

 List<String> excelSheets = new List<string>(); // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { string temp = row["TABLE_NAME"].ToString(); if (temp[temp.Length - 1] == '$') { excelSheets.Add(row["TABLE_NAME"].ToString()); } } 

then when you want to request a sheet:

  OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + sheet + "]", ExcelFile); dt = new DataTable(); da.Fill(dt); 

NOTE. - Use tables in excel !:

Excel has “tables” that make data more like a table. This gives you some great benefits, but does not allow you to make all types of requests.

http://office.microsoft.com/en-gb/excel-help/overview-of-excel-tables-HA010048546.aspx

For tabular data in excel, this is my default value. The first thing I do is click on the data, and then select the “table format” from the home section on the tape. this gives you default filtering and sorting and allows you to access the table and fields by name (for example, table [fieldname]), it also allows you to create aggregate functions in columns, for example. max and average

Answer 4

You can use SQL in Excel. It is only well hidden. See this tutorial:

http://smallbusiness.chron.com/use-sql-statements-ms-excel-41193.html

Answer 5

If you need to do this, just follow Charles's descriptions, but this can also be done using Excel formulas and auxiliary columns if you want to make a dynamic filter.

Suppose the data is on a DataSheet and begins on row 2 of the following columns:

• A: lastname
• B: firstname
• C: phonenumber

You need two auxiliary columns on this sheet.

• D2: =if(A2 = "", 1, 0) , this is a filter column that matches your condition
• E2: =if(D2 <> 1, "", sumifs(D$2:D$1048576, A$2:A$1048576, "<"&A2) + sumifs(D$2:D2, A$2:A2, A2)) , this matches the order on

Copy these formulas according to your data.

On the sheet that should display your result, create the following columns.

• A: a sequence of numbers starting with 1 on line 2 limits the total number of lines you can get (sort of like a limit in the future)
• B2: =match(A2, DataSheet!$E$2:$E$1048576, 0) , this is a row of corresponding data
• C2: =iferror(index(DataSheet!A$2:A$1048576, $B2), "") , this is actual data or empty if no data exists

Copy the formulas in B2 and C2 and copy column C to D and E.

Answer 6

I can suggest giving QueryStorm a try - this is a plugin for Excel, which makes it very convenient to use SQL in Excel.

In addition, it is freemium. If you are not interested in autocomplete, squigglies errors, etc., you can use it for free. Just download and install, and you have SQL support in Excel.

Disclaimer: I am the author.

Answer 7

If you want to run SQL when combining data from multiple Excel sheets or from multiple Excel files, you can use a cloud service called Rockset . Rockset provides you with an SQL interface that can combine data from Excel, Pdf or txt / xml / json / csv.

Answer 8

You can experiment with your own DB driver for Excel in your chosen language / platform. In the Java world, you can try the http://code.google.com/p/sqlsheet/ , which provides the JDBC driver for working with Excel worksheets directly. Similarly, you can get drivers for database technology for other platforms.

However, I can guarantee that you will soon click on the wall with the number of functions provided by these cover libraries. It would be better to use Apache HSSF / POI or a similar library level, but this will require more coding efforts.

Answer 9

Perhaps I do not understand, but is it not a pivot table? Do you have data in the table or just a filtered list? If his table does not make it one (ctrl + l), if it is, then just activate any cell in the table and paste the pivot table on another sheet. Then add the columns lastname, firstname, phonenumber to the row section. Then add the phone number to the filter section and filter the zero values. Sort now looks as usual.

Answer 10

I suggest you take a look at MySQL csv storage engine , which essentially allows you to load any csv file (easily created from excel) into the database, as soon as you do this, you can use any SQL command that you want.

Worth a look at this.

Answer 11

If you have GDAL / OGR compiled using the Expat library, you can use the XLSX driver to read .xlsx files and run SQL statements from the command line. For example, from the osgeo4w shell in the same directory as the spreadsheet, use ogrinfo :

 ogrinfo -dialect sqlite -sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx 

will execute the SQLite query on sheet1 and display the query result in an unusual form:

 INFO: Open of `Book1.xlsx' using driver `XLSX' successful. Layer name: SELECT Geometry: None Feature Count: 36 Layer SRS WKT: (unknown) name: String (0.0) count(*): Integer (0.0) OGRFeature(SELECT):0 name (String) = Red count(*) (Integer) = 849 OGRFeature(SELECT):1 name (String) = Green count(*) (Integer) = 265 ... 

Or run the same query using ogr2ogr to make a simple CSV file:

 $ ogr2ogr -f CSV out.csv -dialect sqlite \ -sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx $ cat out.csv name,count(*) Red,849 Green,265 ... 

To do the same with old .xls files, you will need an XLS driver created against the FreeXL library, which is actually not a common occurrence (for example, not from OSGeo4w).

Answer 12

Microsoft Access and LibreOffice Base can open a spreadsheet as a source and run SQL queries on it. This would be the easiest way to run all kinds of queries, and also avoid the mess of running macros or writing code.

Excel also has autofilters and data sorting that will perform many simple queries like your example. If you need help with these features, Google will be a better source for tutorials than me.