Working with CSV: RFC 4180, Turkish Excel, Encodings and CSV Injection

Version note: The code in this post was built and run with .NET 10, CsvHelper 34.0, Sep 0.18, Sylvan.Data.Csv 1.4 and BenchmarkDotNet 0.15.8.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, reads the amounts in its bank collection import like this:

decimal amount = decimal.Parse("1234.50");

The server's culture is tr-TR. What is amount? Do you get an exception, 1234.50, or something else?

Second question: if you split the second data row of the file below with line.Split(','), how many fields do you get?

OrderNumber,CustomerName,Note,Total
BS-0000001,Ada,Gift wrap,349.90
BS-0000002,"Lovelace, Ada","Leave at the door, ring twice",189.50

Third: a customer has entered their name as =1+1. If you write the order export for the accounting team with CsvHelper's default settings, how does that cell end up in the file?

After XML we move to a format that is at least as common but has far fewer rules: CSV. The examples are in the Bookstore.IO.Csv console app and the benchmark project in the series' repo:

dotnet run --project src/Bookstore.IO.Csv -- split
dotnet run -c Release --project benchmarks/Bookstore.IO.Benchmarks -- --filter "*CsvBenchmarks*"

RFC 4180 and Where string.Split Breaks

The definition of CSV that everyone cites, RFC 4180, dates from 2005 and its status is "Informational": it does not set a standard, it documents common practice. In short, it says this: each record is a line ending in CRLF, the line break after the last record is optional, the first line may be an optional header, and every record should have the same number of fields. A field containing the separator, a double quote or a line break is wrapped in double quotes; a double quote inside a quoted field is escaped by writing it twice (""). Spaces are part of the field and are not trimmed.

What it does not say matters more. The separator is only the comma; semicolons, tabs and | are not in the RFC. Its grammar only allows printable ASCII characters; it leaves the encoding to the charset parameter of the MIME type, and there is no way to carry encoding information inside the file. It says nothing about types such as numbers, dates, nulls or booleans: whether 1.234,50 is a number or text is not even a question as far as the RFC is concerned. RFC 7111 in 2014 only added URI fragments for text/csv; it did not fill these gaps. The result: real-world CSV files roughly follow RFC 4180, but every producer speaks its own dialect.

The answer to the second question. In the third order of the sample file the note also contains a line break. First, the code everyone writes first:

foreach (string line in NotesCsv.Split("\r\n", StringSplitOptions.RemoveEmptyEntries).Skip(1))
{
    string[] fields = line.Split(',');
    Console.WriteLine($"  {fields.Length} fields, Total = {fields[^1]}");
}

string.Split:
  4 fields, Total = 349.90
  6 fields, Total = 189.50
  3 fields, Total = "Deliver to ""Building B""
  2 fields, Total = 259.00

The second row was split into six fields because Split does not know about the comma inside the quotes. The third order was split into two "lines" and part of a note was read in the amount column. Because we read the last column here, the amount happened to look right; had you written fields[3], the second order's amount would have been " ring twice". Bugs like this do not show up in tests, because the test data has no customer name with a comma in it.

Reading RFC 4180 correctly needs a character-based state machine rather than a line-based one: are we inside quotes or not. The minimal version looks like this:

public static List<string>? ReadRecord(TextReader reader, char separator = ',')
{
    int c = reader.Read();
    if (c == -1) return null;

    var fields = new List<string>();
    var field = new StringBuilder();
    bool inQuotes = false;
    while (true)
    {
        if (inQuotes)
        {
            if (c == -1) throw new FormatException("Unclosed quote at end of input.");
            if (c == '"')
            {
                // "" inside quotes is one double quote; a single " closes the field.
                if (reader.Peek() == '"') { field.Append('"'); reader.Read(); }
                else inQuotes = false;
            }
            else field.Append((char)c);
        }
        else if (c == '"' && field.Length == 0) inQuotes = true;
        else if (c == separator) { fields.Add(field.ToString()); field.Clear(); }
        else if (c is '\r' or '\n' or -1)
        {
            if (c == '\r' && reader.Peek() == '\n') reader.Read();
            fields.Add(field.ToString());
            return fields;
        }
        else field.Append((char)c);

        c = reader.Read();
    }
}

