A spreadsheet import becomes difficult at the point where a neat table stops being a reliable data contract. A workbook may contain instructions above the header, identifiers with leading zeroes, formulas, multiple tabs, and dates that depend on regional conventions. CSV removes some workbook complexity but introduces its own questions about separators, quoting, and encoding. Treat both formats as source material that needs interpretation, not as a ready-made database.
This guide walks through a proposed customer-directory import. The destination needs an external identifier, a display name, and a country code. The objective is to preserve meaning, explain corrections, and make repeated imports predictable. The spreadsheet import API overview provides the broader workflow and related resources.
Choose CSV or XLSX for the actual handoff
CSV is often a convenient choice when the handoff is one rectangular table. An XLSX workbook is useful when a team already works with multiple worksheets or needs richer authoring context. That convenience does not mean every workbook feature belongs in the importer. Decide whether your workflow accepts one named sheet, asks an operator to choose a sheet, or treats several sheets as related datasets.
Write a small delivery guide for the producer. Include the expected header, one ordinary example, and one example containing an optional blank value. State which format variants you accept. A clear agreement with the exporting team is usually more valuable than adding complicated guesses for every possible spreadsheet layout.
Parse CSV records, not individual lines
A tempting shortcut is to split text on newline characters and then split each line on commas. That approach confuses separators with characters inside quoted fields. A customer name or note can legitimately contain a comma, and a quoted field may contain a line break. Use an appropriate parser and test it with those cases rather than relying on a small, clean sample.
The CSV format description in RFC 4180 documents quoting conventions, including doubled quotation marks within quoted fields. It also describes an optional header. Treat the RFC as a reference for a documented format, not proof that every file called CSV follows it. Your importer should state its accepted dialect and return a useful parsing error when a file falls outside that agreement.
Make header selection explicit
Consider a workbook whose first row says Customer export and whose actual field names appear in row four. A detector might guess correctly in a preview, yet fail when someone adds another instruction row. For recurring imports, store the selected worksheet and header rule with the mapping configuration. For one-off imports, show the operator what was selected before proceeding.
Check for duplicate headers, blank headers, and invisible whitespace around names. Two columns called Name cannot safely map to different destination fields without additional identification. Preserve column positions in the source reference so an error can point to the correct cell even when the displayed header is ambiguous. Do not silently discard an unexpected extra column.
Preserve identifiers as identifiers
A customer identifier such as 00127 is not a number merely because all its characters are digits. Turning it into a numeric value changes the identifier. Decide which fields are strings at the destination boundary, and test whether the source export has already altered their representation. An importer cannot recover lost leading zeroes with confidence unless an agreed rule supplies that information.
The same caution applies to long reference numbers, postal codes, and telephone labels. When a source has already changed a value, return a correction request rather than pretending the original can always be reconstructed. Display the source value and intended destination type in the preview so the problem is visible before records are committed.
Treat dates and money as explicit conversions
The text 03/04/2026 can represent different dates depending on the chosen convention. Do not guess silently. Ask the producer to use an agreed representation or associate a date interpretation with the mapping. For timestamps, decide whether the source includes a time zone and what a missing zone means. Keep a date-only value distinct from a moment in time.
Set a separate money policy
For monetary values, identify the currency, decimal convention, and rounding policy before converting. A punctuation mark may separate decimals in one export and thousands in another. Test the intended transformation with positive, negative, zero, and blank values. A blank balance should not automatically become zero unless that is a deliberate business rule accepted by the destination owner.
Decide how to handle workbook formulas
A workbook may contain both entered values and formulas. Define whether your chosen parser reads stored results, exposes formulas, or requires another evaluation step. Do not promise that formula results will always be fresh. A source workbook can be saved in a state that does not match the latest underlying data or the destination's calculation environment.
A practical handoff policy is to request a values-only export for fields that must be deterministic. Keep the original workbook available under appropriate access controls when an audit trail is needed. Include a formula-containing sample in acceptance testing so the actual library behavior is observed rather than inferred from what a spreadsheet application displays.
Build a correction-friendly preview
Show a small representative preview after parsing and mapping, while making clear that the full file still needs validation. Include the source row number, destination field name, and any transformation applied. Highlight missing required values and conflicting identifiers. Avoid a preview that makes every cell look successful merely because the parser produced a string.
For the customer-directory example, a useful report might identify row twelve as missing external_id and row twenty as having an unknown country code. Explain the expected value in ordinary language. Let the operator distinguish a spelling correction from a business decision, such as whether two records actually refer to the same customer.
Make duplicates a policy, not a surprise
There are at least two duplicate questions: whether the same identifier appears twice within the file, and whether that identifier already exists in the destination. Handle these separately. An existing destination record might be an intended update, while conflicting rows inside one source file may need review before either is accepted.
Define whether blank fields in an update mean leave unchanged, clear the destination, or reject the record. This decision is easy to overlook and can cause unexpected data loss. Demonstrate it in the import instructions with a small before-and-after example. The mapping and validation guide expands on destination contracts and transformation boundaries.
Test the awkward files before release
Build a fixture collection that contains a quoted comma, a quoted newline, a duplicate header, a blank required identifier, an unexpected sheet name, and a mixed-format date column. Include a file that parses successfully but fails business validation. Keep each fixture small enough that a maintainer can understand its purpose without opening a large workbook.
Test the error report as carefully as the imported records. Row numbers should match the source convention explained to the operator. Ensure the count of accepted and rejected rows reconciles with your definition of the data region. If blank trailing rows are ignored, document that behavior so the reported total does not appear arbitrary.
Conclusion: agree on meaning before automating
A dependable spreadsheet import is a collaboration between the producer, the operator, and the destination application. CSV and XLSX are useful containers, but neither removes the need for an explicit schema or a correction process. Keep identifiers intact, make conversions visible, and choose how updates behave before handling the first real customer file.
Start with the format guides and a small representative export. Once the workflow produces clear results for imperfect source files, automate the repeated steps. The best first milestone is not the largest workbook accepted; it is a predictable import that another person can understand and repair without asking its author.
