Import Data from Text Files

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

1. Open the workbook the project provides (or create a blank workbook if the task says to start fresh).
2. Select the Data tab.
3. In the Get & Transform Data group, click From Text/CSV.
4. 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.
5. 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

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:

1. Data → From Text/CSV → select Products.txt → Import.
2. Set Delimiter to Tab and confirm headers appear as column names in the preview.
3. 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

1. Correct file name and extension.
2. Delimiter splits fields into the expected number of columns.
3. Header row is (or is not) used exactly as the task requires.
4. Sample values look like numbers, dates, and text in the right columns.
5. Destination matches "new sheet" vs "existing sheet / cell" wording.