CSV Is Not "Just Text": A Practical Guide to CSV Handling in C# Business Apps (Encoding, Excel Compatibility, Injection Defense)

CSV-based data exchange is still very much a mainstay in the world of business applications. And precisely because it looks like “just joining text with commas,” it’s also an area where the same incidents keep repeating: a hand-rolled parser breaks on a comma inside a field, the file turns to mojibake the moment someone opens it in Excel, or the leading zero disappears from a phone number.

This article assumes a C# business application that reads and writes CSV, and walks through the CSV specification (RFC 4180) and its real-world dialects, how to choose a character encoding, Excel compatibility issues, CSV injection defense, and operational pitfalls - in the order you’re likely to run into them in practice.

1. The Conclusion First (A Decision Table)

CSV design decisions should be worked backward from “who (or what) is going to open this file.”

Here’s the conclusion, up front:

• Parsing CSV with String.Split(',') is broken by specification, even if it looks like it works. It cannot handle commas, newlines, or quotes inside a field. Within the .NET standard library, use TextFieldParser (Chapter 3).
• Character encoding incidents happen because of how Excel interprets the file. For CSV that a human will open in Excel, UTF-8 with BOM is currently the safe default choice. There’s also a trap in .NET: whether a BOM gets written depends on how you specify the Encoding (Chapter 4).
• In .NET (Core), Shift_JIS isn’t available without an extra package. Encoding.GetEncoding("shift_jis") only succeeds once you register CodePagesEncodingProvider from the System.Text.Encoding.CodePages package¹. This is a classic point where CSV handling migrated from .NET Framework first throws an exception in production.
• Leading-zero loss and date reinterpretation cannot be prevented from the CSV side. Crucially, wrapping the field in double quotes does not prevent it either (Chapter 5).
• An application that outputs user input to CSV needs CSV injection defenses. Excel interprets fields starting with = or + as formulas (Chapter 6).

2. The CSV “Specification” - RFC 4180 and Real-World Dialects

CSV has a de facto standard in RFC 4180². There are really only four rules you need to keep in mind in practice.

1. Records are separated by a newline (CRLF); fields are separated by a comma.
2. If a field contains a comma, newline, or double quote, wrap the entire field in double quotes.
3. A double quote inside a field is escaped by doubling it as "".
4. A field that doesn’t need quoting may be quoted or not - either is fine.

In other words, the following single line is a record with three fields.

1001,"Sample Corp Sales Dept","The address is ""1-2-3 XX Ward, Tokyo"", and this note field has a comma in it"

And because RFC 4180 is only a “de facto” standard, real-world CSV comes in countless dialects: line endings that are LF only, no quote handling at all, delimiters that are tabs or semicolons, and headers that may or may not be present. That’s exactly why stating “RFC 4180 compliant” in the integration spec, along with the character encoding, BOM, line-ending convention, and whether there’s a header row, is one of the most cost-effective things you can do to prevent trouble down the road.

3. Don’t Hand-Roll It With Split(‘,’) - Reading CSV in Practice

Given the rules in the previous chapter, it’s clear why String.Split(',') breaks: a comma inside a field causes an extra split, and a newline inside quotes cuts a row off midway. “It works because the file we’re currently receiving doesn’t have any data with commas in it” is not a guarantee - it can break the moment the data changes.

To read CSV correctly within the .NET standard library, you can use Microsoft.VisualBasic.FileIO.TextFieldParser. Despite the namespace, it works perfectly well from C#³.

using Microsoft.VisualBasic.FileIO; // Microsoft.VisualBasic.Core (bundled with the Windows desktop SDK)

using var parser = new TextFieldParser(path, System.Text.Encoding.UTF8)
{
    TextFieldType = FieldType.Delimited,
    HasFieldsEnclosedInQuotes = true, // correctly handle fields wrapped in quotes
    TrimWhiteSpace = false, // defaults to true (silently strips surrounding whitespace); disable this when whitespace is meaningful data
};
parser.SetDelimiters(",");

while (!parser.EndOfData)
{
    try
    {
        string[]? fields = parser.ReadFields();
        if (fields is null) break;
        // process fields[0], fields[1], ...
    }
    catch (MalformedLineException ex)
    {
        // A malformed row: ErrorLine / ErrorLineNumber record the content and line number
        logger.LogWarning("Line {Line} of the CSV is malformed: {Content}",
            ex.LineNumber, parser.ErrorLine);
    }
}

