Hanze

Go back to the main page
Go back to the Excel overview page

Excel Data Import

Solutions

Exercise 1

Exercise 2

Find the solution in the link below.

Heart Disease Health Indicators Dataset

Exercise 3

Find the solution in the link below.

Properties of ATC accepted medicines

As you can see from this file, decimal separator is a dot.

import

Make sure that you properly import the file with the correct decimal separator selected. You can do this by setting the Regional Setting correctly to English (United States).

Exercise 4

During the import, the column headers where not placed as such. See figure below:

import

To fix this, click Transform Data and then Use First Row as Headers.

import

The column headers are now loaded well:

import

The solution can be found here

Exercise 5

Note that this file uses the dot as a decimal separator. In addition, some cells contain null values (something that we will deal with later on).

import

The solution can be found here.

Exercise 6

As you probably noted, there are no column headers in covid_cds. You can add these manually after the import. You can find information about the column headers at Kaggle: SARS-CoV-2 Genetics

See a screenshot below:

screen shot

You can rename the column headers to: Location, Protein Name, Protein Sequence etc.

As you might have observed, this is a very large dataset (one of the csv files is almost 1 GB in size). Excel has a lot of trouble in rendering the data in real-time. You are probably way better off doing analysis of such large datasets in R or Python. The solution is not included as the file size is to large to include on the website.

Exercise 7

Use PowerQuery to load the csv files.
Data tab > From Text/CSV.
Here you can select the delimiter (tab, comma, custom). Click Transform Data for more import options.
You can click remove Rows to remove top rows.
Replace Values can be used to replace text to indicate missing values.
You can manually enter Column names.
You can click Use First Rows as Headers to use the first row as a header.
Decimal separator can be changed by changing the regional settings under File > Options and Settings > Query Options > Regional Settings.

Go back to the main page
Go back to the Excel overview page
⬆️ Back to Top

This web page is distributed under the terms of the Creative Commons Attribution License which permits unrestricted use, distribution, and reproduction in any medium, provided the original author and source are credited. Creative Commons License: CC BY-SA 4.0.