RFC 4180:
  4 fields, Total = 349.90, Note = Gift wrap
  4 fields, Total = 189.50, Note = Leave at the door, ring twice
  4 fields, Total = 259.00, Note = Deliver to "Building B"\n2nd floor

Three orders, four fields each. When is this code enough? When your own system produces the file, it has a few thousand lines and you do not want to add a NuGet dependency. In a real import, though, header matching, type conversion, tolerance for bad quoting, error messages with line numbers, encodings and performance come on top. At that point a library is cheaper.

Choosing a Library: CsvHelper, Sep and Sylvan

There are three serious options in .NET. We checked their licenses and versions on the day of writing from the NuGet packages themselves:

An important note on CsvHelper: 34.0 is a rewrite. The old CsvConfiguration has been replaced by the immutable CsvReaderOptions/CsvWriterOptions records, the fluent ClassMap by a source generator that writes code at compile time for partial types marked with [CsvRecord], the culture constructor parameter is gone and the default culture is now invariant. Most examples on the internet are for 33 and earlier; if your project uses 33, plan the upgrade with the official migration guide. The code in this post uses 34.

We measured the performance claims ourselves: we read a 100,000-order (6.3 MB) CSV in invariant format from memory and summed the amount column. StringSplit is the method we just saw break; here the data has no quotes, so it gives the right result and serves as the baseline.

[Benchmark]
public decimal CsvHelperRecords()
{
    using var csv = CsvHelper.CsvReader.Create(new MemoryStream(_data), CsvHelper.CsvReaderOptions.Default);
    decimal total = 0;
    foreach (OrderSummaryRow row in csv.GetRecords<OrderSummaryRow>()) total += row.Total;
    return total;
}

[Benchmark]
public decimal Sep()
{
    using var reader = nietras.SeparatedValues.Sep.Reader().From(new MemoryStream(_data));
    int index = reader.Header.IndexOf("Total");
    decimal total = 0;
    foreach (var row in reader) total += row[index].Parse<decimal>();
    return total;
}

| Method           | Orders | Mean     | Error     | StdDev    | Ratio | Gen0      | Gen1    | Allocated   | Alloc Ratio |
|----------------- |------- |---------:|----------:|----------:|------:|----------:|--------:|------------:|------------:|
| StringSplit      | 100000 | 8.867 ms | 0.1773 ms | 0.5058 ms |  1.00 | 5390.6250 | 15.6250 | 44071.22 KB |       1.000 |
| CsvHelperRecords | 100000 | 5.444 ms | 0.0546 ms | 0.0426 ms |  0.62 | 2210.9375 |  7.8125 |  18073.3 KB |       0.410 |
| CsvHelperFields  | 100000 | 1.835 ms | 0.0073 ms | 0.0057 ms |  0.21 |         - |       - |     1.14 KB |       0.000 |
| Sep              | 100000 | 4.305 ms | 0.0115 ms | 0.0107 ms |  0.49 |         - |       - |        4 KB |       0.000 |
| Sylvan           | 100000 | 7.958 ms | 0.1589 ms | 0.4268 ms |  0.90 |         - |       - |    37.65 KB |       0.001 |

In this measurement all three libraries are faster than Split, and as long as we only sum the amount, all three allocate almost nothing; Split allocated 44 MB because it creates a line string, an array and five field strings for every row. The fastest was CsvHelper's field-based API (csv.Read() and csv.Parse<decimal>(4)): version 34 reads the file as UTF-8 bytes and parses numbers directly from those bytes without creating strings. Turning every row into an OrderSummaryRow object with the same library is three times slower and means 18 MB of allocation; the difference comes not from the library but from 100,000 objects and their strings. Sep is roughly twice as fast as Sylvan. Do not read these numbers as a generalization: Sep's own benchmarks may show a different order with other file shapes and other operations. The absolute numbers come from a developer machine. A practical rule: for business files that need type mapping, error reporting and injection protection, CsvHelper; for allocation-sensitive code that scans millions of rows for a few columns, Sep or CsvHelper's field API; if you are going to push the data straight into SQL Server with SqlBulkCopy, Sylvan being a DbDataReader makes your job easier.

The Turkish Reality: Semicolons, Decimal Commas and Culture

