Validate a CSV Export Against the Original Spreadsheet

A valid CSV export should contain the intended worksheet’s records, columns and exported values. It does not need to reproduce the workbook byte for byte or preserve colors, fonts, formulas, charts or cell data types.

Use this acceptance test before publishing:

Check	Expected result
Worksheet	The CSV came from the intended sheet
Data rows	Parsed CSV record count equals the intended source data-row count
Columns	Header names, order and count match
Values	Selected records match the defined exported representation
Structure	Every record has the expected number of fields
Text	Accents, non-Latin text, commas, quotes and multiline values survive

Keep the original spreadsheet unchanged and validate a copy of the finished CSV.

1. Define the expected export

Before comparing files, identify the exact source range:

• workbook filename and version;
• worksheet name;
• header row;
• first and last data row;
• first and last exported column;
• whether totals, notes, hidden rows or filtered-out records belong in the export.

Also decide what text the CSV should contain for dates, times, percentages, currency amounts, formula results and values displayed with rounded precision. The correct comparison target is this intended export representation—not necessarily the workbook’s underlying value or visual formatting.

This matters for multi-sheet workbooks. Excel’s CSV formats save only the active sheet, according to Microsoft’s text and CSV export documentation. If several sheets must be published, validate each separate CSV against its corresponding worksheet; see the guide to exporting Excel worksheets as separate CSV files.

Record the expected dimensions before exporting. For example:

Source: survey-results-v4.xlsx
Sheet: responses_clean
Header row: 1
Expected data rows: 12,438
Expected columns: 17
Key field: response_id

Count observations only. Do not include the header, title rows or a totals row unless they are deliberately part of the published data.

2. Parse the CSV before counting rows

Do not use the number of lines shown by a text editor as the record count. A valid quoted field can contain a line break, so one CSV record may occupy multiple physical lines. RFC 4180 says fields containing commas, double quotes or line breaks should be quoted, and literal double quotes inside quoted fields must be doubled (RFC 4180, Section 2).

Open the file with a CSV-aware application or parser. Then verify:

1. the parser reads the file without an error;
2. the first parsed record is the intended header;
3. every record has the same number of fields as the header;
4. the number of parsed data records, excluding the header, equals the expected source count.

If cells contain paragraphs or addresses with embedded newlines, use the more specific multiline CSV validation checklist.

3. Compare headers exactly

Compare the parsed CSV header with the source columns as an ordered list. Check:

• number of columns;
• spelling and capitalization;
• punctuation and spaces;
• column order;
• blank or duplicate names;
• the first data record immediately below the header.

A set comparison is insufficient because it would miss reordered columns. For publication, headers should also remain stable enough for readers and API consumers to understand future releases. If it is unclear whether the first row contains labels or observations, resolve that before upload using the CSV header-row test.

4. Compare values by stable record key

Do not validate only the first five rows. Select records by a stable identifier—such as response_id, station_code or a documented composite key—so sorting cannot make the comparison meaningless.

Use a risk-based sample that includes:

• first, middle and last records;
• at least one blank value;
• zero and negative numbers;
• the minimum and maximum date;
• long text;
• accented or non-Latin characters;
• commas, double quotes and embedded line breaks;
• identifiers with leading zeros;
• unusually large or small numeric values.

For each sampled key, compare every exported field. A compact audit sheet can look like this:

Key	Column	Expected export text	Parsed CSV value	Match?	Note
A-0007	amount	-1250.50	-1250.50	Yes	—
A-0042	postcode	00123	00123	Yes	Text identifier
A-0100	status	Pending	Pending	Yes	—

For important numeric columns, add aggregate checks over the complete dataset: count, count of blanks, minimum, maximum and sum. Apply the same documented parsing and rounding rules on both sides. Matching aggregates do not prove every record is correct, but they can expose truncation, omitted records or decimal conversion that a small sample misses. For a high-stakes release, automate comparison of every value by key rather than relying on a sample alone.

5. Compare content, not workbook appearance

CSV is a text table, not a workbook. For Excel’s comma-delimited CSV format, Microsoft says the active worksheet’s displayed text and values are saved, while formatting, graphics, objects and other worksheet content are lost (Microsoft’s format-compatibility reference).

These differences are therefore normally expected:

• fill colors, fonts, borders and column widths disappear;
• formulas are not retained as working formulas; Excel normally exports displayed results, but exports formula text if the worksheet is set to display formulas;
• comments, charts, validation rules and hyperlinks are not preserved as workbook features;
• one source cell becomes one text field, with no inherent number, date or identifier type.

The W3C’s CSV primer notes that CSV has no mechanism within the file to declare a column’s data type or uniqueness requirement (CSV on the Web: A Primer). Preserve those meanings in a data dictionary and test risky fields as text. In particular, confirm that an identifier such as 00123 remains 00123, not 123; the leading-zero preservation checklist covers that case in detail.

Inspect the parsed value rather than trusting how a spreadsheet application displays a reopened CSV. Import software may reinterpret text as dates or numbers even when the file itself contains the intended characters.

6. Record the result and inspect the published table

Save a short validation record with the source and export:

Source workbook: survey-results-v4.xlsx
Source sheet: responses_clean
CSV file: survey-results-2026-09-22.csv
Rows: 12,438 expected / 12,438 parsed
Columns: 17 expected / 17 parsed
Sampled keys: 25
Aggregate checks: passed
Encoding and special-character checks: passed
Validated by/date: …

Only publish after discrepancies are corrected or explicitly documented. Because the resulting data page is public, remove confidential, personal or otherwise sensitive fields before upload.

After publication, repeat a small acceptance test on the rendered table: confirm the intended columns appear, locate several sampled keys, compare their displayed values, and test sorting or filtering on representative columns. This final check validates what readers can use, not just the local CSV.