How to open a CSV file in a spreadsheet correctly

Open the file in Grid by Zaweba and let it do the detective work. When you open a .csv, .tsv or .txt file, Grid detects the text encoding and the delimiter by itself and brings the data in without damaging it, so the columns land in the right places and accented letters read as they should. It's free, offline and needs no account.

Why CSV files go wrong so often

A CSV file is plain text with no instructions inside it. Nothing in the file says which character separates the fields or which encoding the text was saved in, so the program opening it has to guess. Guess the delimiter wrong and every row piles into column A. Guess the encoding wrong and a name like Zoë or a price with a pound sign turns into a jumble of odd symbols.

Different places make CSVs differently, too. A bank export may use semicolons because commas are used as decimal points where it was made. A database may write tabs. An old system may save in a legacy encoding while a newer one saves as UTF-8. The file extension says CSV in all of these cases.

Opening the file step by step

Start Grid and open the file as you would any workbook, choosing the .csv, .tsv or .txt file. Grid reads the start of the file, works out the encoding and which delimiter is in use, and splits each line into cells. You see the result straight away as an ordinary sheet.

Check three things before you trust it. Look along the header row to see the columns are separate. Look for any names or words with accents to see the characters are right. And look at one or two long numbers, such as account or product codes, to see they look the way the source system shows them.

Grid can also appear in Windows' Open with menu for .csv and .tsv files without taking them over, so if another program is the usual opener you can still right-click a file and choose Grid for that one.

Working on the data once it's in

Treat the opened file as a starting point. Turning the range into a table gives you filter drop-downs and a total row, Sort and AutoFilter help you find what matters, and Remove Duplicates clears repeated rows from a messy export. If a column holds two things at once, such as a full name or a code with a date stuck to it, Text to Columns or the TEXTSPLIT function will pull it apart.

Formulas, formatting and charts are features of a workbook, not of CSV. A CSV only ever stores the values of one sheet. If you've done real work on the data, save it as a workbook. Grid's own .zagr file is an .xlsx inside, and Save As .xlsx gives you a file anyone can open.

Saving back to CSV

When another system needs the data back as CSV, Grid saves the current sheet as CSV. Because CSV holds one sheet of values, only the sheet you're on is written and formulas are saved as their results. An opened .csv keeps its own format when you press Save, which is convenient for quick fixes but means the formatting you added won't be in the file next time. If you want to keep both, save a workbook copy as well.

When the result still looks odd

If a column of codes has lost leading zeros or long numbers look rounded, the source data may need those columns kept as text. Spreadsheets treat anything that looks like a number as a number. Check how the original system wrote the values, and use a number format or the TEXT function to show them the way you want.

Dates can trip you up as well, since 03/04 means a different day in different countries. Grid uses the 1900 date system that other spreadsheets use, so serial numbers match, but a date written as text in an unfamiliar order may need converting. DATEVALUE style conversions, or splitting the parts out and using DATE, will set it straight.

Most of the time, though, opening the file is all it takes. Grid by Zaweba was built to read CSV and TSV files carefully, so the usual frustrations of a single crowded column or broken accents simply don't show up.