- Data Analysis and Business Modeling with Excel 2013
- David Rojas
- 757字
- 2021-07-09 21:35:22
Reading a table from MSSQL – the Microsoft SQL Server database
The data that we will import comes from a company called Fast forms. This company specializes in processing rental applications. Landlords or owners of homes sign up with Fast forms, and people interested in renting their homes can apply via the Fast forms' website. We will import the following table:
Users: This table holds information about all of the home owners who want to rent their homes. Data, such as their name, address, phone number, and registration date, will be found in the table.
- Fire up Excel 2013 and create a new spreadsheet. Select the DATA tab, click on the From Other Sources button, and select the From SQL Server option, as shown in the following figure:
Figure 2.1
You will now see the Data Connection Wizard dialog box appear on the screen. Now, to connect to a SQL server database, you either need to have a local database available, or you need to make an attempt to connect to your clients'/companies' internal database. You may not have access to any of these options and that is fine. By following the given examples, you will be able to adapt to the examples presented in this chapter and read from databases in future. The data used in this chapter will be available in the
Chapter 02folder for your reference.
Figure 2.2
- Type the name of your server and click on the Next button, as shown in the following screenshot. If your server requires a username and password, then you need to type these in the Log on credentials section, but 99 percent of the time, people tend to keep the default option of Use Windows Authentication selected, and 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.
Figure 2.3
Where do I find the server name in a Microsoft SQL Server database?
In the following screenshot, you will find a dialog box that contains the name of the server. This dialog box is not found in Excel, but it is found in a different program called Microsoft SQL Server Management Studio. This program allows you to manage and query SQL Server databases. If you do not have access to this program, you will have to ask your client or coworker for the name of the server. Providing more information on Microsoft SQL Server Management Studio is outside the scope of this book, so we've not covered it here.
Figure 2.4
- The next step in the Data Connection Wizard dialog box is to select the database, which contains the data that we want to import. If you are not familiar with the term database, do not be discouraged. A database is a collection of tables, functions, and other database objects. 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 Column Name, and clicking on the Finish button, as shown in the following screenshot:
Figure 2.5
I am using a personal database, and you would most likely not have a database named BizIntel or a table named users. But you are now familiar with database terms, such as table and database, so you will be able to use these steps as a guide to your own data.
- After you click on the Finish button, you will get a new dialog box asking you where you want to place the data we are importing from the users table. We will keep the default location of cell A1 and click on the OK button.
Figure 2.6
Congratulations! You have just queried a Microsoft SQL Server database and placed the results in an Excel spreadsheet. The steps that you just performed might not seem that interesting, but these steps have just given you the power to gather an unlimited amount of data. Think about it; earlier the data was stuck in a database and you couldn't do anything with the data. Now that you have learned how to get the data out of a database and into a familiar tool, such as Excel, you no longer have to be afraid of databases. You will now see that databases are wonderful sources of valuable data to consume and analyze.
- UI設計基礎培訓教程
- DBA攻堅指南：左手Oracle，右手MySQL
- C#完全自學教程
- Web Development with Django Cookbook
- 零基礎學MQL：基于EA的自動化交易編程
- Java Web應用開發技術與案例教程（第2版）
- Python機器學習實戰
- 零基礎入門學習Python
- Mastering Apache Spark 2.x（Second Edition）
- Visual FoxPro程序設計
- Python Data Analysis Cookbook
- Java網絡編程核心技術詳解（視頻微課版）
- The Professional ScrumMaster’s Handbook
- Node學習指南（第2版）
- Android應用開發深入學習實錄