Visit Website →
← Back to blog

How to Format a CSV File: Rules, Fixes and Free Tools

November 3, 2026
How to Format a CSV File: Rules, Fixes and Free Tools

To format a CSV file, put one record on each line, separate fields with a single delimiter (usually a comma), give every row the same number of fields, and wrap any field that contains a comma, quote or line break in double quotes. Add a header row with unique column names, save as plain text, and choose UTF-8 encoding if your data has accents or symbols. That covers most import errors. The rest of this guide shows the exact rules, how to get there from a spreadsheet, and how to repair a file that already looks wrong.

fixorahub.com
Work with CSV data in your browser
Fixora’s browser-based data comparison tools can help you work with CSV data without an account or software downloads.
Explore Fixora’s tools

TL;DR

  • A CSV is plain text: rows are lines, columns are separated by commas, and there is no bold, color, formula or column width.
  • Quote any field containing a comma, double quote or line break, and double any quote inside it.
  • Keep the same number of fields on every line and one clear header row.
  • If everything lands in one column, the delimiter in the file does not match your computer's list separator.
  • You can preview, clean and convert CSV files in your browser with Fixora's CSV tools, with no upload and no account.

Table of Contents

What a CSV file actually is

A CSV (comma-separated values) file is a flat text file for a rectangular grid of numbers and text. The Library of Congress format description explains that fields are separated by commas and records by line breaks, as set out in RFC 4180 in October 2005. The same page notes that CSV is a de facto format with no single official specification, so variants exist.

That last point explains most of the trouble. One program writes semicolons, another expects tabs, a third saves in a different character set. Each program is following slightly different habits, and the file looks fine in one place and broken in another.

Here is a small, correctly formatted example:

id,name,city,notes
1,Ana Torres,Austin,Prefers email
2,"Smith, Jordan",Denver,"Said ""call after 5"""
3,Priya Nair,Seattle,

Three things are worth noticing. The header row names each column. Row 2 puts a comma inside the name, so that field is wrapped in quotes. Row 3 has an empty last field, but it still has the comma that marks it, so every line has four fields.

CSV example showing four fields and quoted comma

The formatting rules that matter

The RFC 4180 specification is the closest thing to a standard. Here is how its rules play out in practice.

One record per line, same number of fields

Each record sits on its own line, fields are separated by commas, and every line should hold the same number of fields, according to RFC 4180. A row with one comma too many or too few shifts everything after it, and importers either reject the file or quietly misplace data. Missing values are written as nothing between two commas, not skipped.

Headers

A header row is optional in the spec, but nearly every import tool expects one. Use short, unique names. Avoid duplicate column names, leading or trailing spaces, and line breaks inside a header. Keep one convention, such as first_name or FirstName, across the whole file. As the Open Water Foundation's CSV guide points out, applications that write and read CSV need to treat headers consistently, so the safest move is to match the exact header names the destination system documents.

Quotes and special characters

Fields that contain commas, line breaks or double quotes should be enclosed in double quotes, and a double quote inside a quoted field is written as two double quotes, per RFC 4180. Addresses ("100 Main St, Suite 4"), free-text notes and product descriptions are the usual culprits.

Quoting a field that does not need it is harmless. Forgetting to quote one that does is what breaks files.

Line endings

RFC 4180 uses a CRLF pair (carriage return plus line feed) between records, though real implementations vary, and the last record may or may not end with a line break, as the RFC Editor's copy describes. In practice, Windows tools use CRLF while Linux and macOS use LF, and a robust parser should accept both, as the Open Water Foundation notes. If an import fails with no obvious reason, line endings are a reasonable thing to check, though most modern tools cope.

Encoding

CSV traditionally uses US-ASCII, but other character sets are permitted, according to the Library of Congress. If your file includes names like José or symbols like € or ñ, save it as UTF-8 so the characters survive. When a file shows odd characters such as "é", the writer and the reader disagree about encoding.

Dates, numbers and leading zeros

The format itself has no data types. Everything is text until a program guesses. That is why ZIP codes like 02134 can lose their leading zero, long ID numbers can turn into scientific notation, and 3/4 can become a date when you open the file in a spreadsheet. Two habits help:

  • Write dates in the unambiguous year-month-day form (2026-11-02) when the destination allows it.
  • Treat IDs, ZIP codes and phone numbers as text. When importing into a spreadsheet, use its import or text-import option and mark those columns as text, instead of double-clicking the file.