The practical advantage of TextFieldParser is that, in addition to quote handling (HasFieldsEnclosedInQuotes), it can detect malformed rows as a MalformedLineException, complete with the line number³. In real-world CSV ingestion, “should we reject the whole file if one row is malformed, or skip just that row and report it?” is exactly where the spec tends to diverge, and this exception-handling design maps directly onto that decision.

If you also need column-to-object mapping, type conversion, or performance at the scale of millions of rows, consider adopting a proven CSV library (one of the widely used ones on NuGet). Whichever library you choose, there are four things worth checking: RFC 4180 quote handling, character-encoding specification, how malformed rows are handled, and whether streaming reads are supported.

Writing is simpler by specification, so hand-rolling it is realistic - but make sure the escaping logic is consolidated into a single function.

// Consolidate all output-side escaping into this one function
static string ToCsvField(string? value)
{
    value ??= string.Empty;
    bool needsQuoting = value.Contains(',') || value.Contains('"')
        || value.Contains('\r') || value.Contains('\n');
    return needsQuoting ? $"\"{value.Replace("\"", "\"\"")}\"" : value;
}

If even one spot in the codebase is hand-building a line with string.Join just for the amount column, that’s tomorrow’s incident waiting to happen.

4. Character Encoding - Excel’s Behavior and .NET’s Traps

4.1 Use UTF-8 with BOM for Files Opened in Excel

Most CSV mojibake happens because Excel misinterprets the file’s character encoding. Double-clicking a UTF-8 CSV without a BOM (the few marker bytes at the start of the file) can, depending on the environment, get interpreted as ANSI (Shift_JIS in a Japanese environment), wiping out any non-ASCII text. UTF-8 with a BOM gets recognized as UTF-8, so UTF-8 with BOM is currently the safe default for CSV a human will double-click open in Excel.

On top of this comes a trap on the .NET side. Whether a BOM gets written depends on how you pass the Encoding.

// UTF-8 without BOM: the default when you omit the encoding argument
File.WriteAllText(path, csv);

// UTF-8 with BOM: the static Encoding.UTF8 property is configured to emit a BOM
File.WriteAllText(path, csv, Encoding.UTF8);

// To make the intent explicit, specifying via the constructor avoids any ambiguity
File.WriteAllText(path, csv, new UTF8Encoding(encoderShouldEmitUTF8Identifier: true));

That the resulting file differs depending on whether you pass Encoding.UTF8 or omit it entirely is the kind of thing you simply won’t notice unless you already know about it⁴. Once you’ve decided “no BOM for system-to-system integration, BOM for Excel,” it’s worth making the intent explicit in code too, using new UTF8Encoding(...).

4.2 Using Shift_JIS in .NET (Core) Requires Registration

Shift_JIS (code page 932) CSV is still very much alive in integrations with legacy systems. The important thing here is that .NET (Core)/.NET 5 and later only support a small subset of encodings by default - Unicode-based ones plus things like ASCII. Encoding.GetEncoding("shift_jis") throws until you add the System.Text.Encoding.CodePages package and register CodePagesEncodingProvider¹.

using System.Text;

// Register this once at app startup (e.g., in Program.cs)
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

// Now Shift_JIS (code page 932) can be retrieved
Encoding sjis = Encoding.GetEncoding("shift_jis");

An app whose CSV handling was migrated from .NET Framework to .NET, only to throw an exception in production the first time it receives an actual Shift_JIS file, is a classic pattern of incident. We’ve covered what to check during such a migration in “Pre-Migration Checklist for Moving from .NET Framework to .NET.”

And as long as you’re using Shift_JIS, there’s no avoiding platform-dependent characters (like circled numbers or the ㈱ symbol), the wave-dash problem, and the loss of characters that only exist in Unicode. We cover the basics of character encoding in more depth in “An Introduction to Windows Text Encodings - The Mojibake That Happens When Integrating with Linux” and “Windows Text Encodings and Line Endings - The Basics of Mojibake and CRLF/LF.”

5. Data That Breaks When Opened in Excel - Leading Zeros, Dates, and Exponential Notation

Even with the character encoding correct, viewing in Excel brings a second family of problems: Excel “interprets” values when it opens a CSV.