In Turkish regional settings the decimal separator is a comma. Because the comma is used inside numbers, the Windows list separator is a semicolon, and Excel splits a CSV opened with a double click by that list separator. That is why most "CSV" files in Turkey coming from a bank, an ERP or an accountant's Excel are actually semicolon separated, with numbers formatted as 1.234,50. In .NET the same information lives in CultureInfo:

ListSeparator tr-TR: ';', invariant: ','
ToString("N2") tr-TR: 1.234,50, invariant: 1,234.50
decimal.Parse("1234.50", tr-TR) = 123450
decimal.Parse("1.234,50", tr-TR) = 1234.50
decimal.Parse("1.234,50", invariant): FormatException: The input string '1.234,50' was not in a correct format.
DateTime.Parse("03.11.2026", tr-TR) = 2026-11-03
DateTime.Parse("03.11.2026", invariant) = 2026-03-11

The answer to the first question is on the third line: no exception, the result is 123450. In the Turkish culture the dot is the thousands separator and decimal.Parse does not check where the thousands separator is. A payment of 1,234.50 TRY silently grows a hundredfold. Dates are just as sneaky: 03.11.2026 is November 3 in the Turkish culture and March 11 in the invariant culture. As long as the month is not above 12, you never get an error.

The rule is simple and has no exceptions: every number and date read from a file is parsed with an explicit IFormatProvider. If the file uses the Turkish format, CultureInfo.GetCultureInfo("tr-TR"); if it is a machine-to-machine format, CultureInfo.InvariantCulture. Relying on the server's culture means the code gives different results on a developer machine and on the server. The console apps in this series set CultureInfo.CurrentCulture to invariant at startup; for dates, give the exact format with ParseExact where you can.

The Bank File That Arrives in Windows-1254

Bookstore's bank produces the daily collection file from an old system: semicolon separated, with Turkish headers, Turkish number and date formats, and encoded in Windows-1254. If we read the first lines assuming UTF-8:

GetEncoding(1254) without provider: No data is available for encoding 1254. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method.
UTF-8 (default): ��lem Tarihi;A��klama;Tutar;Referans
Windows-1254:    İşlem Tarihi;Açıklama;Tutar;Referans
                 02.11.2026;BS-0000001 sipariş ödemesi - LİNUS;1.116,40;TRX00000001

We see two separate problems. First, File.ReadLines assumes UTF-8 when no encoding is given; the 1254 bytes for İ, ş, ç and ı are not valid UTF-8, so they are replaced with � (U+FFFD). No exception, the data is silently corrupted and cannot be recovered. Second, .NET (Core) only ships the UTF family, ASCII and Latin-1; for code pages such as 1254 you have to register CodePagesEncodingProvider once:

// Once, at application startup.
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
Encoding windows1254 = Encoding.GetEncoding(1254);

The provider is part of .NET itself; no separate package is needed. The LİNUS in the bank line is worth a note too: the bank uppercased the name with Turkish rules, with a dotted İ. If you compare that name with the customer record using ToUpperInvariant, it will not match LINUS.

Import: not stopping at a bad row

The shape of the file lives in a type. In CsvHelper 34 the source generator writes the mapping code for a partial class marked with [CsvRecord]; there is no reflection:

[CsvRecord]
public sealed partial class BankPayment
{
    [CsvColumn("İşlem Tarihi"), CsvFormat("dd.MM.yyyy")]
    public DateOnly Date { get; init; }

    [CsvColumn("Açıklama")]
    public string Description { get; init; } = "";

    [CsvColumn("Tutar")]
    public decimal Amount { get; init; }

    [CsvColumn("Referans")]
    public string Reference { get; init; } = "";
}

What should happen if three rows in a thousand-row file are bad? Stopping the whole import means the 997 correct payments are not processed either; orders stay in "awaiting payment" until the accounting team fixes the file by hand in the morning and sends it again. A better strategy: process the good rows and report the bad ones with their line number and raw content. In CsvHelper the place for this is OnRecordError:

var errors = new List<string>();
var options = new CsvReaderOptions
{
    Separator = ';',
    Encoding = Encoding.GetEncoding(1254),
    FormatProvider = Turkish,
    TrackLineNumbers = true,
    // Report the bad row and skip it; the import keeps going.
    OnRecordError = error =>
    {
        long line = (error.Exception as CsvFormatException)?.LineNumber ?? 0;
        errors.Add($"line {line}: {error.Exception.Message} | {error.RawRecord}");
        return true;
    }
};

