From external source to a spreadsheet

When you work with spreadsheets, there are a few different ways to import data. This reading covers how you can import data from external sources, specifically:

• Other spreadsheets
• CSV files
• HTML tables (in web pages)

Importing data from other spreadsheets

In a lot of cases, you might have an existing spreadsheet open and need to add additional data from another spreadsheet.

Google Sheets

In Google Sheets, you can use the IMPORTRANGE function. It enables you to specify a range of cells in the other spreadsheet to duplicate in the spreadsheet you are working in. You must allow access to the spreadsheet containing the data the first time you import the data.

The URL shown below is for syntax purposes only. Don't enter it in your own spreadsheet. Replace it with a URL to a spreadsheet you have created so you can control access to it by clicking the Allow access button.

Refer to the Google Help Center's IMPORTRANGE page for more information about the syntax. There is also an example of its use later in the program in Advanced functions for speedy data cleaning.

Microsoft Excel

To import data from another spreadsheet, do the following:

Step 1: Select Data from the main menu.

Step 2: Click Get Data, and then select From File within the toolbar. In the drop down, choose From Excel Workbook

Step 3: Browse for and select the spreadsheet file and then click Import.

Step 4: In the Navigator, select which worksheet to import.

Step 5: Click Load to import all the data in the worksheet; or click Transform Data to open the Power Query Editor to adjust the columns and rows of data you want to import.

Step 6: If you clicked Transform Data, click Close & Load and then select one of the two options:

• Close & Load - import the data to a new worksheet
• Close & Load to... - import the data to an existing worksheet

If these directions do not work for the version of Excel that you have. Visit this free online training center, Microsoft Excel for Windows Training, you will find everything you need to know, all in one place.

If you are using Numbers, search the Numbers User Guide for directions.

Importing data from CSV files

Google Sheets

Step 1: Open the File menu in your spreadsheet and select Import to open the Import file window.

Step 2: Select Upload and then select the CSV file you want to import.

Step 3: From here, you will have a few options. For Import location, you can choose to replace the current spreadsheet, create a new spreadsheet, insert the CSV data as a new sheet, add the data to the current spreadsheet, or replace the data in a specific cell. The data will be inserted as plain text only if you uncheck the Convert text to numbers, dates, and formulas checkbox, which is the default setting. Sometimes a CSV file uses a separator like a semi-colon or even a blank space instead of a comma. For Separator type, you can select Tab or Comma, or select Custom to enter another character that is being used as the separator.

Step 4: Select Import data. The data in the CSV file will be loaded into your sheet, and you can begin using it!

Note: You can also use the IMPORTDATA function in a spreadsheet cell to import data using the URL to a CSV file. Refer to Google Help Center's IMPORTDATA page for more information and the syntax.

Microsoft Excel

Step 1: Open a new or existing spreadsheet

Step 2: Click Data in the main menu and select the From Text/CSV option.

Step 3: Browse for and select the CSV file and then click Import.

Step 4: From here, you will have a few options. You can change the delimiter from a comma to another character such as a semicolon. You can also turn automatic data type detection on or off. And, finally, you can transform your data by clicking Transform Data to open the Power Query Editor.

Step 5: In most cases, accept the default settings in the previous step and click Load to load the data in the CSV file to the spreadsheet. The data in the CSV file will be loaded into the spreadsheet, and you can begin working with the data.

If these directions do not work for the version of Excel that you have. Visit this free online training center, Microsoft Excel for Windows Training, you will find everything you need to know, all in one place.

If you are using Numbers, search the Numbers User Guide for directions.

Importing HTML tables from web pages

Importing HTML tables is a very basic method to extract or "scrape" data from public web pages. Web scraping made easy introduces how to do this with Google Sheets or Microsoft Excel.

Google Sheets

In Google Sheets, you can use the IMPORTHTML function. It enables you to import the data from an HTML table (or list) on a web page.

Refer to the Google Help Center's IMPORTHTML page for more information about the syntax. If you are importing a list, replace "table" with "list" in the above example. The number 4 is the index that refers to the order of the tables on a web page. It is like a pointer indicating which table on the page you want to import the data from.

You can try this yourself! In blank worksheets, copy and paste each of the following IMPORTHTML functions into cell A1 and watch what happens. You will actually be importing the data from four different HTML tables in a Wikipedia article: Demographics of India. You can compare your imported data with the tables in the article.

• =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India";"table";1)
• =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India";"table";2)
• =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India";"table";3)
• =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India";"table";4)

Microsoft Excel

You can import data from web pages using the From Web option:

Step 1: Open a new or existing spreadsheet.

Step 2: Click Data in the main menu and select the From Web option.

Step 3: Enter the URL and click OK.

Step 4: In the Navigator, select which table to import.

Step 5: Click Load to load the data from the table into your spreadsheet.

If these directions do not work for the version of Excel that you have. Visit this free online training center, Microsoft Excel for Windows Training, you will find everything you need to know, all in one place.

If you are using Numbers, search the Numbers User Guide for directions.

Exploring public datasets

Open data helps create a lot of public datasets that you can access to make data-driven decisions. Here are some resources you can use to start searching for public datasets on your own:

• The Google Cloud Public Datasets allow data analysts access to high-demand public datasets, and make it easy to uncover insights in the cloud.
• The Dataset Search can help you find available datasets online with keyword searches.
• Kaggle has an Open Data search function that can help you find datasets to practice with.
• Finally, BigQuery hosts 150+ public datasets you can access and use.

Public health datasets

1. Global Health Observatory data: You can search for datasets from this page or explore featured data collections from the World Health Organization.
2. The Cancer Imaging Archive (TCIA) dataset: Just like the earlier dataset, this data is hosted by the Google Cloud Public Datasets and can be uploaded to BigQuery.
3. 1000 Genomes: This is another dataset from the Google Cloud Public resources that can be uploaded to BigQuery.

Public climate datasets

1. National Climatic Data Center: The NCDC Quick Links page has a selection of datasets you can explore.
2. NOAA Public Dataset Gallery: The NOAA Public Dataset Gallery contains a searchable collection of public datasets.

Public social-political datasets

1. UNICEF State of the World’s Children: This dataset from UNICEF includes a collection of tables that can be downloaded.
2. CPS Labor Force Statistics: This page contains links to several available datasets that you can explore.
3. The Stanford Open Policing Project: This dataset can be downloaded as a .CSV file for your own use.

Test your knowledge on accessing data sources

Question 1

A CSV file saves data in a table format. What does CSV stand for?

A. Cell-structured variables

B. Calculated spreadsheet values

C. Comma-separated values

D. Compatible scientific variables

Explain: CSV stands for comma-separated values.

Question 2

A data analyst wants to bring data from a CSV file into a spreadsheet. This is an example of what process?

A. Importing data

B. Filing data

C. Normalizing data

D. Editing data

The correct answer is A. Importing data. Explain: A data analyst bringing data from a CSV file into a spreadsheet is an example of importing data.

Question 3

A CSV file makes it easier for data analysts to complete which tasks? Select all that apply.

• Distinguish values from one another
• Examine a small subset of a large dataset
• Manage multiple tabs within a worksheet
• Import data to a new spreadsheet

Explain: A CSV file makes it easier for data analysts to examine a small part of a large dataset, import data to a new spreadsheet, and distinguish values from one another.