And crucially, none of this is prevented by wrapping the field in double quotes. Quotes only mark the syntactic boundary in CSV; Excel reinterprets the contents regardless.

In practice, there are three options:

1. Have people open it via the correct Excel workflow. Instead of double-clicking, use Excel’s “Import Data (from Text/CSV)” feature and explicitly set the column type to “Text” when loading - this avoids the corruption entirely. It’s worth documenting in an operational runbook.
2. If viewing is the primary purpose, output xlsx instead. Since you can control cell formatting from the output side, this whole family of problems disappears at the root. See “How to Build Excel Report Output” for how to build Excel file output. Note that Excel COM automation on a server or in a service is not recommended - as covered in “Why EXCEL.EXE Processes Remain After C# Excel COM Automation,” it’s a common source of leftover processes, so choose library-based output instead.
3. Outputting in ="007" form is a last resort. It’s a trick that displays as a string when opened in Excel, but as CSV it becomes a “non-standard file containing formulas,” which breaks any ingestion by a system other than Excel. Don’t use it for a file that also needs to serve another system.

6. CSV Injection - Don’t Let User Input Become a Formula

An application that outputs user-entered values (names, notes, addresses, etc.) to CSV for someone to open in Excel has an attack surface called CSV injection. Excel interprets fields starting with =, +, -, @, and similar characters as formulas, meaning that malicious input (for example, a formula crafted to launch an external process, like =cmd|'/C calc'!A0) can end up executing on a different user’s machine when they open the CSV⁵.

The basic defense is to neutralize dangerous leading characters at output time.

// Prefix fields that could be interpreted as formulas with a single quote.
// In a Japanese environment, full-width ＝＋－＠ can also be interpreted as the start of a formula, so include them too
static string SanitizeForExcel(string value)
{
    if (value.Length > 0 && value[0]
        is '=' or '+' or '-' or '@' or '\t' or '\r' or '\n'
        or '＝' or '＋' or '－' or '＠')
    {
        return "'" + value;
    }
    return value;
}

A leading single quote is Excel’s marker for “treat this as a string.” Note that the set of dangerous leading characters can vary by spreadsheet application and version, so it’s more robust to design this kind of sanitization as an allowlist - “only pass values through unchanged if the first character matches this allowlist (e.g., alphanumerics).” That said, if another system ingests the same CSV, the single quote will remain in the data as-is, so you may want to split output into an “Excel viewing” file and a “system ingestion” file. For whether to combine this with input validation (e.g., disallowing a leading = in the first place) and the broader application security design, see “A Minimum Security Checklist for Windows App Development.”

7. Operational Pitfalls - Where Incidents Happen Beyond the Format Itself

Finally, here’s a rundown of places where real-world incidents happen outside of the CSV syntax itself.

• Don’t let numeric or date formatting depend on the OS culture. value.ToString() is affected by the OS’s regional settings. In a locale where the decimal separator is a comma, it outputs 1,5, which collides with the CSV delimiter. For conversions destined for file integration, explicitly use CultureInfo.InvariantCulture, and use a fixed date format like yyyy-MM-dd. We cover date design in depth in “Date, Time, and Timezones in Business Apps.”
• Process large volumes of data via streaming - but whether “row by row” is fine depends on the spec. File.ReadAllLines loads every line into memory as an array, so it’s not suited to large files⁶. Switching to File.ReadLines (lazy enumeration) or line-by-line reads with StreamReader is only valid when the spec explicitly guarantees no newlines occur inside a field. For RFC 4180-compliant CSV that allows newlines inside quotes, splitting by line will cut a single record in half partway through. In that case, use TextFieldParser reading directly from a file path (which can process the whole thing without loading it all into memory), or a CSV library’s streaming reader.
• Don’t let anything read a file that’s still being written, and don’t read one yourself while it’s mid-write. The output side should write to a temporary filename and then rename it; the ingesting side should confirm exclusivity before reading. This basic pattern for file integration is covered in “The Fundamentals of Mutual Exclusion for File Integration,” and the considerations for folder-watching ingestion are covered in “A Practical Guide to FileSystemWatcher.”
• Plan for columns being added or removed. Explicitly document in the spec whether you’ll map by header column name, or use a fixed column order with a column-count check that rejects mismatches. An implicit magic-number reference like fields[7] will silently break the moment the other party adds a minor column.
• Decide up front how to handle a trailing newline and empty rows. Whether there’s a newline after the final line, and whether to skip or reject empty rows, is a place where behavior diverges by parser implementation. TextFieldParser skips empty rows³.

