Merging CSV and Excel files without breaking them: encodings, leading zeros, and header drift (.NET notes)

"Just concatenate the files" works until the first real-world export shows up. When I was building a small desktop tool in .NET/WPF that merges and splits spreadsheets, three problems caused most of the bugs. Here they are, with the fix I ended up using for each.

1. "CSV" is not one encoding

In practice you'll see at least:

• UTF-8
• UTF-8 with a BOM (common from Excel's "CSV UTF-8" option)
• GBK / GB18030 (still very common in exports from Chinese finance and e-commerce systems)

Open a GBK file as UTF-8 and every Chinese name turns into garbage. A simple detection order that works well:

1. Check for a BOM. If there is one, trust it.
2. Try decoding as strict UTF-8, so invalid bytes throw an error instead of being silently replaced.
3. If that fails, fall back to GB18030 (a superset of GBK).

// .NET Core / .NET 5+: legacy code pages need this once at startup
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

static Encoding DetectEncoding(byte[] bytes)
{
    if (bytes.Length >= 3 && bytes[0] == 0xEF && bytes[1] == 0xBB && bytes[2] == 0xBF)
        return new UTF8Encoding(true);

    try
    {
        new UTF8Encoding(false, throwOnInvalidBytes: true).GetString(bytes);
        return new UTF8Encoding(false);
    }
    catch (DecoderFallbackException)
    {
        return Encoding.GetEncoding("GB18030");
    }
}

Plain ASCII is valid UTF-8, so step 2 won't misdetect it. Still give users a manual override, because detection can guess wrong on short files.

2. Leading zeros are data

Employee IDs, phone numbers, postal codes, and some order numbers look like numbers but aren't. Once 00123 becomes 123, lookups and joins quietly fail.

Rules that saved me:

• Read every CSV cell as a string. Don't infer types during the merge.
• When writing .xlsx, write ID-like columns as text cells, not numbers.
• In a preview grid, show the raw string, so users can spot damage before they export.

3. Header drift

File A has Dept, file B has Department, and file C added CostCenter halfway through the year. You need to pick a strategy on purpose:

• Align to the first file: the output keeps file 1's columns. Extra columns in later files are dropped, and it's worth reporting which ones.
• Union of headers: keep every column you see and leave missing values empty. You lose nothing, but you get sparse columns.

Whichever you choose, trim whitespace and compare headers case-insensitively, because Name and name are a classic trap.

Bonus: splitting

Splitting one sheet into one file per value of a column is mostly about file names: strip characters Windows doesn't allow (\ / : * ? " < > |), handle empty values (for example, a _blank file), and watch out for values that differ only by case. Windows treats Shanghai.xlsx and shanghai.xlsx as the same file.

---

How do you handle header drift? Do you normalize headers in a script first, or keep a mapping table?

Note: this post was written with AI assistance and reviewed by me.