Distributing CSVs is difficult: Key design points for business data handoffs

"When the client opened the CSV we delivered last week in Excel, they contacted us saying all product names were completely garbled," "In the shipping instruction CSV, the product code 007 became 7, causing an incident where a completely different product was shipped"—when building data export or import features in custom development, notifications like these almost always arrive after delivery. A file that opened without issue in the development environment breaks effortlessly in the recipient's environment. While CSV is often thought of as "plain text simply separated by commas," the moment it becomes a "CSV handed over to someone else," it turns into a minefield where character encodings, line breaks, escaping, and Excel's arbitrary conversions collide.

The tricky part is that these issues almost never reproduce during development. The problem lies in our inability to control what the recipient uses to open the file. In this article, we outline where CSV issues occur when building export and import features in custom development, how to agree on specifications, and at what point you should abandon CSV, framing them as practical decision criteria.

Opening in Excel Silently Rewrites CSV Files

The single biggest source of incidents is the recipient opening the CSV in Excel. When a CSV is opened via double-click, Excel "cleverly" converts values without verifying the user's intent. This behavior is responsible for the vast majority of accidents.

A classic example is the loss of leading zeros. For strings with leading zeros such as 007 or the postal code 0150001, Excel treats them as numeric values and strips the leading zeros. Data like product codes, phone numbers, and postal codes—which look like numbers but are actually strings—are thereby completely corrupted into different values. Similarly, long numbers are converted into exponential notation like 1.23457E+12, values like 1-2 and 2026/3/5 are automatically converted into dates, and values beginning with = are interpreted as formulas.

Treating values that begin with = as formulas goes beyond visual discrepancies. If an attacker injects a string like =cmd|... into an input field and it gets exported directly into a CSV, it risks executing the moment the recipient opens it in Excel—a vulnerability known as CSV injection. Cybozu's Bug Bounty Guidelines explicitly note that cells beginning with =, +, -, and @ are interpreted as formulas, representing a risk that cannot be ignored in features that output user input to CSV.

# 危険: ユーザーが氏名欄に入力した値をそのまま出力
name,memo
=1+1,テスト
+44-20-...,海外連絡先

# 対策: 式の起点になる文字で始まるセルの先頭に ' を付与してエスケープ
name,memo
'=1+1,テスト
'+44-20-...,海外連絡先

Developers building export features must identify "columns that look numeric but need to be treated as strings" and "columns containing user input," protecting them with escaping and quotes upon output. If you rely on getting the recipient to change their Excel configuration, it will inevitably fall through the cracks somewhere.

Character Encodings and Line Breaks — Where "It Opened Fine on My Machine" Fails

The next source of trouble is character encoding. This is the most common problem with Japanese CSV files, and its cause comes down to how Excel works. When a CSV is opened by double-clicking it, Excel for Windows assumes that the file uses Shift_JIS (CP932). As a result, opening a CSV exported as UTF-8 without a BOM causes the Japanese text to become garbled and unreadable. Exporting it as UTF-8 with a BOM lets Excel open it correctly, but import routines in other systems that do not expect a BOM may read the first three bytes (EF BB BF), corrupting the first column header.

In short, "opening correctly in Excel" and "importing cleanly into external systems" can be mutually incompatible at the character encoding level. The right answer depends on where the output is destined.

Line endings follow the exact same pattern. RFC 4180 specifies CRLF for line breaks, and using CRLF will pass safely through most runtimes; however, systems that only support LF also exist in production, making this entirely dependent on the recipient as well.

Character encodings and line endings are classic examples of issues that do not reproduce on a developer's local machine. When your development machine is macOS and you use UTF-8 viewing tools, what happens in Windows Excel under Shift_JIS assumptions remains invisible. This is precisely why delivering samples early, as discussed below, is so effective.

RFC 4180 — Escaping Commas, Line Breaks, and Quotes

CSV has an international standard in RFC 4180, and anyone building an export feature should understand it. The core principle is straightforward: if a field contains a comma, a line break (CRLF), or a double quote, the entire field must be enclosed in double quotes, and any double quotes within the field must be escaped by doubling them.

# 壊れるCSV: 住所にカンマ、備考に改行とダブルクォートが入っている
id,address,memo
1,東京都港区1-2,3,担当は"鈴木"さん
改行も入っている

# RFC 4180 準拠: カンマ・改行・クォートを含むフィールドを "" で囲み、" は "" にエスケープ
id,address,memo
1,"東京都港区1-2,3","担当は""鈴木""さん
改行も入っている"

When you assemble CSVs through ad-hoc string concatenation, you inevitably forget this escaping. A comma inside an address misaligns columns, a line break inside a notes field splits a single record into two rows, and the recipient's import process silently breaks. The golden rule is to rely on standard language libraries or dedicated CSV writers rather than manual String.join(",") string concatenation. On the consuming side as well, relying on simple split(",") splitting will reliably fail whenever a quoted comma appears. The challenge of passing data reliably between systems is directly tied to the "integration resilience" discussed in our article on system integrations via CDC (Change Data Capture).

