Column Harbor
Module 06: Make the work repeatable
LESSON 21 / Advanced · ABOUT 20 MIN

Import CSV without damaging identifiers

Choose encoding, delimiter and types before loading, then verify leading zeros, long IDs, quoted names and numeric amounts.

Before you start

The walkthrough uses Get & Transform in Windows desktop Excel 2016 and later. Microsoft 365 for Mac has Text/CSV import through Get Data, but its interface differs. Use desktop Excel for this local-file workflow; web import and refresh depend on supported connectors and account capabilities.

Useful first: Format numbers without confusing display and value · Build dates and calculate elapsed hours safely

By the end, you can…
  • Keep identifiers as text before conversion can discard information.
  • Distinguish a delimiter problem from a type or encoding problem.
  • Verify the imported values against the original text file.
YOUR PRACTICE FILE

Small data. A result you can check.

Synthetic UTF-8 CSV. Preserve the raw text file and import it; do not double-click and resave it as the preparation step.

Download practice data
View the raw practice data
order_id,tracking_id,date_text,amount,customer
00123,123456789012345678,03/04/2026,19.50,"Ana, Jr."
00007,9007199254740993,17/04/2026,8.25,Zoë

01Decide what each column means

Save the supplied sample as its UTF-8 CSV file without first opening and resaving it in Excel. The five fields have different roles: order_id and tracking_id are identifiers, date_text is an intentionally unparsed date string, amount is arithmetic data, and customer is text. The original file is your evidence if a conversion goes wrong.

The two leading-zero codes and the long tracking values must remain exact. A custom number format cannot recover digits already discarded during numeric conversion. Keep date_text as text here because the first date, 03/04/2026, is ambiguous without the supplier's date convention.

02Inspect before loading

In a blank desktop workbook choose Data > Get Data > From File > From Text/CSV; some Windows ribbons expose From Text/CSV directly. Select the sample. Set File Origin to UTF-8 and Delimiter to Comma. Choose no automatic type detection when offered, then Transform Data. The preview should contain five columns and two data records, with Ana, Jr. remaining one customer value.

If an automatic Changed Type step exists, remove it before setting types and inspect the earlier raw values. Changing an already-damaged worksheet number back to Text is too late. Promote the first row only if it is still a data row. Set order_id, tracking_id, date_text and customer to Text. Set amount through Change Type > Using Locale to Fixed Decimal Number and English (United States), matching the sample's decimal point.

03Check exact values, not just appearance

Use Home > Close & Load To to load a table at Import!A1 in an empty sheet named Import. The expected cells are A2=00123, B2=123456789012345678, C2=03/04/2026, D2=19.5 and E2=Ana, Jr. A3 is 00007, B3 is 9007199254740993, D3 is 8.25 and E3 is Zoë.

Run the exact-text check below in G2. Also verify =COUNT(D2:D3) returns 2 and =SUM(D2:D3) returns 27.75. The display may show 19.50 after formatting without changing 19.5 numerically. Save the working workbook as XLSX so its query and sheet setup survive; reopening an exported CSV can trigger fresh type inference.

Excel 365 and 2024 offer additional automatic-conversion controls, but this lesson does not depend on those controls being present in 2016, 2019 or 2021. Text import remains a deliberate column-level decision.

=AND(EXACT(A2,"00123"),EXACT(B2,"123456789012345678"),ISTEXT(A2),ISTEXT(B2))

Expected result: TRUE. Use your locale's argument separator if commas are not accepted.

PUT IT INTO PRACTICE

Audit the second record without coercing its identifiers into numbers.

  1. In G3 enter =AND(EXACT(A3,"00007"),EXACT(B3,"9007199254740993"),ISTEXT(A3),ISTEXT(B3)).
  2. Compare E2 and E3 with the source file, then inspect the two amount checks.
  3. If a check fails, return to the original CSV and correct the import steps rather than overwriting IDs by guesswork.
Show the worked answer

G3 is TRUE; the two amount cells are numeric and total 27.75.

There are two records, five columns, one comma inside Ana, Jr., and an intact ë in Zoë.

Check your work
  • Date strings are preserved, not yet interpreted as dates.
  • No ID was repaired merely by adding a display format.
CHECK YOUR UNDERSTANDING

One question before you move on.

An 18-digit identifier was already rounded as a worksheet number. What is the reliable recovery path?

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.
NEXT LESSONTurn one CSV import into a repeatable query

Keep the skill close.

FIELD GUIDEKeep Leading Zeros in Excel CSV Imports and ExportsFIELD GUIDEExcel Changes the Last Digit to Zero: Keep Long IDs IntactFIELD GUIDECSV Accents Look Broken in Excel: Import the File as UTF-8FIELD GUIDECSV Opens in One Column in Excel? Fix Comma or Semicolon ImportsWORKSPACE TOOLCSV import checkerWORKSPACE TOOLConvert CSV / TSV / JSON

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.