Import Data from Text Files

Microsoft Excel Microsoft 365 Apps

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the MO-210 Exam Performance-Based Exam Strategy

2 Import Data & Navigate Workbooks

Import Data from Text Files Import Data from Online Sources Search and Navigate to Named Cells, Ranges, Elements Insert and Remove Hyperlinks

3 Format Worksheets, Views & Collaboration

Page Setup, Headers, Footers, Row Height & Column Width Quick Access Toolbar, Views, Freeze Panes, Properties, Display Formulas Print Area, Export Formats, Print Settings, Inspect, Comments & Notes

4 Manipulate Data in Worksheets

Paste Special and Auto Fill Insert and Delete Rows, Columns, and Cells Generate Numeric Data with RANDBETWEEN and SEQUENCE

5 Format Cells and Ranges

Merge, Alignment, Orientation, Indentation, Wrap Text Number Formats, Format Painter, Format Cells Dialog Cell Styles, Clear Formatting, Group Worksheets

6 Named Ranges & Visual Summaries

Define and Reference Named Ranges Insert and Manage Sparklines Apply and Remove Conditional Formatting

7 Create and Format Tables

Create Excel Tables from Cell Ranges Apply Table Styles and Convert Tables to Ranges Add or Remove Table Rows and Columns

8 Table Options, Filter & Sort

Configure Table Style Options Insert and Configure Total Rows Filter Table Records Sort Data by Multiple Columns

9 Formula References

Relative, Absolute, and Mixed References Structured References in Tables

10 Calculate and Transform Data

SUM, AVERAGE, MIN, and MAX COUNT, COUNTA, and COUNTBLANK Conditional Operations with IF SORT and UNIQUE Functions

11 Format and Modify Text

LEFT, RIGHT, and MID UPPER, LOWER, LEN, CONCAT, and TEXTJOIN

12 Create Charts

Create Charts from Data Create Chart Sheets

13 Modify and Format Charts

Add Data Series and Switch Rows/Columns Add and Modify Chart Elements Chart Layouts, Styles, and Alternative Text

Import Data from Text Files

Key Takeaways Explain This Topic AI

On the Data tab, Get & Transform Data → From Text/CSV opens the modern import path used on MO-210 for .txt and .csv files

Always verify the Delimiter and whether the first row is treated as headers in the preview before you choose Load

CSV means comma-separated values; .txt files may use tab, semicolon, pipe, space, or a custom delimiter

Load places data on a new sheet as a table by default; Load To lets you target an existing worksheet or create a connection only

Wrong delimiter or missing header detection is the most common import trap on performance tasks
Last updated: August 2026

Why text import matters on MO-210

Many MOS Excel Associate projects start with external data already prepared as a comma-separated values (CSV) file or a plain text (.txt) export from another system. The exam does not ask you to memorize Power Query M code. It does expect you to open the right connector, read the preview carefully, fix delimiter and header settings when Excel guesses wrong, and load clean columns onto a worksheet—often as a starting point for later formatting, tables, or formulas.

Task wording you will see includes phrases such as "Import the data from SalesExport.csv into a new worksheet" , "Import the tab-delimited text file" , or "Ensure the first row is used as column headers." Those instructions map directly to Data → Get & Transform Data → From Text/CSV and the preview dialog that appears after you select the file.

Ribbon path: From Text/CSV

Open the workbook the project provides (or create a blank workbook if the task says to start fresh).

Select the Data tab.

In the Get & Transform Data group, click From Text/CSV .

In the Import Data dialog, browse to the file named in the task (pay attention to folder path hints in the project instructions), select it, and click Import .

Review the preview dialog before loading.

That preview is where most points are won or lost. It is not the full Power Query Editor yet. It shows a sample of rows, the inferred File Origin , the Delimiter , and how columns will split. You can change those controls and watch the preview update immediately.

Delimiter choices

File type / clue Typical delimiter What wrong choice looks like
.csv Comma All values jammed into one column, or extra empty columns
Tab-delimited .txt Tab Same "one giant column" symptom if left on Comma
European-style exports Semicolon Commas inside numbers stay inside cells; columns do not split
Pipe-separated logs Custom: | Must pick Custom and type the character
Fixed-width (rare at Associate) Fixed width Columns align by character position, not a separator

CSV literally means values separated by commas, but MO-210 still expects you to confirm the delimiter in the preview. Autodetect is usually right for clean CSV files and wrong more often for .txt files that use tabs or semicolons. If every field sits in column A with no splits, change the delimiter first—do not start manually Text to Columns unless the task forces an older method.

Headers and data types

The preview also decides whether the first row becomes column names. If the task says the file includes headers, leave or enable header detection so OrderID , Region , and Amount become field names—not row 1 of data. If the file has no header row and Excel treats the first data values as headers, turn that off or the real first record disappears into the header bar.

Power Query also detects data types (Whole Number, Decimal Number, Text, Date, and so on) from a sample of rows. At Associate level you mainly need to:

Notice when an ID column that should stay text (leading zeros) was coerced to a number.

Notice when dates imported as text and will not sort chronologically.

Use Transform Data only when the task explicitly requires cleaning before load; otherwise Load or Load To is enough.

Stay inside Associate scope: you do not need custom M formulas, merge queries, or advanced transforms that belong on Excel Expert (MO-211).

Load vs Load To vs Transform Data

After the preview looks correct:

Load — Creates a new worksheet and places the data in an Excel table , usually named after the source file. This matches tasks that say "import into a new worksheet" without naming a destination cell.

Load To… — Opens options to load as a table, PivotTable, or connection only, and to choose an Existing worksheet plus a starting cell. Use this when the task says to place imported data starting at a specific cell on a named sheet.

Transform Data — Opens the Power Query Editor for filters, column splits, type changes, and other cleanup. Use it when the project tells you to remove columns, filter rows, or change types before the data lands in the grid.

Exam scenario walkthrough

Descriptor: "Import Products.txt from the exam files folder. The file is tab-delimited and the first row contains headers. Place the data on Sheet1 starting at cell A1."

Performance steps:

Data → From Text/CSV → select Products.txt → Import.

Set Delimiter to Tab and confirm headers appear as column names in the preview.

Open the Load drop-down → Load To… → Table → Existing worksheet → select Sheet1!$A$1 → OK.

If you leave Delimiter on Comma, the sheet shows one column of tab-joined text and later sorting/filtering tasks fail even if you "imported" something.

Common traps

Wrong file — Projects often include both .csv and .xlsx helpers; import only the file named in the task.

Opening the file by double-click — That may open CSV in Excel without creating a refreshable query connection; prefer From Text/CSV when the objective is import via Get Data.

Skipping the preview — Clicking Load immediately trusts autodetection; always glance at column splits and header names.

Confusing Paste with Import — Copy-paste creates static values with no query connection; use Get Data when the skill is import.

Editing source after load — If the task later says refresh, the connection must point at the same file path; renaming or moving the source breaks refresh.

Quick checklist before you click Load

Correct file name and extension.

Delimiter splits fields into the expected number of columns.

Header row is (or is not) used exactly as the task requires.

Sample values look like numbers, dates, and text in the right columns.

Destination matches "new sheet" vs "existing sheet / cell" wording.