CSV, Excel, and code solve different parts of tabular-data work. Use CSV for a simple exchange table, a spreadsheet for interactive formulas and presentation, and code for repeatable transformations. A reliable project may use all three while giving each artifact one clear job.
Do not ask only, “Which file should hold my data?” Ask what must be preserved: values, types, formulas, formatting, multiple sheets, transformation steps, or a portable exchange copy.
Begin with the table’s job
Answer these questions:
- Must different applications exchange the rows and columns?
- Will a person explore, calculate, filter, chart, or present the data interactively?
- Must the same transformation run again on new or corrected input?
- Which artifact is the original input?
- Which calculations or decisions must remain inspectable?
- What output must another person receive?
The answers can identify more than one artifact. A lab project may preserve the instrument export as CSV, use code to clean it, and deliver a spreadsheet or chart for review.
Use CSV for simple exchange
A CSV file represents records and fields as text:
student_id,week,score
S001,1,8
S002,1,10
S003,1,9
Its strengths are useful but limited:
- a text editor can inspect it;
- many programs can import and export it;
- scripts can process it;
- version tools can compare lines; and
- one table has a straightforward shape.
RFC 4180 documents a common CSV format, including comma-separated fields, optional headers, and quotation rules for fields containing commas, quotation marks, or line breaks. It also notes that implementations have varied. In practice, delimiter, encoding, date, and decimal conventions can still differ.
CSV does not inherently preserve:
- spreadsheet formulas;
- cell formatting;
- charts or images;
- comments;
- several worksheets;
- a complete data type schema; or
- the steps that produced a value.
Use CSV when a simple, inspectable table is the desired exchange artifact. Do not use it as if it were a complete workbook.
Use a spreadsheet for interactive work
A spreadsheet workbook is useful when a person needs to:
- enter or correct values in a grid;
- write formulas;
- sort and filter interactively;
- explore alternatives;
- create charts;
- apply meaningful presentation; or
- maintain related worksheets.
Important logic may be spread across cells. A copied formula can shift references. Hidden rows or sheets can affect interpretation. Formatting can communicate meaning that is absent from a plain export.
Make the workbook understandable:
- give columns stable, descriptive names;
- include units in headers or documentation;
- distinguish input, calculated, and output cells;
- avoid unexplained constants inside formulas;
- record assumptions;
- check formulas at boundaries; and
- preserve the original input separately when correction history matters.
Use code for repeatable transformation
Code is appropriate when the procedure itself must be preserved and rerun. Examples include:
- applying the same cleaning rules to weekly exports;
- validating that each row has required fields;
- joining several tables;
- computing a result for many files;
- generating an output from raw data; or
- testing assumptions automatically.
Code adds an environment, dependencies, documentation, and testing work. Use it when repeatability or complexity justifies that cost.
A good data script makes inputs and outputs explicit:
Input: data/raw/course-scores.csv
Procedure: scripts/summarize_scores.py
Output: data/derived/score-summary.csv
Verification: row count, required columns, and known sample result
The code preserves the transformation. The output file preserves one result of running it. They are not substitutes for each other.
A combined workflow is often strongest
Consider a weekly course-attendance export:
- Preserve the original CSV without manual edits.
- Run documented code that validates columns and standardizes values.
- Save a derived CSV for exchange.
- Open the derived table in a spreadsheet for interactive review and charts.
- Record any manual correction and apply the correction at the appropriate source or transformation step.
This separates:
- raw input: what the source system provided;
- procedure: how values were checked or transformed;
- derived data: the repeatable output; and
- presentation: the human-facing analysis or chart.
CSV export is a reduction
When you save a workbook as CSV, you are selecting one table of displayed values, not preserving the whole workbook.
Microsoft’s current guidance on saving a workbook as CSV states that formatting is removed and only the active sheet is saved. Microsoft also documents spreadsheet features that do not transfer to text formats.
Therefore:
- keep the workbook if its formulas, sheets, charts, or formatting matter;
- treat the CSV as an exchange or output copy;
- inspect the exported values;
- record the delimiter and encoding when the receiving system requires them; and
- do not overwrite the only copy of the workbook during export.
Reopen the CSV in a text editor as well as the receiving application. A spreadsheet view can make a malformed or automatically interpreted value less obvious.
Compare CSV and workbook representations
Create this table in a spreadsheet:
| item | quantity | unit_price | total |
|---|---|---|---|
| Notebook | 2 | 3.50 | formula |
| Pen | 5 | 1.20 | formula |
| Folder | 1 | 2.75 | formula |
Use formulas to calculate each total. Add a bold header and a second worksheet
named Notes containing:
Prices are fictional practice data.
Save the workbook as .xlsx. Then export the table as .csv without replacing
the workbook.
Inspect both:
- Open the CSV in a text editor.
- Reopen the workbook.
- Compare the formula cells with the exported values.
- Check whether formatting and the
Notessheet exist in the CSV. - Confirm the number of rows and columns.
- Change one quantity in the workbook and observe which artifact recalculates.
Record what each file preserves. The exercise is successful when you can explain why the files are related but not interchangeable.
Check data assumptions explicitly
Whichever representation you choose, document:
- column names and meanings;
- units;
- allowed missing values;
- date and time conventions;
- identifiers that must retain leading zeros;
- delimiter and character encoding when relevant;
- whether formulas or calculated values are authoritative; and
- validation checks.
Be cautious when importing untrusted data into spreadsheet software. Text that begins like a formula may be interpreted by some spreadsheet workflows rather than treated as ordinary data. Review the source and use the institution’s safe handling guidance for unknown or sensitive files.
Common mistakes
- Calling CSV a simple workbook. It does not preserve the workbook model.
- Keeping formulas only in one unexplained spreadsheet. Document important logic and assumptions.
- Writing code for a one-time two-row calculation. Match the procedure to the scale and need for repetition.
- Editing generated output by hand. Correct the source or transformation when the output must be reproducible.
- Overwriting raw input. Preserve the original when provenance matters.
- Assuming imported values kept their intended types. Check dates, identifiers, decimals, and missing values.
- Exporting without reopening the result. Verify the artifact another tool will receive.
Do this now
Complete the workbook-and-CSV comparison. Then label each file:
Raw input:
Working analysis:
Transformation procedure:
Exchange output:
Final presentation:
Some labels may be not needed. The purpose is to assign clear jobs, not to
create unnecessary files.
Log what you learned
Record only:
- Result: What did the action produce?
- Evidence: What observation, test, or source supports that result?
- Next action or unresolved question: What should happen next?
Next, distinguish source files from files that can be regenerated.