How to run a SQL query on an Excel table

Question

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

User · Answer

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

Excel also has autofilters and data sorting that will accomplish a lot of simple queries like your example. If you need help with those features, Google would be a better source for tutorials than me.

User · Answer

If you have GDAL OGR compiled with the against the Expat library  you can use the XLSX driver to read  xlsx files  and run SQL expressions from a command prompt  For example  from a osgeo4w shell in the same directory as the spreadsheet  use the ogrinfo utility   ogrinfo -dialect sqlite -sql  SELECT name  count    FROM sheet1 GROUP BY name  Book1 xlsx   will run a SQLite query on sheet1  and output 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 similar with older  xls files  you would need the XLS driver  built against the FreeXL library  which is not really common  e g  not from OSGeo4w

User · Answer

I might be misunderstanding me  but isn t this exactly what a pivot table does  Do you have the data in a table or just a filtered list  If its not a table make it one  ctrl l  if it is  then simply activate any cell in the table and insert a pivot table on another sheet  Then Add the columns lastname  firstname  phonenumber to the rows section  Then Add Phone number to the filter section and filter out the null values  Now Sort like normal

User · Answer

You can experiment with the native DB driver for Excel in language platform of your choice  In Java world  you can try with http   code google com p sqlsheet  which provides a JDBC driver for working with Excel sheets directly  Similarly  you can get drivers for the DB technology for other platforms    However  I can guarantee that you will soon hit a wall with the number of features these wrapper libraries provide  Better way will be to use Apache HSSF POI or similar level of library but it will need more coding effort

User · Answer

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 has the  Data Connection Wizard  which allows you to import or link from another data source or even within the very same Excel file  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   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  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   Example  reading a worksheet  SELECT   FROM  Sheet1   Example  reading a range  SELECT   FROM MyRange 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      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   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   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   Data types  worth trying  for CREATE TABLE  Short  Long  Single  Double  Currency  DateTime  Bit  Byte  GUID  BigBinary  LongBinary  VarBinary  LongText  VarChar  Decimal   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   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    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     More details at http   www connectionstrings com excel   More information at http   msdn microsoft com en-US library ms141683 v sql 90  aspx  and at http   support microsoft com kb 316934  Connecting to Excel via ADODB via VBA detailed at http   support microsoft com kb 257819  Microsoft JET 4 details at http   support microsoft com kb 275561

User · Answer

Might I suggest giving QueryStorm a try - it s a plugin for Excel that makes it quite convenient to use SQL in Excel   Also  it s freemium  If you don t care about autocomplete  error squigglies etc  you can use it for free  Just download and install  and you have SQL support in Excel   Disclaimer  I m the author

User · Answer

You can do this natively as follows    Select the table and use Excel to sort it on Last Name Create a 2-row by 1-column advanced filter criteria  say in E1 and E2  where E1 is empty and E2 contains the formula  C6     where C6 is the first data cell of the phone number column  Select the table and use advanced filter  copy to a range  using the criteria range in E1 E2 and specify where you want to copy the output to   If you want to do this programmatically I suggest you use the Macro Recorder to record the above steps and look at the code

User · Answer

If you need to do this once just follow Charles  descriptions  but it is also possible to do this with Excel formulas and helper columns in case you want to make the filter dynamic   Lets assume you data is on the sheet DataSheet and starts in row 2 of the following columns    A  lastname B  firstname C  phonenumber   You need two helper columns on this sheet    D2   if A2       1  0   this is the filter column  corresponding to your where condition E2   if D2  lt  gt  1      sumifs D 2 D 1048576  A 2 A 1048576    lt   amp A2    sumifs D 2 D2  A 2 A2  A2    this corresponds to the order by   Copy down these formulas as far as your data goes   On the sheet which should display your result create the following columns    A  A sequence of numbers starting with 1 in row 2  this limits the total number of rows you can get  kind like a limit in sequel  B2   match A2  DataSheet  E 2  E 1048576  0   this is the row of the corresponding data C2   iferror index DataSheet A 2 A 1048576   B2        this is the actual data or empty if no data exists   Copy down the formulas in B2 and C2 and copy-past column C to D and E

User · Answer

tl dr  Excel does all of this natively - use filters and or tables    http   office microsoft com en-gb excel-help filter-data-in-an-excel-table-HA102840028 aspx   You can open excel programatically through an oledb connection and execute SQL on the tables within the worksheet   But you can do everything you are asking to do with no formulas just filters    click anywhere within the data you are looking at go to data on the ribbon bar select  Filter  its about the middle and looks like a funnel  you will have arrows on the tight hand side of each cell in the  the first row of your table now  click the arrow on phone number and de-select blanks  last option  click the arrow on last name and select a-z ordering  top option    have a play around   some things to note    you can select the filtered rows and pasty them somewhere else in the status bar on the left you will see how many rows meet you filter criteria out of the total number of rows   e g  308 of 313 records found  you can filter by color in excel 2010 on wards Sometimes i create calculated columns that give statuses or cleaned versions of data you can then filter or sort by theses too   e g  like the formulae in the other answers    DO it with filters unless you are going to do it a lot or you want to automate importing data somewhere or something   but for completeness   A c  option    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 handy place to start is to take a look at the schema as there may be more there than you think   List lt String gt  excelSheets   new List lt string gt         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 query a sheet    OleDbDataAdapter da   new OleDbDataAdapter  select   from      sheet        ExcelFile    dt   new DataTable      da Fill dt     NOTE - Use Tables in excel    Excel has  tables  functionality that make data behave more like a table   this gives you some great benefits but is not going to let you do every type of query    http   office microsoft com en-gb excel-help overview-of-excel-tables-HA010048546 aspx  For tabular data in excel this is my default   first thing i do is click into the data then select  format as table  from the home section on the ribbon  this gives you filtering  and sorting by default and allows you to access the table and fields by name  e g  table fieldname    this also allows aggregate functions on columns e g  max and average

User · Answer

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

User · Answer

I suggest you to have a look at the MySQL csv storage engine which essentially allows you to load any csv file  easily created from excel  into the database  once you have that  you can use any SQL command you want   It s worth to have a look at it

[sql] How to run a SQL query on an Excel table?

Examples related to sql

Examples related to excel

Examples related to filtering