Book Image

Data Analysis and Business Modeling with Excel 2013

Book Image

Data Analysis and Business Modeling with Excel 2013

Overview of this book

Table of Contents (18 chapters)
Data Analysis and Business Modeling with Excel 2013
About the Author
About the Reviewers
Creating Interactive Spreadsheets Using Tables and Slicers

Importing data from various sources

In this section, we are going to cover the sources the data is imported from. They are as follows:

  • Importing data from a text file

  • Importing a CSV file

  • Importing other Excel files

  • Importing data from the Web

Importing data from a text file

There are times when data comes in a text file, and to be honest, this can be a scary thing. I am saying this because data that comes in a text file can be formatted in many ways, and you never know what you are going to get. Luckily, most of the time you will get comma-separated data and this is something Excel can consume pretty easily. Let's start with creating our own text file and then importing it into Excel:

  1. Open Notepad or a similar text editor. Copy and paste the following chunk of data into the text file, as shown in the following screenshot:






    Then, save the file as data.txt on your desktop.


    Downloading the example code

    You can download the example code files from your account at for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit and register to have the files e-mailed directly to you.

  2. Now, open Excel and create a new workbook. Go to the DATA tab and click on the From Text button, as shown in the following screenshot:

    Navigate to your data.txt file and click on the Import button, as shown in the following screenshot:

  3. You will now see a dialog box, as shown in the following screenshot. This dialog box will ask you how your data has been formatted. By default, you will have the Delimited option selected. This means that your data is separated by some characters such as spaces, commas, and semicolons. In our example, the values in the data.txt file are separated by commas. There are other options, but 99 percent of the time, you can just click on the Next button.

  4. Step two of the import wizard will now ask you to select the delimiter or the character that separates each of the values you are trying to import. Make sure that you click on the Comma delimiter and remove any other options that may have been checked automatically.

    Now, let's take a look at the Data preview area in the following screenshot. This area will show you a few records of how Excel plans to parse the data. As shown in the following screenshot, we can see that by choosing the Comma delimiter, Excel correctly splits the data into two columns. We can now click on the Finish button.

  5. The last dialog box will ask you to select where you want to paste the data. The default value is A1 and this is usually the cell you would like to insert the data into. At this point, you also have the option to paste your data into a new worksheet by choosing this option in the dialog box.

    After you click on the OK button, you will see your data in columns A and B.


You can also drag the actual data.txt file into Excel and this will activate the Text Import Wizard.

Importing a CSV file

The acronym CSV means comma-separated values. What this means to us is that when we use the Text Import Wizard, we need to select Comma as the delimiter. To import a CSV file, the steps are exactly the same as those in the Manually creating data section; however, the data.txt file is not a CSV file. A CSV file can be identified by its filename ending in .csv.

Importing other Excel files

