Query using SQL statements in Excel

1. Connect Excel to the Oracle database

The data-from other sources drop-down menu in the Excel Palette has the option to connect to a different database from the Data Connection Wizard and from Microsoft Query.

1. Configuration of Oracle Data sources

In Excel, you need to configure Data source ODBC regardless of which way you implement access to that database.

Step 1: Open Microsoft ODBC Administrator, click the "Add" button on the option "User DSN"

Figure 11

Step 2: Select the driver for the Oracle and click "Finish"

Figure 12

Step 3: If set, click "Test Connection" for connection test

Figure 13

Step 4: Press the settings, click "OK" to test, test the connection is successful.

Figure 14

1. Making connections from the Data Connection Wizard

Step 1: Click "From Data Connection Wizard", the following pop-up window will appear, select "ODBC DSN" and click "Next"

Figure 15

Step 2: Select "Oracle" in the pop-up box below and click "Next"

Figure 16

Step 3: Enter the appropriate Oracle server name, username and password in the popup window, and click the OK button;

Figure 17

Step 4: Successful connection, the following window appears, select "Finish" to end the session or select a table click "Next" to import the table into Excel;

Figure 18

1. Make a connection from Microsoft Query

Step 1: Click "from Microsoft Query", the following window appears, select "Oracle*" and "OK"

Figure 19

Step 2: The following wizard appears, fill in the Oracle server name, user name, password, and click "OK"

Figure 110

Step 3: Complete the connection, which can then be done by working with the tables in Oracle.

1. Using SQL statements for exact queries in Excel

In the current work will encounter a variety of data processing tools, Oracle, MySQL, Hive, HBase, Excel, and so on, which before with more SQL, more familiar with SQL, the VLOOKUP function of Excel is not very skilled, I want to lazy to write SQL statements in Excel, so that the benefit of my lazy man ~

After the search and practice, completed the lazy work, but lazy also can not lazy to not summarize their learning results, otherwise learning will be the white school, so there is the following summary:

Warm tip : Listen to netizens said only Excel2007 and above version has this function,2003 version of either pass to learn, or to upgrade their own version. My office is in version.

problem: In Excel write in SQL , query table 1 the corresponding ID the table 2 the user name and age

User ID	Name	Age
10000	Zhang San	23
10001	Harry	27
10002	Liusi	19
10003	Zhuge	26
10004	Wang Jing	42
10005	Liu San	53
10006	Zhao four	42
10007	Qi Qin	34
10008	Xuhui	21
10009	Zhang Jia	26
10010	Liu	31
10011	Kuje	33
10012	John doe	12

Table 2-1

Span style= "FONT-SIZE:10PT;" > id	occupation
10000	engineer
10004	doctor
10005	Barber
10010	consultant
10008	teacher
10012	Retired

Table 2-2

1. Step 1, Name the table: Select Table 1, right-click on the menu "define name", in the "name" column named "Table 1"; Similarly, select Table 2, right-click on the menu "define name", in the "name" column named "Table 2"; 0-3.

Figure 21

1. Step 2 Select "from other sources" in the Data menu 0-4, drop-down, select "from Microsoft Query" in the dropdown, choose the "Excel file" database, and confirm that 0-5:

Figure 22

Figure 23

1. Step 3 Select the file address and file name (for the database name) of the Excel file from the directory, 0-6; after "OK", "add" Table 1 and Table 2, 0-7:

Figure 24

1. Step 4 after adding "Table 1" and "Table 2", close the window; Click the SQL button, 0-8, write the SQL statement in the SQL window displayed, 0-9;

Figure 25

Figure 26

1. Step 5 Return the results to the Excel window: Click "File"-"Return data to Microso Excel (R)", save the data in the cell, 0-10.

Figure 27

Query using SQL statements in Excel