Query using SQL statements in Excel

Source: Internet
Author: User
Tags dsn vlookup function

    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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.