SQL query methods for Excel worksheets

The SQL Query method of "reprint" Excel worksheet Source: http://blog.csdn.net/zhanghongju/article/details/8428458

Recently in the unit to do business data analysis, found or Excel with the direct, screening, summation, classification and so is also a pleasure, but found that some functions of efficiency and SQL still have a large gap, or even a world of difference, so the composition of an article, provide SQL query in Excel using the way.

The worksheet that you query can be in the current workbook or in another workbook. For example, in the "site data. xlsx" Workbook shown in Figure 1, the Sheet1 table stores the site access information statistics and now needs to get a city from Sheet1 that has more than 500 views.

Figure 1 Access data stored in the Sheet1

You can run an SQL query in another table in the current workbook, or you can create a new workbook, select the Sheet2 table for the current workbook in this example, and then click the Existing Connection button in the Get External data module, and in the Open Existing Connection dialog box, click the Browse for More button in the Open Select Data Source dialog box to locate an Excel workbook file that stores source data-site data. xlsx,2.

Figure 2 Locating the workbook file that stores the source data

Click the Open button to open the Select Table dialog box shown in 3 and select the SHEET1 worksheet by checking the first row of data contains column headings check box.

Click the OK button to open the Import Data dialog box shown in 4, select table in the Select how to display the data in the workbook option, select Existing worksheet and specify the location as A1 cell. ".".

Figure 3 The Select Table dialog box

Figure 4 The Import Data dialog box

Click the Properties button to open the Connection Properties dialog box shown in 5, select SQL in the command Type drop-down list, enter the SQL query statement in the command text "select * from [sheet1$] where browse times >500", where "Sheet1" That is, the specified SHEET1 worksheet, when you reference an Excel worksheet in SQL, you need to add a "$" character after the name and enclose it in square brackets, "*" To remove all the fields from the worksheet, where clause is used to specify the filter criteria, that is, the number of views is greater than 500.

Figure 5 Connection Properties dialog box

Click the OK button to return to the Import Data dialog box and click OK again to see the query results, as shown in 6.

Figure 6 SQL query Results

SQL query methods for Excel worksheets