using var csv = CsvReader.FromFile(path, options);
foreach (BankPayment payment in csv.GetRecords<BankPayment>())
{
    imported++;
    total += payment.Amount;
}

Imported 997 payments, total 872,536.90 TRY, 3 errors
  line 401: Cannot read BankPayment.Amount from field 2 'Tutar' of record 401 (line 401): The input string '1.2O4,50' was not in a correct format. | 02.11.2026;BS-0000400 sipariş ödemesi - LİNUS;1.2O4,50;TRX00000400
  line 601: Cannot read BankPayment.Date from field 0 'İşlem Tarihi' of record 601 (line 601): String '31.11.2026' was not recognized as a valid DateOnly. | 31.11.2026;BS-0000600 sipariş ödemesi - LİNUS;988,40;TRX00000600
  line 801: Cannot read BankPayment.Amount from field 2 'Tutar' of record 801 (line 801): The input string '' was not in a correct format. | 02.11.2026;BS-0000800 sipariş ödemesi - ADA

Three errors, all with a useful message: an amount with a capital letter O instead of a zero, a date that does not exist (the 31st of November, which has 30 days) and a row missing its last two fields. The description on line 250 carried a semicolon inside quotes and was read without trouble. Because Encoding is set, CsvHelper transcodes 1254 to UTF-8 while reading, and thanks to FormatProvider, 1.116,40 is parsed correctly. Returning true from OnRecordError means skip the record; in ReadingExceptionOccurred in 33 and earlier the meaning was the opposite, so watch out when porting old code.

Where the error report goes in a real system is a separate decision: a "rejected rows" file, an e-mail to accounting or a table. What matters is that "997 processed, 3 rejected" is not lost at the end of the import and that the rejected rows can be fixed and sent again.

An Export That Opens Correctly in Excel for Accounting

The other direction: the accounting team opens the order export in Excel with a double click. The file written with CsvHelper's default settings:

First bytes: 53697061
Sipariş No,Müşteri,Tarih,Durum,Tutar
BS-0000001,Linus,01.11.2026 00:00:07,Pending,1116.40
BS-0000002,=1+1,01.11.2026 00:00:14,Pending,559.30
BS-0000003,Linus,01.11.2026 00:00:21,Pending,996.30
BS-0000004,@Grace,01.11.2026 00:00:28,Shipped,1096.00
BS-0000005,Ada,01.11.2026 00:00:35,Pending,1758.20

A file that follows RFC 4180, but it has three problems for an Excel with Turkish regional settings. Because the separator is a comma, Excel puts each line in a single cell. Because the amounts use a dot, they are not recognized as numbers. Because there is no BOM at the start of the file, Excel does not recognize UTF-8 and reads the file with the Windows ANSI code page; the ş and ü in the header look broken. Three settings fix it:

// The format Excel opens correctly with Turkish regional settings.
public static readonly CsvWriterOptions ExcelTurkish = new()
{
    Separator = ';',
    FormatProvider = CultureInfo.GetCultureInfo("tr-TR"),
    WriteByteOrderMark = true,
    Injection = CsvInjection.Escape
};

First bytes: EFBBBF53
Sipariş No;Müşteri;Tarih;Durum;Tutar
BS-0000001;Linus;01.11.2026 00:00:07;Pending;1116,40
BS-0000002;"'=1+1";01.11.2026 00:00:14;Pending;559,30
BS-0000003;Linus;01.11.2026 00:00:21;Pending;996,30
BS-0000004;"'@Grace";01.11.2026 00:00:28;Shipped;1096,00
BS-0000005;Ada;01.11.2026 00:00:35;Pending;1758,20

EF BB BF is the UTF-8 BOM. The amounts now have a decimal comma and no thousands separator; leaving out the thousands separator reduces confusion when the file is read by another tool. This file has a price: it is now only right for Excel with Turkish settings. If you send the same export to another system, a data warehouse or a business partner abroad, produce a separate "machine format" with the invariant culture and commas. One file is not enough for two consumers.