Summary

CSV wears the face of “just comma-separated values,” but underneath it’s an integration format riddled with dialects. There are four essentials in practice - use a component that handles quoting correctly for reading (don’t hand-roll it with Split); decide the character encoding by working backward from who’s going to open the file (especially Excel), and spell out even the presence of a BOM in the spec; know that Excel’s value reinterpretation (dropped zeros, date conversion) can’t be prevented from the CSV side; and add injection defenses if you’re outputting user input - and doing just this much eliminates most CSV integration incidents at the design stage.

Investigating mojibake or data corruption in an existing CSV integration, clarifying integration specs with a legacy system, and building new CSV import/export functionality often require judgment calls made while looking at the actual files and the other party’s specification, so please reach out if you’re unsure.

Related Articles

• An Introduction to Windows Text Encodings - The Mojibake That Happens When Integrating with Linux
• Windows Text Encodings and Line Endings - The Basics of Mojibake and CRLF/LF
• How to Build Excel Report Output
• Why EXCEL.EXE Processes Remain After C# Excel COM Automation
• The Fundamentals of Mutual Exclusion for File Integration
• A Practical Guide to FileSystemWatcher
• Date, Time, and Timezones in Business Apps
• Pre-Migration Checklist for Moving from .NET Framework to .NET
• A Minimum Security Checklist for Windows App Development

Related Consulting Areas

At KomuraSoft LLC, we handle technical consulting on business application development involving CSV and file integration, root-cause investigation of mojibake and data corruption in existing integrations, and clarifying integration specifications with legacy systems.

• Windows App Development
• Technical Consulting & Design Review
• Contact

References

Related Articles

Recent articles sharing the same tags. Deepen your understanding with closely related topics.

Related Topics

These topic pages place the article in a broader service and decision context.

Where This Topic Connects

This article connects naturally to the following service pages.

Frequently Asked Questions

Common questions about the topic of this article.

What character encoding should I use for CSV files?

The overriding rule is: if the receiving side's requirements are already fixed, match them. If you get to decide from scratch, a safe starting point is to specify UTF-8 with BOM for files a human will open in Excel, and BOM-less UTF-8 for system-to-system integration, and to write that down explicitly in the spec. If a legacy system requires Shift_JIS, agree on how to handle platform-dependent characters and external characters up front, or you'll end up arguing about whose fault the mojibake is later.

Should I bring in a CSV library, or is hand-rolling it fine?

For reading, a hand-rolled String.Split can't correctly handle cases the spec requires (commas, newlines, and quotes inside a field), so you should use some kind of proper component. Within the .NET standard library, TextFieldParser supports RFC 4180-equivalent quote handling. If you also need column-to-object mapping, type conversion, or performance at scale, consider adopting a proven CSV library. Writing is simpler by specification, so hand-rolling is realistic there too - just make sure you funnel all output through a single shared quote-escaping function.

Should I use tab-separated values (TSV) instead of commas?

If your data frequently contains commas (addresses, names, monetary amounts, etc.), switching to TSV reduces how often you need quoting and makes visual inspection easier. That said, tabs and newlines can still appear in the data, so it doesn't eliminate the need for quote handling. Don't assume 'it's TSV, so I don't need to worry about the delimiter.' If you can get agreement from the other party, spelling out the character encoding, quoting rules, line-ending convention, and whether there's a header row in the spec does more to prevent incidents than the choice of delimiter.

If the goal is to have people open the file in Excel, should I output an Excel file instead of CSV?

If the primary purpose is for a human to view or print the file in Excel, it's worth considering xlsx output instead. CSV is a format where leading zeros get dropped and values get reinterpreted as dates the moment Excel opens it, and there is no way to fully prevent this from the CSV side. With xlsx, you can control cell formatting from the output side. On the other hand, if the primary purpose is ingestion by another system and viewing in Excel is just a bonus, it's more realistic to keep the file as CSV and document an import procedure (using the data import feature) as an operational instruction.

Author Profile

Profile page for the article author.

Go Komura

Representative of KomuraSoft LLC

Focused on Windows software development, technical consulting, and investigations into failures that are difficult to reproduce.