Back to blog

Tips

5 CSV mistakes everyone makes

Common pitfalls that corrupt data and how to avoid them.

Dec 24, 20244 min read
Even experienced data people make these mistakes. The good news: once you know them, they're easy to avoid.

Opening in Excel without import

Double-clicking a CSV lets Excel guess the format. It often guesses wrong, mangling dates, leading zeros, and long numbers. Always use File > Import.

  • Leading zeros disappear (00123 becomes 123)
  • Long numbers become scientific notation
  • Dates reformat to Excel's preference

Saving as CSV loses data

Excel's CSV format doesn't preserve multiple sheets, formulas, or formatting. When you save as CSV, only values from the active sheet survive.

  • Only one sheet saves
  • Formulas become values
  • All formatting is lost

Quick CTA

View without the pitfalls

Readable CSV doesn't mangle your data on open. What you see is what's in the file.

Try it

Ignoring encoding

Opening a UTF-8 file as Latin-1 (or vice versa) corrupts special characters. Always verify encoding before processing.

  • Garbled characters indicate encoding mismatch
  • UTF-8 is safest for new files
  • Check encoding when problems appear

Not checking row counts

After any operation, verify your row count. Unexpected drops indicate lost data. Unexpected increases suggest duplicates or header issues.

  • Count before and after operations
  • Investigate any differences
  • Missing rows are easy to overlook

Key takeaway

Most CSV mistakes happen at open and save. Slow down at those moments and verify.