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