Editing data in a CSV file

Most of you out there reading are not just looking at CSV files - you are working with and editing the data within them (which is more fun anyway)!

It's most common to open and edit your data in a spreadsheet program, like Excel, rather than doing any major editing in a text editor (CSV's natural habitat...). So how do you go about actually doing that?
First thing's first - import your data into the spreadsheet program. This will ensure that your CSV data is displayed accurately. That means avoiding things like dropped leading zeroes, missing special characters, and so on...This way you can actually edit and work with your data, rather than exerting all your efforts on fixing data that opened like a can of worms.

Once your CSV data is opened, use it like you would any other tabular data in a spreadsheet! That means, you can:

  • Add a header (a must!)
  • Insert rows and/or columns if you need to add data to your file
  • Apply a filter or sort your data for quick navigation of specific data points
  • Search & replace if you need to make corrections on data values
  • and so on!
Just remember, if you are going to be saving your data as CSV file at the end of your editing session, don't waste your time with formatting or trying to save formulas to be used again. CSV is plain text, which means all your hard work will straight up disappear once it's saved as CSV format. As nice as your formatted table and highlighting look, they won't do you any good here!

And when you're done tidying up your dataset, just remember to save as .CSV (comma delimited) format, with UTF-8 encoding!