The representation of null values also requires alignment. Does an empty string "" mean NULL, or is NULL represented as a string like \N? If you deliver files without deciding this, the recipient may arbitrarily coerce empty fields into zeros or empty strings, skewing financial amounts or item counts.

The Moment Columns Change, the Receiver Silently Breaks

Another hazard of CSV is that the schema is not embedded within the payload. The assumption that "column 3 is the unit price" exists only in the header row label or in verbal agreements. Consequently, even if you add a column out of goodwill, a receiving system that parses by column index will silently shift and break. Even when values flow into columns of the wrong data type, CSV raises no warnings.

Header contracts and schema agreements prevent this. Column names, order, data types, required/optional status, character encoding, line breaks, and escaping rules must be agreed upon in writing before handing over data. Furthermore, if the format may change, include a version identifier in the filename or header (export_v2_20260626.csv, etc.). Doing so allows the receiving system to branch logic—"use parser v1 for v1, parser v2 for v2"—ensuring that adding a column does not immediately cause an incident. How master data attributes are defined and typed ties directly to the architectural principles covered in our article on master data design for business systems.

Additionally, memory issues with massive CSV files cannot be overlooked. Implementations that load millions of rows entirely into memory will easily fail at production scale, so both export and ingestion should be designed around streaming row by row.

A Custom Development Story: How a Shipping CSV Turned into an Incident for an Apparel Wholesaler

Here is a concrete example. We were commissioned by a mid-sized apparel wholesaler (company name withheld) to build an integration feature that exports shipping instruction CSVs from their core system to be ingested into their partner warehouse company's WMS (Warehouse Management System). In the initial delivery, core system data was exported directly in UTF-8 without BOM. When the warehouse staff opened the file in Excel to review its contents, all product names turned into garbled characters, and leading zeros in SKU codes such as 0078-0042 were stripped, colliding with codes for completely different items. This was discovered right before physical shipping, narrowly preventing a misdelivery incident.

Two problems had overlapped. The warehouse staff's workflow involved visual inspection in Excel after receiving the file, which caused UTF-8 without BOM to corrupt. In addition, SKU leading zeros were stripped due to Excel's automated conversions. The solution we took was to deliver a single-record sample CSV first, having them open it in both their WMS and their Excel before beginning core implementation. At the sample stage, the discrepancy became apparent: the WMS could ingest UTF-8 without BOM properly, but opening it in Excel for visual inspection caused garbled text.

Ultimately, we exported the authoritative CSV for WMS ingestion in UTF-8 without BOM, escaped string columns like SKUs to prevent zero-stripping, locked column names, order, types, and null handling into a single-page specification header contract, and attached version numbers to filenames. As a result, subsequent additions of new warehouse partners could be rolled out using the same contract, reducing inquiries about garbled text and dropped zeros to zero. What made the biggest difference was not technical cleverness, but eliminating the unknown of "what the recipient uses to open the file" upfront with a sample.

Keeping the Option to Abandon CSV on the Table

Finally, in custom development, proposing when to "abandon CSV" provides just as much value as "building CSVs correctly." While CSV is human-readable and can be opened anywhere, it carries neither types nor schemas, making escaping and encoding accidents structurally inevitable.

If the recipient only processes data programmatically, your options broaden significantly. JSON Lines (one JSON object per line) allows line-by-line streaming, represents types and nesting, and frees you from encoding and escaping nightmares. For analytical workloads or large volumes, columnar formats like Parquet excel, with comparisons demonstrating file sizes a fraction of CSV and query speeds orders of magnitude faster. When real-time capability or bidirectional interaction is required, stop distributing files and transition to API integrations. If the recipient is a non-engineer who simply wants to inspect small volumes visually, sharing a Google Sheet eliminates any worry of garbled characters—an area that pairs naturally with the collaborative workflows discussed in our article on Google Sheets automation.

The right answer depends on the recipient. In custom development, identifying upfront whether the recipient is "a human opening Excel," "a machine running automated scheduled imports," or "an analyst crunching large datasets"—and deciding whether to refine the CSV or switch to an alternate format or API—eliminates downstream incidents entirely.

If your data export feature produces garbled text for clients, import routines silently break on specific files, maintaining slightly different CSV specifications across business partners is draining your team, or you want guidance on whether to keep relying on CSV at all—please reach out through the GleamHub contact form. Starting from how the recipient opens the data, we will work with you to build a resilient data handoff setup, covering character encoding and escaping design, header contracts and versioning, as well as evaluating whether to stick with CSV or migrate to an alternate integration method.

Sources

• Re-Reading RFC 4180, the International CSV Standard, in Retrospect － Monex Engineer Blog
• Guidelines for Preventing CSV Injection － cybozu/bugbounty
• Mastering BOM Means Mastering UTF-8! Completely Understanding Excel Garbled Text, the Mysterious Leading 3 Bytes, and the Root Causes of Broken CSVs － Qiita
• Recommended CSV Practices Extending Beyond RFC 4180 － Router Co., Ltd.
• Let's Use Parquet Instead of CSV! － Iprio Engineers