Some systems write sep=; on the first line of the file to tell Excel the separator. It works in Excel, but for other CSV readers that line is data; CsvHelper's reader can recognize it with DetectSeparatorHint. If you do not control the consumer, a BOM and the right separator cause fewer surprises.

CSV Injection: Keeping a Cell from Becoming a Formula

The answer to the third question is in the first export: CsvHelper 34's default is CsvInjection.None, so =1+1 is written to the file as is. Excel or another spreadsheet evaluates that cell as a formula when it opens the file. Any text field the user writes, such as a customer name, a book review or an address note, can turn into a formula on the accountant's computer this way. The details of the attack are on OWASP's CSV Injection page; here we only look at the defensive side.

OWASP recommends neutralizing =, +, -, @, tab and line break characters at the start of a cell: wrap the field in double quotes, put a single quote in front of it and escape the double quotes inside. Excel shows a cell that starts with a single quote as text. CsvInjection.Escape did exactly that in the second output: "'=1+1". The setting applies only to text fields, so a negative refund amount such as -149,90 stays a number.

There are three limitations to know about. First, CsvHelper's default character list is = @ + -, tab and CR; LF and the full-width Unicode variants (such as ＝ and ＋) from OWASP's list are not in it, and the list only accepts ASCII characters. If you want to cover those variants as well, add your own check before the export. Second, when Excel opens the file and saves it again as CSV, the escape characters can be lost and the formula can become active again. Third, the single quote stays in the data: if a program reads the file, it has to strip that quote. That is why you should turn on injection protection only for exports that people will open in a spreadsheet; the machine-format file should carry the data as is. The general treatment of injection and of input/output encoding principles is in the A05: Injection posts of the OWASP Top 10 series.

In Production

Document the dialect and pin it in code. A "bank file" is a contract: separator, encoding, number and date formats, header names. Write all of them explicitly in CsvReaderOptions and in the [CsvRecord] type; CsvHelper's automatic separator detection is good for exploration, but in production use the contract instead of a guess.

Set size and record limits. An unclosed quote tries to turn the rest of the file into a single field. CsvHelper 34's MaxRecordSize setting exists for exactly this; set an upper limit for files coming from users and check the total file size at upload time.

Make the import idempotent. The bank may send the same file twice, or accounting may upload a corrected file again. Use each row's Referans value as a unique key and skip references that have already been processed.

Choose a library by measuring. In an import that processes a few files per second, the difference between CsvHelper and Sep is negligible; in a job that processes gigabytes of data every night, it matters. We will cover processing large files with constant memory in the series' upcoming post on large files.

Common Pitfalls

Parsing with string.Split(','). It works until a customer name with a comma or a multi-line note arrives. Even for your own files, use at least a reader that knows about quotes.

Parsing numbers and dates without a culture. decimal.Parse("1234.50") returns 123450 in the Turkish culture without an exception. Always use an explicit IFormatProvider.

Assuming the encoding. Reading a non-UTF-8 file as UTF-8 corrupts the data beyond repair. The file's encoding is part of the contract; if you do not know it, look at the bytes of a sample file before you see �.

Stopping the whole import at a bad row. A one-row error should not hold back hundreds of good records. Skip, report, continue; but if the error rate passes a threshold (the file's format may have changed), stopping the import is the right decision too.

Forgetting Unescape in Sep. For performance, Sep does not unescape quotes by default; a quoted field returns its value together with the quotes. For files that contain quotes, set SepReaderOptions.Unescape = true.

A Small Experiment

★☆☆ Give Rfc4180.ReadRecord a field with text after the closing quote, such as "ab"c. What does it return? How does CsvHelper handle the same input with MalformedData = CsvMalformedDataHandling.Throw?

★★☆ Add a threshold to the bank import: if more than 5 percent of the rows are bad, stop the import and process none of the rows. Hint: instead of reading the file twice, collect the valid records in a list first.

★★★ Add your own protection step to the export that also covers the full-width characters from OWASP's list (＝, ＋, －, ＠) and write a test for it together with CsvInjection.Escape. Verify that negative amounts stay numbers.

That wraps up CSV. The next post moves to what carries most of these files in real life: compression and archives. We will look at the differences between GZip, Deflate, ZLib and Brotli, .NET 10's async ZIP APIs, streaming the "all invoices" zip to the response without writing a file to disk, and safely extracting an uploaded zip against Zip Slip.