快乐炸金花下载手机版下载

書名： Data Analysis and Business Modeling with Excel 2013
作者名： David Rojas
本章字數： 1243字
更新時間： 2021-07-09 21:35:22

Reading from MSSQL using SQL

This section will briefly show you how to use the SQL syntax to query a Microsoft SQL Server database. In the previous two sections, we imported all the tables into Excel and this was done easily. The issue is that we may only want a slice of the data that is found in a table. We may also have a table that has 5 million rows and imports large amounts of data that can cause Excel to crash. Even if we are able to import large amounts of data, unless you really need all that information, you will spend a lot of time to remove the excess data and waste time. A better idea is to edit any existing connections that Excel has with the database via SQL queries. This is exactly what we will learn in the Reading a table from MSSQL – the Microsoft SQL Server database section.

What exactly are SQL queries? SQL is just a language that databases understand. It is a language used to give instructions to a database. Using this language, you can tell a database to grab the users table, but only the records where the firstname column is equal to Mike. So, instead of grabbing all of the records found in the users table, you can only grab records where the first name is Mike and life is good. Learning SQL is outside the scope of this book, but we will briefly introduce you to the language. It is important for you as a data analyst to be aware of SQL and see how you can use it with Excel. In practice, most of the data-gathering steps will most likely be performed with database-specific tools, such as Microsoft SQL Server Management Studio. After the data to be analyzed is gathered, it will then be moved to Excel for further analysis and reporting.

If you are still confused about SQL and what this actually means, do not worry as this is normal. The following steps will walk you through an example that will clear up some of the questions you are currently wrestling with:

We will create a data connection that is similar to the Reading a table from MSSQL – the Microsoft SQL Server database section. Select the DATA tab, click on the From Other Sources button, and select the From SQL Server option, as shown in Figure 2.1 of the Reading a table from MSSQL – the Microsoft SQL Server database section.
Type the name of your server and click on the Next button, as shown in Figure 2.3 of the Reading a table from MSSQL – the Microsoft SQL Server database section. If your server requires a username and password, you need to type these in the Log on credentials section. However, 99 percent of the time, you will keep the default option of Use Windows Authentication selected, and then click on the Next button. Use Windows Authentication means that you do not have to provide any usernames or passwords and Windows will take care of granting you access. Refer to Figure 2.3 of the Reading a table from MSSQL – the Microsoft SQL Server database section.
The next step in the Data Connection Wizard dialog box is to select the database, which contains the data that we want to import. In this section, our goal is to import the table named users and this table is located in the database called BizIntel. So, we proceed by selecting BizIntel in the drop-down menu, highlighting the users table under the Name column, and clicking on the Finish button, as shown in Figure 2.8.
After you click on the Finish button, you will get a new dialog box asking you where you want to place the data that we are importing from the users table. We will keep the default location of cell A1 and click on the OK button, as shown in Figure 2.6 of the Reading a table from MSSQL – the Microsoft SQL Server database section.
In the Reading a table from MSSQL section of this chapter, you will now see that your data in Sheet 1 has four records. For reference, you can find the data references in this section in a file named users.csv in the Chapter 02 folder. In the following steps, we are going to edit the data connection so that it only returns records where the firstname column is equal to Mike. The following screenshot shows you a snapshot of the data:

Figure 2.11
Select the DATA tab and select any cell in the table that we just imported in order to activate it. Click on the Properties button, as shown in the following screenshot. If you do not select a cell in the table, the Properties button will not be activated and you will not be able to click on the button.

Figure 2.12
When the External Data Properties dialog box appears, click on the Properties button in the Connection section.

Figure 2.13
Select the Definition tab. This is where we are going to edit our data connection and enter our SQL query. But before we do this, let's take a closer look at the connection string in the following screenshot. There is a lot of information here, but I only want to point out a few key parameters. They are as follows:
- Data source: The data source is the name of your server. It is the name of the server that we connect to and we discussed this in the Reading a table from MSSQL – the Microsoft SQL Server database section.
- Catalog: The parameter named catalog refers to the name of the database. In our specific example, the catalog is set to BizIntel.
Another section worth mentioning is the Connection file section. Every time you create a data connection, Excel will save a file on your computer with all of the connection details. This means that the next time you open Excel, you will be able to use any of the data connections you created in the past. If you ever need to delete a connection, just click on the Browse... button and delete the connection of your choice. It will have a file extension of .odc and a filename that matches the Connection name.

Figure 2.14
Change the Command type from Table to SQL, as shown in the following screenshot:

Figure 2.15
Clear any text in the Command text section and type the following SQL statement:
```
SELECT * FROM users
WHERE firstname = 'Mike'
```
The quotes around the word Mike can be single or double quotes, as shown in the following screenshot:

Figure 2.16
Now, click on the OK button, and your data in Sheet 1 will get updated. If you get a dialog box similar to the one in the following screenshot, just click on the Yes button to continue.

Figure 2.17

To finish applying the updates, click on the OK button on the External Data Properties dialog box, as shown in the following screenshot:

Figure 2.18

You will now only see two records and these are the ones with Mike as their first name. As you learn more about the SQL language, you can create more sophisticated queries. This concludes the Reading from MSSQL using SQL section and the chapter as well. Good job in blazing through the material. I guarantee you that this is valuable information that will serve you well in your data adventures.

官术网_书友最值得收藏!

Data Analysis and Business Modeling with Excel 2013

Reading from MSSQL using SQL