What Is CSV? Delimiters, Quotes, and How to Avoid Garbled Text

Exporting spreadsheet data, linking systems together, downloading reports from online services. CSV shows up everywhere. It is a simple format, yet many people trip over differences in delimiters and character encoding. This article explains what CSV really is and how to handle it correctly, including how to avoid garbled text.

What Is CSV

CSV (Comma-Separated Values) is, as the name says, a text format for tabular data made of values separated by commas. One line corresponds to one row (record) of a table, and commas within a line separate the columns.

For example, a list of three people is written like this:

name,age,city
Taro Yamada,30,Tokyo
Hanako Sato,25,Osaka

The first line is called the header row and names what each column represents (some CSV files have no header). CSV is widely used for three reasons:

• Simple and lightweight: it is plain text, so you can open it in a basic editor without special software.
• Highly compatible: nearly every spreadsheet app and database, starting with Excel, can read and write it.
• Easy for machines: its plain rows-and-columns structure makes it simple to import in programs.

On the other hand, it cannot express hierarchical structures like JSON, and for a long time it had no single strict standard. That very looseness is what causes the delimiter and garbled-text troubles discussed below.

Rules for Delimiters, Quotes, and Line Breaks

Because CSV looks so simple, the key questions arise when a value itself contains a delimiter or a line break. RFC 4180, the de facto standard, lays out the broad rules.

Delimiters

The default is the comma, but other characters are common in practice. When values often contain commas (addresses or amounts, for example), tab-separated (TSV) or semicolon-separated formats are sometimes chosen. In much of Europe, where a comma is used as the decimal point, semicolons are commonly used as the delimiter. When you receive a CSV, the first step is to check what it is separated by.

Quotes

When you need a value to contain a comma or a line break, you wrap that value in double quotes ("). A comma inside the quotes is then treated as an ordinary character, not a delimiter.

id,note
1,"Tokyo, Chiyoda"
2,"a note with
a line break"

If you also need a double quote inside the value, you escape it by doubling it (""). For example, the value [He is "genius"] is written as "He is ""genius""". Without knowing this rule, data containing quotes can shift columns and break the file.

Line breaks

Rows are separated by line breaks, but the newline code differs by environment: Windows uses CR+LF while Mac and Linux use LF. Most software reads both, but with older programs or some systems the difference can cause problems such as everything ending up on a single line.

Character Encoding and Why Text Gets Garbled

The most common trouble with non-English CSV is garbled text. The cause is usually a mismatch in character encoding.

A character encoding is the agreed rule for converting characters into numbers (byte sequences). For Japanese, two encodings come up often:

Garbled text happens when the encoding used to save the file differs from the encoding assumed when opening it. For instance, if a Japanese CSV saved in UTF-8 is opened as if it were Shift_JIS, the characters turn into meaningless symbols, and vice versa. In other words, the data is not corrupted; in most cases it is simply being read the wrong way.

Avoiding Garbled Text in Excel

Many people have opened a UTF-8 CSV by double-clicking it in Excel only to see the text garbled. This happens because older Excel behavior guesses the encoding as Shift_JIS when opening a CSV. There are several ways to avoid it.

1. Save as UTF-8 with a BOM: a BOM marker at the start of the file tells Excel to read it as UTF-8 correctly. Many tools offer a "save with BOM" option.
2. Import via Data tab "From Text/CSV": instead of double-clicking, use Excel's Data tab and "From Text/CSV," which lets you specify the encoding and delimiter on import. This is the most reliable method.
3. Check the contents in a viewer first: if you are unsure which encoding it uses, it is safer to check before handing it to Excel. Paste it into the CSV Viewer to display it correctly as a table and immediately confirm whether the text is garbled and how it is delimited. It runs in your browser, so nothing is sent externally.

Conversely, when feeding a CSV made in Excel into a web system, the system usually expects UTF-8, so converting the encoding to UTF-8 on save is the safe choice. Being mindful of which encoding the receiving side expects is the single best way to prevent garbled text.

When to View, Clean, and Convert to JSON

The tasks you need when working with CSV are fairly predictable. Knowing the right tool for each improves efficiency.

Viewing, cleaning, and searching

Raw CSV text is hard to read by eye when columns are misaligned or values are long. The CSV Viewer displays CSV or TSV as a clean table and lets you sort by column, search by keyword, and tidy up stray whitespace. It is the ideal first step for confirming that received data is split correctly and is not garbled.

Converting to and from JSON

When handling data in programs or web APIs, the hierarchical JSON format is often more convenient. Tabular CSV and structured JSON convert back and forth. With the JSON / CSV Converter you can turn an exported CSV into JSON to load into a program, or convert JSON from an API into CSV to open in Excel. You can also specify the delimiter and whether a header is present.

As a bridge between formats

Mismatches like "I received CSV but need JSON" or "It arrived as JSON but my manager wants Excel" happen all the time. The advantage of these tools is that they bridge formats entirely in the browser, with no software to install.

Practical Tips for Handling CSV

Finally, here are practical points for handling CSV safely.

1. Check the delimiter and encoding first: knowing whether it is comma or tab, UTF-8 or Shift_JIS up front prevents most trouble.
2. UTF-8 without BOM for web links, UTF-8 with BOM for Excel: choosing based on the recipient avoids garbled text.
3. Always quote values containing commas or line breaks: forgetting to quote is the top cause of shifted columns.
4. Watch for leading zeros disappearing: postal codes and phone numbers get treated as numbers in Excel, so "01234" becomes "1234." Handle them as text.
5. Use browser-only tools for sensitive data: when CSV contains personal data, choose tools that do not send it externally.

CSV has little to memorize yet a wide range of uses. Master the structural rules of delimiters, quotes, and line breaks, plus how encoding causes garbled text, and you will rarely struggle when exchanging data. Start by pasting your CSV into the CSV Viewer and checking that it displays correctly.