A formatting checklist before you upload or import

  1. Open the file in a plain text editor and look at the raw lines. This shows the true delimiter, quotes and line count.
  2. Check the header row. Unique names, no blanks, exact match to what the destination expects.
  3. Count fields. Every row should have the same number as the header.
  4. Remove empty trailing rows and columns. Stray commas at the end of lines create phantom empty columns.
  5. Trim whitespace around values, especially in keys like email or ID.
  6. Quote fields that contain commas or line breaks.
  7. Save as UTF-8 unless the destination names another encoding.
  8. Preview the result in a viewer that shows the delimiter, not just in a spreadsheet that reinterprets values.

Steps 4 to 6 are tedious by hand on a file with thousands of rows. Our CSV cleaner is built to prepare files for import, and the CSV viewer lets you preview a file and its delimiter before you commit to anything. Both run locally in your browser, so the file stays on your device.

How to create a CSV from a spreadsheet

Most people start with a table in a spreadsheet and need to turn it into CSV. The steps are nearly the same everywhere:

  1. Make sure the sheet is a plain grid: one header row at the top, no merged cells, no blank rows, no totals row, one table per sheet.
  2. Open the File menu, choose Save As or Download, and pick the CSV option. Where several CSV variants are offered, choose the UTF-8 one if your data has non-English characters.
  3. Expect only the active sheet to be saved. A CSV holds a single table, so a workbook with several sheets needs one export per sheet.
  4. Reopen the saved file in a text editor to confirm it looks the way you expect.

What you lose when you save as CSV

Because a CSV is plain text, it keeps values and nothing else. Colors, bold text, column widths, formulas, charts, multiple sheets and cell formats do not travel. Formulas are saved as their current results. That answers a common question: no, a CSV does not keep spreadsheet formatting, and if a file seems to remember formatting, the program reopening it is guessing and applying defaults.

It also answers another popular question: a CSV file is not a PDF. A PDF is a fixed-layout document meant for reading and printing, while a CSV is raw tabular data meant for software. If you need a document version of a table, you would export to PDF separately.

What you lose when you save as CSV

Building a CSV with code

If you create files programmatically, use a CSV library rather than joining strings with commas, because a library handles quoting for you. In Python, you can write a list of lists with the built-in csv module, as a CodeSignal lesson demonstrates. That lesson also advises opening the output file with newline='' so you do not get extra blank lines, which matches the official Python documentation. The same documentation covers delimiter, quote character, escaping and quoting controls, and recommends choosing the encoding explicitly when needed.

For dataframes, the pandas to_csv reference lists options for separators, encoding and how missing values are written.

Starting from a plain list or JSON

Not every CSV starts in a spreadsheet. If you have one item per line, such as emails or SKUs, the text list to CSV converter turns it into a column. If you have an array of objects from an API, JSON to CSV flattens it into rows and headers, and CSV to JSON goes the other way.

Why your CSV looks wrong and how to fix it

Most "bad CSV" problems fall into a few patterns.

SymptomLikely causeFix
Everything is in one columnDelimiter in the file differs from the computer's list separatorImport with the file's real delimiter, or change the separator
Odd characters like "é"Encoding mismatchRe-save as UTF-8, or import choosing UTF-8
Leading zeros vanishSpreadsheet guessed the column was a numberImport the column as text
Long numbers show as 1.23E+15Same guess, scientific notationImport as text, or keep IDs as text
Columns shifted on some rowsUnquoted commas inside fieldsQuote those fields
Blank lines between rowsExtra line break added during writingOpen files with newline='' in Python, or clean the file
Dates swapped (day and month)Regional date interpretationUse year-month-day

The "one column" problem

This is the most common complaint. CodeTwo's troubleshooting guide says data often lands in one column when the file's delimiter differs from the computer's regional list separator. The fix is to check the system list separator and retry with the delimiter the file actually uses. In the US, the default separator is a comma, but a file exported from a system set to a European region often uses semicolons. Opening the file in a viewer first tells you which one you have.

The same file looks different on two computers

This is the reverse case and has the same cause: regional settings. One machine expects commas, another semicolons, and the same bytes are split differently. When you send a CSV to someone else, tell them the delimiter and encoding, or use a plain comma and UTF-8, which most tools expect.

