A practical CSV preflight for agencies: map columns deliberately, preserve URLs and IDs, check encoding, and verify the records that reach your destination.
A profile list can look correct in a spreadsheet and still import incorrectly. A name may split into two fields, a record ID may lose its leading zeros, or a price may land in the wrong column. For agencies moving lists between a researcher, a CRM and an operations tool, the useful check is whether the destination receives the intended values.
Use this workflow before importing a lead or profile list. It focuses on file integrity and field mapping; the destination's documentation remains the authority on accepted columns and formats.
1. Define what each column means
Keep the original export untouched and work on a copy. Before editing, write down the destination field for every source column. A familiar header does not prove the mapping is correct: name might mean a person, company or internal account label.
An illustrative mapping for a profile operations list might be:
| Source column | Intended meaning | Check before import |
|---|---|---|
name | Display name | Preserve accents and punctuation |
profile_url | Public profile address | Preserve the complete URL as text |
external_id | Source record identifier | Treat as text, including leading zeros |
monthly_price_usd | Monthly price in US dollars | Confirm dollars versus cents and billing period |
notes | Internal context | Confirm that the destination supports this field |
These are example headers, not an AllProfiles import schema. Follow the actual destination's required fields, permitted values and size limits. Explicitly skip columns you do not intend to transfer rather than mapping them to a vaguely similar field.
2. Check the delimiter and character encoding
Inspect the import preview before loading anything. If the whole row appears in one column, check the selected delimiter. Microsoft documents that text-file separators can vary and that Excel's Data → From Text/CSV flow provides a preview before loading. Use Transform Data when you need to adjust the imported data first. Microsoft's CSV import guidance.
Use UTF-8 for a working export where the destination accepts it, and check a few names containing accents or non-Latin characters. Microsoft says a UTF-8 CSV saved with a byte order mark can open normally in Excel; otherwise, use its text/CSV import tools. That is guidance for Excel, not a universal requirement for every importer. Microsoft's UTF-8 guidance.
If a name becomes garbled, stop and correct the encoding before continuing. Manually repairing one visible name can leave similar corruption elsewhere in the file.
3. Preserve identifiers and URLs as text
A record identifier is not a quantity. Excel can remove leading zeros and change long numeric values; Microsoft recommends importing such columns as Text. Formatting a cell afterward will not recover information already lost. Microsoft's guide to leading zeros and large numbers.
In an illustrative example, 000427 and 427 are different source identifiers. Compare the exported value with the original before importing it into another system.
Preserve the full profile URL, including its path. Do not substitute the spreadsheet's display label for its underlying link or apply blanket lowercasing to every URL component. Open a representative sample and confirm that each address points to the intended profile. Keep identifiers, phone numbers and handles as literal text when their exact characters matter.
4. Let a CSV exporter handle commas and quotes
Avoid assembling CSV rows by joining values with commas. A note containing a comma needs quoting; a quote inside a quoted field needs escaping. RFC 4180 describes these common conventions, including consistent field counts and quoted fields that can contain line breaks. It is an Informational RFC, and individual importers can have additional requirements. RFC 4180.
This illustrative CSV shows a comma inside a note and an escaped quote:
name,profile_url,external_id,notes
Alina Example,https://example.com/profiles/alina,000427,"Reviewed, ready for mapping"
Denis Example,https://example.com/profiles/denis,000428,"Label: ""priority"""
The names, identifiers and URLs are placeholders. In the parsed result, each record has four fields, and the second note reads Label: "priority". Count parsed records rather than physical lines if notes can span multiple lines.
5. Review formula-like values before opening shared files
CSV quoting protects field boundaries, but it does not make spreadsheet formulas harmless. OWASP documents that spreadsheet applications may interpret values beginning with characters such as =, +, - or @ as formulas. It also warns that common escaping approaches can fail after an Excel save-and-reopen cycle; there is no universal sanitization strategy for every spreadsheet and downstream consumer. OWASP's CSV injection guidance.
Treat this as a review flag, not a reason to erase legitimate data such as an international phone number beginning with +. Use a text preview to inspect an unfamiliar export. If your team needs a spreadsheet viewing copy with extra protections, keep it separate from the canonical import file and verify that those protections have not changed the values sent to the destination.
6. Verify a representative trial and reconcile the result
Where the destination permits a small trial, choose records that exercise the difficult cases: an accented name, a leading-zero ID, a complete URL and a note containing punctuation. Check the resulting records before importing the remainder. If there is a preview-only mode, use it; do not assume every tool offers one.
Before committing the full file, confirm:
- Every column is mapped deliberately or explicitly excluded.
- Required values are present, and dates, currencies and units match the destination's documented format.
- The selected record count matches the intended batch.
- A copied source value still matches the value shown in the destination preview.
- Any invalid or excluded rows have an explicit reason and an owner to resolve them.
After import, record the number submitted, created, updated and rejected, where the tool reports those outcomes. Keep the result alongside the source filename and mapping notes. If an import times out, inspect the destination before retrying the entire batch: a missing success screen does not establish that nothing was created.
This gives the next person a clear handoff: which file was used, how its columns were interpreted, and which records still need attention.
