This lesson will introduce you to how to connect to external data sources using get and transform. The challenge with external data sources is knowing what you need to connect to, and having the information and access necessary to create that connection. Excel has built in support for dozens of different data connections through the getting Transform tool. These options can be daunting, and Microsoft continues to add more. For this lesson, let's focus on some of the most common ones. Other Excel workbooks are very common connections for Excel users.
In this file, I have nearly the exact same data set as from our prior lesson. I've saved this file to my hard drive, and I'm going to access it as an external data source for this lesson. In a new workbook, I'll go to the Data tab, select new query, and then find from workbook This will bring up a file explorer, which I'll then use to find the source file. I'll click on the source file, and press the Import button. Once I do this, the navigator window will launch with details about the workbook that I've selected. Along the top, there's a search bar to assist in finding specific information for larger data sets.
Below that, you'll find an option for selecting multiple items. Next up is the list of the available data objects in my file. selecting any one of these such as sheet one will give you a preview of the data that's available. This data set is not structured as a table, so it will pull the entirety of sheet one, which includes any blank cells that would tag along for the ride. We can manage these data artifacts later in our query. To the bottom right, we'll find our commands.
There's a load drop down and edit button and a Cancel button. The load drop down includes up For immediately loading the data into a table in a new workbook, or the load two will bring up the load two for providing a specific location for loading our information. The Edit button recently renamed transform will launch the query editor for the query and the cancel will cancel the data import all together, I'm going to go to load two and I'm going to load this information as a connection only. Excel workbooks can serve nicely as small data stores. But more commonly, you're going to see comma separated or other delimiter defined text data sets for managing small sets of information. These can be imported like Excel workbooks, with a few different features.
To do so we'll go to the from text CSV command along the data ribbon to bring up the Import Wizard. Within the file browser, we can find the file we want to import and click the Import button. The CSV wizard will open the file with a different type of import editor. The editor will include a quick preview of the data. Here, I can choose the file origin, the delimiter to split with and the rules for data type identification. The same three buttons with the same options are available to the bottom right, with the same changes for Excel 2019.
As with the prior data source, I'm going to select low too and turn this into a data connection. The next common data source, particularly among small businesses, is Microsoft Access, so we'll use it as our next external connection. Again, we're going to start from the data ribbon and click on our get data and choose from database from access. Our file browser will appear and ask us to navigate to the appropriate Microsoft Access database file location. Once we found it, we'll select the file and we'll choose Import. This will take us to a navigator that is effectively identical to the one we used with the Excel workbook.
We have our search bar the select multiple comment In the list of available data options. In this example, we have multiple data sources, which are all copies of each other. I can preview any data set by selecting it from the list. I should note that we did not encounter any permissions options in setting up this connection. One noticeable issue that I found with getting transform is that some legacy types of access protection are not supported by the connector. You can work around some of these by using older style data connections with some connection string manipulation, linking them into a table, and finally loading those tables into a query as if they weren't local data sources.
At this point for our access file, I'm going to go to load two and choose to load this into a connection only. The final data source type that we'll cover in this lesson will be a SQL database connection. In order to connect to SQL Server, you need some details in advance. First, you need either the server's IP address or the local DNS name. Second, you'll need to be given read permission and SQL Server Settings with your windows credentials, or you'll need to have a database specific set of login credentials. Once you have that information, you can connect by going to get data databases SQL Server database.
The database that I'll connect to is my local host SQL Express server. In order for you to connect to SQL, you'll either need to set up a local SQL server or connect to an alternative server such as your company's own database, given appropriate oversight and permissions from your IT staff. Once I've inputted the server, I can press OK. And the first time I do this, I'll be asked for my credentials. At this stage, you'd specify either to use your local windows login or alternative login credentials. Selecting the alternative credentials will allow you to input your username and password as necessary. Once you're ready, you can press connect to initiate the connection.
If your SQL server does not have encryption support enabled, you will be Given a warning, if you work with sensitive data, you should consult with your IT department If this occurs. Once you press OK, it will take me to another navigator, which is quite similar to the ones we have for Excel and access. Within the navigator, we have a list of the available servers, a list of databases, and then a list of options within each database. Selecting a table or query from here, we'll generate a preview of the data. So I can see that we have another copy of basically the same data set as before. I'm going to choose load load to and create a connection only.
Once you're more familiar with the get and transform tool set, you can revisit this lesson for a primer on the external data sources and determine the best approach for data access with guidance from your database administrator. This gives us four different external data connections located within a single Excel workbook. You could take any one of these queries and load it to a table to use the information in your work because you see fit Give the basic tools necessary for creating tables listing information from a wide variety of data sources, and then using that data with Excel formulas to manipulate them as you need. This covers the basic get portion of the get and transform tool. The rest of our course will be dedicated to covering how to transform data, starting in our next chapter with some simple commands for renaming columns, replacing values, and filtering data.
After that, we'll move on to how to merge pivot and really start manipulating our data sets. To close out the chapter we have our first exercise, I've provided three different datasets. One is an Access database. One is a CSV file and one is an Excel workbook. You should create for table output from these files as per the rules provided within the exercise. There are multiple options and some of the sources that will require you to utilize the navigator appropriately.
Additionally, you should find The instructions for where to load the data to get comfortable using the load two commands. In the next video I'll provide a walkthrough for this exercise