Try the example, then check the result.
Download the fictional material and work through the exact steps below. App screens in the video are English illustrative diagrams, not a recording of the product interface. Controls can vary by version.
Use the answer check before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
Synthetic narration · English illustrative diagrams · English captions. Product menus may differ in your account.
Target result: three fictional orders retain ZIP 02108, customer code 000123, and three distinct 17-digit references. AmountUSD remains numeric and totals $159.
Download the practice CSV, expected Excel workbook, and plain-text answer check. The data is fictional. Any UI diagrams accompanying this guide are illustrative reconstructions, not screen recordings.
Why the problem happens
A CSV has rows of text separated by commas. It does not carry Excel number formats or column types. When Excel opens a CSV directly, it interprets values using its current import defaults. A ZIP code such as 02108 can become the number 2108; a reference longer than 15 significant digits can lose precision. Changing the display format afterward does not recover the original characters. Microsoft documents these conversion and precision limits.
| Order | CustomerCode | ZIP | Reference | AmountUSD |
|---|---|---|---|---|
| O-201 | 000123 | 02108 | 12345678901234567 | $48 |
| O-202 | 000124 | 10001 | 12345678901234568 | $75 |
| O-203 | 000125 | 94107 | 12345678901234569 | $36 |
Swipe or scroll sideways to see every table column →
These codes are identifiers. Their leading zeros and final digits matter. Only the amount column is a quantity to sum.
Before you start
- Use a fresh copy of the supplied CSV. Open it in a plain text editor to see the original characters before importing.
- The steps target current Excel desktop with Power Query. Web, Mac, older releases, and non-English menus may differ. Check the actual column previews in your version.
- If Excel already changed the codes in a workbook, return to the untouched CSV. You cannot infer lost digits from the altered number alone.
Import and set the column types
- In Excel desktop, start a blank workbook. Choose Data → Get Data → From File → From Text/CSV and select
orders-ids-practice.csv. Some ribbons show the shorter Data → From Text/CSV command. Check that the preview has five columns. If it shows one column, select the comma delimiter rather than proceeding. Microsoft documents the full desktop import path. - Choose Transform Data to open Power Query. Some older Microsoft instructions call this control Edit. Do not choose an immediate load yet.
- Inspect Applied Steps in the query editor. Power Query may have added Changed Type through automatic type detection for a CSV. If that early step converted
CustomerCode,ZIP, orReferenceto a number, delete or revise that step before typing the identifiers as Text. Selecting Text after a destructive number conversion would preserve an already damaged value. Microsoft explains how CSV type detection creates Changed Type. - Select
CustomerCode,ZIP, andReference. Set their data type to Text. Microsoft's instructions describe Home → Transform → Data Type → Text; placement may vary. SetAmountUSDto a numeric decimal type. Inspect the preview: the first ZIP is02108, and the three references end in567,568, and569. - Choose Close & Load. Save the result as an
.xlsxworkbook after checking it. You can refresh the query later when the source CSV changes, but check new values and column types again after any change in source structure.
Answer check
The loaded table should have 3 data rows. O-201 must show CustomerCode 000123, ZIP 02108, and Reference 12345678901234567. O-203 must end with ...569, not the same digits as O-201. The amounts remain numeric: 48 + 75 + 36 = $159. Compare with the answer workbook, which stores the identifiers as text, or use the plain-text check.
If the total is correct but the IDs are wrong, the import is not correct. A total checks the amount column, not the identity columns.
Common mistakes
- Double-clicking the CSV: Excel may infer numeric data types before you can choose what an identifier means. Use a controlled import instead.
- Changing cells to Text after opening: a formatting change cannot reconstruct an original zero or rounded digit that was lost earlier.
- Keeping an earlier Changed Type step: inspect the applied steps in order. Set identifier types before numeric conversion, not after it.
- Treating every column as Text:
AmountUSDshould stay numeric so sorting, filtering, and sums work as expected. - Saving a checked workbook as CSV and reopening it directly: CSV does not retain spreadsheet data types. The next direct open can repeat the same issue.
FAQ
Could I use Excel's Automatic Data Conversions setting? In Microsoft 365 and Excel 2024, that setting can prevent some conversions. The controlled import here is still useful because it makes each column's intended type explicit. Availability depends on version. Microsoft lists supported releases.
Can a custom `00000` number format fix ZIP `02108`? It can display a known width in a workbook, but it does not prove the original text survived. It cannot recover arbitrary lost digits or a rounded 17-digit reference. Import IDs as Text when exact characters matter.
Why does the CSV look correct in a text editor but wrong in Excel? The text editor shows the raw characters. Excel applies data-type interpretation when opening. The source may still be intact even though the quick-open workbook is not.
Official references
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.