Ragged rows

If some rows have more fields than the header, look for an unquoted comma, a stray quote, or a line break inside a text field. A quick way to find the problem is to preview the file with the delimiter highlighted and look for rows where columns run past the header.

Which program opens a CSV file

Almost any text editor or spreadsheet program can open one, including Microsoft Excel, OpenOffice Calc and Google Sheets, as freeCodeCamp's overview lists. Which one you pick changes what you see:

  • A text editor shows the raw truth: delimiters, quotes, encoding quirks. Best for diagnosing.
  • A spreadsheet shows a tidy grid but may reinterpret dates, numbers and leading zeros. Best for reading and quick edits.
  • A browser-based viewer gives you a quick preview without installing anything or sending the file to a server.

Common cleanup jobs after formatting

Once a file is structurally valid, you often need to reshape it before importing:

  • Keep only some columns, in a new order. The column selector lets you select and reorder fields so the file matches the template of the system you are importing into.
  • Filter rows. The row filter keeps only the records that meet a condition.
  • Split a big file. Many importers cap rows per upload. Splitting by row count creates smaller files with the header repeated.
  • Combine files. If you have monthly exports with the same columns, merging CSV files with matching headers gives you one file.
  • Compare exports. To see what changed between two versions, compare CSV files by key so rows are matched on an ID rather than position.

Our perspective

Most CSV trouble is not about the data. It is about guessing: which delimiter, which encoding, which column is text. The quickest fix is to see the file as it really is before changing anything.

We built our CSV tools around that idea. You can preview a file with its delimiter, clean it for import, filter, split, merge or compare it, and convert it to and from JSON. Everything runs locally in your browser, with no upload and no account, so a customer list or an internal export never has to leave your computer.

For students, analysts and anyone who just needs a file to import correctly today, that means no software to install and no sign-up before you start. We think the right place to fix a file is the place where it already is.

IA

Fix and prepare your CSV in the browser

Fixora is a suite of 40 free browser-based tools. For CSV work you can preview a file, clean it for import, select columns, filter rows, split, merge, compare, and convert between CSV and JSON, all without creating an account or uploading the file.

Fixorahub

Start with the CSV and JSON tools to see the whole set, or browse all Fixora tools if you also need to compress images, merge PDFs or count words in the same session.

A worked example: from messy export to clean import

Suppose you export contacts from one system and the destination wants the columns email, first_name, last_name and company, in that order, as UTF-8.

The export has 12 columns, a header named E-mail Address , with a trailing space, and some company names such as Acme, Inc. that were written without quotes.

  1. Preview the file and confirm the delimiter. You see that rows with Acme, Inc. are shifted by one column.
  2. Run a cleanup to trim whitespace and fix the quoting.
  3. Keep only the four columns you need, in the order the destination lists them.
  4. Rename the header to email exactly.
  5. Save as UTF-8 and do a last preview.

The result is a file that passes validation on the first try, instead of the third.

FAQ

Do CSV files keep formatting?

No. A CSV stores only values as plain text, so colors, fonts, column widths, formulas and multiple sheets are not saved. When you reopen the file in a spreadsheet, the program applies default formatting and may guess the type of each column. Keep a separate .xlsx copy if you need to preserve the look.

Is a CSV file a PDF?

No. A CSV is raw tabular data in plain text, meant for software to read. A PDF is a fixed-layout document meant for viewing and printing. You can turn a table into a PDF, but the two formats serve different jobs.

What program will open a CSV file?

Any text editor or spreadsheet program can open it, including Excel, OpenOffice Calc and Google Sheets. A text editor shows the raw content, while a spreadsheet shows a grid. A browser-based CSV viewer lets you preview it without installing anything.

How do I convert a file to CSV format?

In a spreadsheet, use Save As or Download and pick CSV, choosing the UTF-8 variant if you have special characters. For JSON, a text list or another structure, use a converter such as JSON to CSV. Afterwards, open the result in a text editor to check delimiter and quoting.

Why does my CSV open in a single column?

The delimiter in the file does not match your computer's list separator, for example semicolons in the file and commas in your settings. Import the file choosing the right delimiter, or change the separator setting and reopen it. Previewing the file first shows which delimiter it uses.

Sources

Made with BabyLoveGrowth to earn more organic traffic