Reading and writing CSV files in code

Language cheat sheet

When you handle Japanese CSV files in code, the encoding is the biggest hurdle. In every language the rule is the same: state the encoding explicitly.

Python (standard csv module)

import csv

# Read a Shift-JIS CSV. Forget newline="" and you get doubled line breaks
with open("sales.csv", encoding="cp932", newline="") as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row["product_name"], row["sales"])

The official docs for DictReader explain that it turns the first row into dictionary keys, so your code keeps working even if the column order changes. Splitting on split(',') yourself will shift columns whenever a comma appears inside quotes, which is why you should always use the csv module.

Python (pandas)

import pandas as pd

df = pd.read_csv("sales.csv", encoding="cp932", dtype={"product_code": str})
df.to_csv("out.csv", index=False, encoding="utf-8-sig")

The main read_csv options, limited to the ones you actually use at work:

Excel VBA

Sub ReadCsv()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)

    With ws.QueryTables.Add(Connection:="TEXT;C:\data\sales.csv", Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFilePlatform = 65001   ' UTF-8. Use 932 for Shift-JIS
        .TextFileColumnDataTypes = Array(2, 1, 1)  ' Column 1 as text
        .Refresh
    End With
End Sub

QueryTables.Add (Microsoft Learn) lets you control both the encoding and the column types, which makes it more reliable than Workbooks.OpenText. The TextFilePlatform values are 65001 for UTF-8 and 932 for Shift-JIS.

C#

// On .NET Core / .NET 5+ register the CodePages provider first
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

using var parser = new TextFieldParser(@"C:\data\sales.csv", Encoding.GetEncoding(932));
parser.SetDelimiters(",");
parser.HasFieldsEnclosedInQuotes = true;

while (!parser.EndOfData)
{
    string[] fields = parser.ReadFields()!;
    Console.WriteLine(string.Join(" / ", fields));
}

TextFieldParser (Microsoft.VisualBasic.FileIO) handles commas inside quoted fields correctly. To use Encoding.GetEncoding(932) on .NET Core or later you need a reference to the System.Text.Encoding.CodePages package and the provider registration above. In practice many teams use CsvHelper (official site) instead.

Writing CSV (exporting)

• pandas — df.to_csv(path, index=False, encoding="utf-8-sig"). Forget index=False and you get an extra row-number column (docs).
• Python standard library — csv.writer with writerow(). Convert numbers to strings before passing them in (docs).
• VBA — SaveAs FileFormat:=xlCSVUTF8 writes UTF-8.
• C# — WriteRecords() from CsvHelper is the usual choice.

After exporting, always open the file once in a text editor and check the column count, the quoting and the encoding before you hand it over.

Encoding cheat sheet

The detailed repair procedure is on the dedicated encoding page.

Frequently asked questions