CSV Imports: Check Columns and Preserve Identifiers

Updated 22/10/2026 · Reviewed content

A CSV import can look tidy while putting a label in the wrong column or shortening an equipment code. Before using the result, check both the field boundaries and the meaning of each field. A count belongs in a numeric column; an identifier may need every character preserved.

This guide follows a fictional equipment handover in Kuching. The example, comparison method and acceptance checklist are our own editorial work. Microsoft documentation supports the limited Excel behaviours identified below; this is not a report of a live workbook test.

Define the expected record first

Imagine that Mariam receives a three-record CSV export from an invented community equipment cupboard. Each record should contain a tag, an item label and a quantity. The cupboard's own register treats tags as exact strings, including any initial zeros.

These are invented identifiers, not personal details or real inventory data. In this example, 00421 and 421 refer to different items because the register defines them that way. Mariam should not decide that they are interchangeable simply because both can be read aloud as the number four hundred and twenty-one.

The expected result is three columns and three records, with each tag still attached to its original label and quantity. The quantities total six units. That total is useful for reconciliation, but it cannot establish that the three tags survived unchanged.

Check where each column begins

A separator tells the importing tool where one field ends and the next begins. Microsoft's text-file import guidance documents a delimiter choice and a preview showing its effect. Use that preview to compare the proposed columns with the expected record structure.

The comma inside Lamp, desk is part of Mariam's label. It should not create an extra field. Microsoft's Text Import Wizard documentation explains that a text qualifier can keep a quoted value containing a delimiter together in one cell; the setting must match the file's actual conventions.

Our example therefore has two different uses for a comma: separating fields and appearing inside an item label. Check the complete first record, not just the header. A preview that gives Lamp and desk separate columns fails the example's expected structure even if the other two records look normal.

• Match each header to the values beneath it.
• Check a label containing the separator character.
• Confirm that the final quantity remains in its intended column.
• Compare the number of fields across every record in a small file.

If the structure is unclear, ask the sender which export settings were used. Repeatedly deleting apparent extra columns can conceal the original parsing problem and separate a value from the record it belongs to.

Assign types by purpose

Microsoft's leading-zero guidance explains that Excel can automatically convert numeric-looking text and remove initial zeros. It describes importing identifier columns as Text through Get & Transform. Availability and control names vary by Excel version; its automatic conversion settings are documented for particular recent editions.

For Mariam's register, the tag answers “which item?” while the quantity answers “how many?”. Those are different jobs. Treating the tag as text and the quantity as a number follows the fictional register's definitions, rather than applying one type to the whole file.

A successful total of six does not compensate for a changed identifier. Conversely, preserving all tags does not establish that the quantities are ready for arithmetic. Inspect both requirements before using the import for a stock handover.

Recover from the source rather than guess

Suppose a trial import displays 421 for both the lamp and the cable box. The visible result no longer tells Mariam which original tag had initial zeros. She still has the unchanged source export and the cupboard register, so she can discard the trial and repeat the import with the correct field definitions.

Microsoft warns that applying formatting after a conversion does not restore zeros already removed. Our recommendation is to preserve the received file and return to that reference when a mismatch appears. Making every tag five digits long would be wrong here: the cable box legitimately uses the three-character tag 421.

Do not invent a universal tag length from the longest example. If no authoritative original or field definition is available, mark the identity discrepancy unresolved and ask the owner. A plausible-looking reconstruction should not become the basis for handing over the wrong item.

Use a small acceptance routine

For a three-record file, Mariam can compare every record. For a larger import, define checks appropriate to the complete data and report any sampling limits. Our example routine is deliberately small enough that the reader can reconcile all of it.

1. Keep the received CSV unchanged and identify the sender's field definitions.
2. Import a working copy using the route supported by the installed spreadsheet tool.
3. Check separator and qualifier settings against the preview, including the comma-bearing label.
4. Set exact identifier fields to an appropriate text type before completing the import.
5. Compare all three tags, labels and quantities with the reference table.
6. Confirm three records and six units, then save the working result separately.
7. Reopen the saved deliverable and repeat the checks the recipient will rely on.

The handover note should state what was compared. In this example it can say that all three records matched the reference, including the two distinct tags ending in 421. It should not claim that one correct total proves the entire import is accurate.

If a later export is required, inspect that output as another deliverable. A validated working sheet and the file eventually sent to a colleague are separate checkpoints. Agree which format and field definitions the receiving process expects.

FAQ

Can I fix every shortened tag by adding zeros?

Only an established source definition can tell you the correct characters. Our fictional register contains both 00421 and 421, so padding everything to five digits would change a valid tag. Return to the original reference instead of guessing.

Why is one label spread across two columns?

Investigate how the tool interpreted separators and text qualifiers. In this example, the comma in Lamp, desk belongs inside the label. Compare the source conventions and import preview before editing individual cells.

Should every CSV column be imported as text?

Choose types according to the receiving task and field definitions. Mariam needs exact text identifiers and numeric quantities. Keeping identity intact and making quantities usable are both part of acceptance.

Does the correct record count prove the import worked?

No. Three records can still contain altered tags or displaced labels. Reconcile structure, values and record associations, then report the scope of the checks actually performed.

Related reading

If records acquire the wrong values after reordering, use our spreadsheet sorting guide. If the question is whether to retain a calculation or its current output, see formula and fixed-result paste choices. Neither replaces the import checks above.