If you perform the same steps that we just learned to import a text or a CSV file into an Excel workbook, then all that Excel does is open the file you are trying to import. It will not ask you for a delimiter, it will not open the Text Import Wizard, and it will simply open the Excel file. Let's go through a simple example to show you how to correctly merge multiple Excel files:

  1. Make two separate Excel files using the same techniques, as shown in the Manually creating data section. Split the dataset that we have been using, create one spreadsheet with the Revenue column, and create a second spreadsheet with the Name column.

    File one should be called one.xlsx and the file will look like this:

    File two should be called two.xlsx and will look similar to the following screenshot:

    Now, let's pause and see what we are trying to do here with the two files. The goal is to combine them into one file. There are two methods that we can use, so let's start with the easiest one.

    In the first method, open the one.xlsx and two.xlsx files. Using the two.xlsx file, highlight columns A1 through A5. Press Ctrl + C to copy the selected cells. Now, switch to the one.xlsx file and select column B1. Press Ctrl + V to paste the data. Your spreadsheet should now look like the following screenshot:

    Congratulations! At this point, you can save the file as final.xlsx and you are all done. You have combined two different Excel workbooks into one.

    The second method involves using an Excel feature that you will often use in different situations. Let's go through the following steps, and then, I will explain the benefits of using this technique:

    1. Open the one.xlsx and two.xlsx files. Using the one.xlsx file, right-click on the tab named Sheet 1, and select the Move or Copy... option, as shown here:

    The Move or Copy dialog box will appear. Select the workbook that you plan to move the data to. In this case, it is going to be 01 – Chapter – two.xls. Make sure that you have the second workbook open, or you will not be able to see this option in the drop-down menu. In the next section named Before sheet, select the option called (move to end), check the Create a copy checkbox, and click on the OK button, as shown here:

    You will now have your second spreadsheet with two tabs: one named Sheet 1 that holds your original data and another one named Sheet 1 (2) that holds the data we just imported from the first spreadsheet. From here on, we can just employ the first technique and combine both the datasets. Good job!

    What was so different about the second method? This method gives us options and that is the key. We currently have a spreadsheet that contains the raw data from each of the two workbooks. We can then create a third spreadsheet or a third tab that holds the data from the two datasets. If we make any mistakes, we can simply remake the third tab/spreadsheet, as our original data is still intact. We can also filter the data of our two original datasets before we combine any data. In practice, you will notice that you will be performing a unique combination of these two methods, depending on your dataset and the problem you are trying to solve.

Importing data from the Web

Here, we will learn how to grab data from the Web. This is the least common task that you will be asked to perform from the previous lessons but taking a look at how it is done is worth your time. We will go through a typical scenario in the following steps:

  1. Open Excel 2013 and create a new spreadsheet. The next steps will involve opening a web page in Excel and extracting data from a table found on the website. We will start with selecting the DATA tab and clicking on the From Web button, as shown in the following screenshot:

  2. A dialog box will appear. In the Address textbox, type and click on the Go button. After the page has been loaded, you will see a similar new web query dialog box, as shown in the following screenshot:

    You will notice that in several places, on the website, you can see a yellow square with a single back arrow, as shown in the following screenshot. This button tells you that Excel has found a table on the website. This button also tells you that you can grab the contents of the table and import them into your spreadsheet. What do we mean by saying that the website has a table on it? This is outside the scope of this book, as the answer requires you to know HTML. But for your reference, Excel looks for HTML <table> tags to identify tables on a website.

  3. Scroll down to the end of the web page and then click on the last button and Import button, as shown in the following screenshot. Why did we choose to select the last button and not the first one? In this example, there were two buttons to choose from. Sometimes, the button will be right next to the table that you are interested in, and at other times, you will have to complete the task by trial and error.

    Notice that the yellow button will change to a green checkbox. After you click on the Import button, you will get a new dialog box that will ask you where you want to paste the data, as shown in the following screenshot. The cell A1 is usually the default location selected, but you may change the location if you wish.

    At this point, Excel will grab the data from the website, and you will have a worksheet that looks similar to the following screenshot. You should have 50 records, but they do not have to match the ones in the following screenshot:

    Good job! You have just imported data from a website effortlessly, thanks to Excel's robust tools that helped you get the job done as easily and quickly as possible. The advantage of grabbing the data using the previous steps is that if there are any changes in the data on the website, we can easily update our spreadsheet to match any new changes. If we simply copy and paste the data from the website into Excel, we would have to perform these same steps every time the data changes. The Pandas Bootcamp website actually changes data every time you refresh the web browser. Try it!

    This means that if we ask Excel to refresh or to check whether the website has any new data, it will update our spreadsheet with the new data. Let's give it a try.

  4. Right-click on cell A1 or any other cell with data. Go to the menu bar, and click on the Refresh button, as shown in the following screenshot. Your data should have changed! This feature will allow your data to always be in sync with just a few clicks.


    Alternatively, we can perform this operation by hitting the Refresh All drop-down button under the DATA tab in Excel.