Tasks ~ Get External Data

This section contains practical tasks with step-by-step instructions. You should complete the tasks in order.

  • Start off with a new blank workbook.

Task 1 ~ Get data from a text file

In this task you will get data from a text file.

  • Select cell B2.

  • On the Ribbon, click the Data tab.

  • In the Get External Data group, click the From Text button.

The Import Text File dialog box is displayed.

  • Locate and select the text file FromText in your course folder.

  • Click Import.

The Text Import Wizard is started.

In Step 1, you can choose to split the text using a delimiter (a particular character), or using fixed width (a particular number of characters).

The Preview box shows the content of the data file.

  • Choose Delimited.

  • Click Next.

Step 2 of the wizard is displayed. You can choose the delimiter character to create the split. In this case, there are two; a comma and a semi-colon.

  • In the Delimiters area, click Tab so that it is NOT ticked.
  • In the Delimiters area, click Semicolon and Comma so that they are both ticked.

As you make changes, you can see the results in the Data preview area.

  • Click Next.

Step 3 of the wizard is displayed.

In step 3, you can adjust the individual columns that are being created. In this case, there is no need to change anything.

  • Click Finish.

The Import Data dialog box is displayed.

  • Click OK.

The data is imported into the Excel.

Task 2 ~ Get data from a Microsoft Access database

In this task you will get data from a Microsoft Access database.

  • Select cell B7.

  • On the Ribbon, click the Data tab.

  • In the Get External Data group, click the From Access button.

The Select Data Source dialog box is displayed – this is similar to a standard Open dialog box.

  • Locate and select the database file FromAccess in your course folder.
  • Click Open.

The Select Table dialog box is displayed.

In this database, there is one VIEW (a query) and several tables. Here you will select a table.

  • In the list, choose Region.
  • Click OK.

The Import Data dialog box is displayed.

In this case, you will import the data into a table.

  • Make sure that the Table option is chosen.
  • Click OK.

The data is imported into Excel and automatically formatted as an Excel table.

A connection to the data source will probably be established, but security settings on your computer could prevent this.

Task 3 ~ Get data from a web page

In this task you will get data from a web page. If you do not have access to the Internet, you should skip this task.

  • Select cell F2.

  • On the Ribbon, click the Data tab.

  • In the Get External Data group, click the From Web button.

Assuming that you have access to the internet, the New Web Query dialog box is displayed, and your default home page shown.

  • In the Address box, delete the current address, and then type:
  • www.pctrainingcourseware.co.uk/table2
  • Press Enter.

For each individual table that Excel finds on the web page, it will show a small black and yellow arrow icon.

  • Beside Year A, click the small black and yellow arrow icon.

  • Hold down the Ctrl key, and then beside Year B, click the small black and yellow arrow icon.

You should now have two tables selected.

  • Click Import.

The Import Data dialog box is displayed.

  • Click OK.

Excel will attempt to download the data from the web page. A connection to the data source will probably be established, but security settings on your computer could prevent this.

Task 4 ~ Refresh data connections

In this task you will refresh data connections.

At the moment, you should have two or three sets of imported data in your worksheet. You will refresh all the data connections. Refreshing the link to the text file requires selecting the file again. In this task, you will select a different text file which has already been updated.

Before starting the refresh process, note the data in cells B2 to D5. It contains four rows with numbers 1, 2, 3 and 5. The updated file has a row for number 4 in, so if the refresh works, you should see that row.

  • On the Ribbon, click the Data tab.

  • In the Connections group, click the Refresh All button – click the main part of the button and not the arrow on the button.

The Import Text File dialog box is displayed so that you can choose a text file.

  • Choose the FromTextUpdated file, and then click Import or press Enter.

The data is updated accordingly. Data from the Access and Web connections hasn’t changed, but you should see the new row for the text data.

Task 5 ~ Remove data connections

In this task you will remove all data connections.

You will remove all three data connections – or just two data connections if you were unable to connect to the internet.

  • On the Ribbon, click the Data tab.

  • In the Connections group, click the Connections button.

The Workbook Connections dialog box is displayed.

  • In the list of connections, choose all the connections – hold down the Ctrl key while selecting each one.

  • Click the Remove button.

You are asked to confirm the removal.

  • Click OK.

Excel will close the connections.

  • Close the Workbook Connections dialog box.

The data that you imported is no longer connected to its source files.

Finishing off

You have now completed the tasks in this section.

  • Close the workbook – there is no need to save it.