Importing, cleaning, joining, and separating data
When we import data from different sources, cell data may contain extra spaces or invisible characters. It may be necessary to remove any unwanted characters if issues arise when working with formulas or formatting in the workbook.
After importing and cleaning your dataset, data may need to be joined or separated. In this section, we will learn how to import, clean, join, and separate data.
Importing datasets
The best way to import data into Excel from other sources would be to use the Get Data command from the Data tab. Numerous features are available on this ribbon, such as establishing data queries and connections, accessing Power Query (to transform and query data), sorting, filters, outlines, and accessing various data tools.
Let’s import a .csv file into Excel:
- Open a new workbook in Excel 2021.
- Click Data | From Text/CSV. Note that the Get Data drop-down option offers many more features, such as combining queries, Power Query, and importing from a range of different sources:
Figure 9.44 – Importing data by going to Data | From Text/CSV
- Select the file you wish to upload. If you are following along, you will need to open
TextImport.txt.
- Click Open.
- Select the correct delimiter for the dataset, then click the Load button at the bottom of the screen. The delimiter has been correctly identified as Tab for this example and the data looks perfect in terms of the cell, row, and column data:
Figure 9.45 – The Delimiter type before it’s imported using the Load button
- Click Load at the bottom of the screen. Note that the Transform button is to the right of this, which is useful when we need to access the Power Query Editor.
- The dataset will be imported into the workbook as a data table. This means that the dataset has an applied style that allows you to add column/row data effortlessly since it automatically expands the selection (range) when new data is added and included in any other connections on the worksheet or workbook.
- If we look closely at the data, we will notice that some of the cell data looks out of line or indented. This is probably because the import has included some spaces or hidden characters upon being imported. Don’t forget to save your workbook before making any amendments:
Figure 9.46 – Workbook data import result
Now that you are aware of the Get Data import option, we will look at ways to clean data.
Cleaning data
After importing data into an Excel workbook, it is good practice to clean the data to get it ready for formatting or manipulation using all the tools Excel has available. Some of the reasons to clean data are as follows:
-
To remove spaces from the imported dataset. Even if you cannot see spaces, they could be lurking at the end or middle of the cell data (text, numbers) – such as a silent space. Some spaces can be seen with ease as the data won’t be aligned.
- When we import data, cell formatting may be lost or associated incorrectly with values or text within the worksheet. This formatting can have a huge effect on formulas and other elements of Excel data manipulation. It is also important to remove any existing formatting that was added by the import. Numbers could also be an issue if they have been pre-formatted with an apostrophe, therefore being reflected as text.
-
Blank cells are a huge issue, especially when you want to produce dashboards. It is important to remove any blank cells or include a fill (either text or highlighting) so that the cell contains something.
- To get rid of any duplicated data.
- Errors – remove any errors on the worksheet using conditional formatting.
- To separate data within a single cell. Often, when we import data, the various bits of data of one row could end up in one cell instead of being separated into cells.
These are just some of the issues we face when importing data. We can use the preceding list as a checklist of things to look at once data has been imported into Excel. Let’s learn a little more about some of the items on our checklist.
