Menu

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

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

DEV Community: dotnet·lxb dev·6 days ago
#BcDcjyyb
#devtodotnet#devto#dev#file#bytes
Reading 0:00
15s threshold

"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:

Check for a BOM. If there is one, trust it.

Try decoding as strict UTF-8, so invalid bytes throw an error instead of being silently replaced.

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 .…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.