How to merge Shift_JIS, UTF-8 BOM, and Windows-1252 CSVs without guessing the encoding

ebhow — Thu, 12 Nov 2026 07:41:17 +0000

How to merge Shift_JIS, UTF-8 BOM, and Windows-1252 CSVs without guessing the encoding

A shop export from Japan, another from the UK, a third from a US system, and a Chinese marketplace file. You want one table. pandas.read_csv() with the default UTF-8 encoding fails on three of the four files. Dates arrive as 2026/3/2, 20260310, 2026年3月14日, and 3/15/2026. Two rows are exact duplicates, one row is empty, and one amount is written in full-width digits (２４９).

This article walks through that exact problem using sheetstack-lite, a small MIT-licensed Python tool. Every command below was run against the repository's fictional sample files. The output is copied from those runs, not reconstructed from memory.

sheetstack-lite is a free, in-memory merge-and-clean utility. It is not a tax tool, and this article does not claim compatibility with Google Sheets or WPS.

What the sample files actually contain

Clone the repo and install the two dependencies (pandas and openpyxl). Python 3.10 or later is required.

git clone https://github.com/ebhow9986-ctrl/sheetstack-lite.git
cd sheetstack-lite
python3 -m venv .venv
source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt

The samples/ folder ships with five fictional CSVs (none of the rows are real customer records):

File	What the tool reported	What is messy about it
`store_us.csv`	`utf-8-sig` (UTF-8 with a BOM)	Mixed date formats, a blank row, a duplicate `US-1002`
`store_uk.csv`	`cp1252`	The word “Café” is a single `0xE9` byte; one date is `3/6/2026`; `UK-2003` is duplicated
`store_jp.csv`	`shift_jis`	Japanese headers (`伝票番号`, `日付`, `商品名`…); dates such as `2026年3月14日` and `20260310`; `JP-3005` is duplicated
`store_cn.csv`	`gb18030`	Chinese headers; a full-width amount `２４９`; a date written as `2026年3月13日`
`customers_messy.csv`	`utf-8-sig`	Duplicate ids, an empty row, mixed dates, a full-width `０８` in the points column

The README describes the four store files as “GBK” for the Chinese export. When the CLI sniffed the same file, it printed gb18030 (a superset of GBK). The rest of this article uses the string the tool actually printed.

Why a default pandas read fails

On the same machine, pd.read_csv(path) with no encoding= argument succeeded only for the UTF-8 BOM file. The other three store exports raised UnicodeDecodeError:

store_cn.csv: 'utf-8' codec can't decode byte 0xb6 in position 0: invalid start byte
store_jp.csv: 'utf-8' codec can't decode byte 0x93 in position 0: invalid start byte
store_uk.csv: 'utf-8' codec can't decode byte 0xe9 in position 67: invalid continuation byte

That last one is the é in “Café”. If you force latin-1 or cp1252 onto the Japanese file, the read may “succeed” and give you mojibake instead of an error. Encoding has to be chosen per file, then the headers have to be lined up, then the dates and duplicates have to be cleaned. Doing that by hand for every export is the tedious part.

This is a well-worn problem — the Stack Overflow thread “UnicodeDecodeError when reading CSV file in Pandas” has been viewed more than a million times. The usual advice is “pass encoding=...”. That works when you already know the encoding and you only have one file. It does not merge four different code pages, or rename 伝票番号 and Order ID into the same column.

Merge the four store exports

The tool is invoked as python3 sheetstack_lite.py. Two subcommands exist: merge and clean.

Point merge at the four store files, ask it to normalize header aliases, and write a single CSV:

python3 sheetstack_lite.py merge \
  samples/store_us.csv \
  samples/store_uk.csv \
  samples/store_jp.csv \
  samples/store_cn.csv \
  -o stores.csv \
  --normalize-headers

Exact output from the run:

Merged 4 file(s), 21 rows, 7 columns
  - store_us.csv: 7 rows / encoding utf-8-sig
  - store_uk.csv: 5 rows / encoding cp1252
  - store_jp.csv: 6 rows / encoding shift_jis
  - store_cn.csv: 3 rows / encoding gb18030
Wrote stores.csv

--normalize-headers maps common aliases onto one English name. In these samples that means, among others:

Order ID / 伝票番号 / 订单编号 → order_id
Date / 日付 / 成交时间 → order_date
Item / 商品名 / 商品名称 → product
Quantity / 数量 / 件数 → qty
Sales / 売上 / 销售额 → amount
Remaining / 在庫 / 库存数 → stock

A source_file column is added unless you pass --no-source. The merged file still has 21 rows, including the blank US row and the three duplicate orders. Dates are not rewritten during merge. “Café mug”, スマホスタンド, and 键盘 all sit in the same product column, and the file is written as UTF-8 with a BOM (utf-8-sig) so Excel on Windows will not treat the CJK text as mojibake.

If you follow the README literally and run python3 sheetstack_lite.py merge samples -o merged.csv --normalize-headers, the tool walks the whole folder. That includes customers_messy.csv, which has a different schema. In that run the CLI reported Merged 5 file(s), 29 rows, 12 columns. For a store-export merge, name the four files (or keep customers in a separate folder).

Clean dates, drop the empty row, remove duplicates

python3 sheetstack_lite.py clean stores.csv \
  -o stores_clean.csv \
  --date-cols order_date \
  --dedupe-cols order_id

Exact output:

Cleaned 21 rows → 17 rows (empty 1, duplicates 3)
Detected encoding: utf-8-sig
Wrote stores_clean.csv

The 17 remaining order_id values are unique: US-1001 through US-1005, UK-2001 through UK-2004, JP-3001 through JP-3005, and CN-4001 through CN-4003. Every order_date is YYYY-MM-DD. A few examples of what changed:

Input (`order_date`)	Output
`2026/3/2`	`2026-03-02`
`20260305` / `20260310` / `20260318`	`2026-03-05` / `2026-03-10` / `2026-03-18`
`2026.3.9`	`2026-03-09`
`3/15/2026`	`2026-03-15`
`2026年3月14日` / `2026年3月13日`	`2026-03-14` / `2026-03-13`

Cells are trimmed, and full-width digits are folded to ASCII, so the Chinese amount ２４９ becomes 249. Duplicate keys keep the first row (US-1002, UK-2003, JP-3005).

Ambiguous dates: `3/6/2026` is not obvious

UK-2002 is dated 3/6/2026. Without --dayfirst, the cleaner treated that as 6 March (2026-03-06). With --dayfirst, the same value became 3 June (2026-06-03):

python3 sheetstack_lite.py clean stores.csv \
  -o stores_clean_dayfirst.csv \
  --date-cols order_date \
  --dedupe-cols order_id \
  --dayfirst

Unambiguous values did not flip: 2026.3.9 stayed 2026-03-09, and 3/15/2026 stayed 2026-03-15 (there is no 15th month). If a file is known to be day-first, pass the flag. If a folder mixes US and UK exports, inspect the ambiguous rows before you trust a single convention. sheetstack-lite does not invent a locale per source file during clean; it applies one rule to the column you name.

A second, smaller clean: messy customer dates and duplicates

The customer list is a better illustration of empty rows and full-width numbers mixed with several date styles:

python3 sheetstack_lite.py clean samples/customers_messy.csv \
  -o customers.csv \
  --dedupe-cols customer_id \
  --date-cols signup_date

Exact output:

Cleaned 8 rows → 5 rows (empty 1, duplicates 2)
Detected encoding: utf-8-sig
Wrote customers.csv

The cleaned table is:

customer_id  signup_date  city      notes            points
U001         2026-02-01   Tokyo     paid             120
U002         2026-02-03   Osaka     pending          08
U003         2026-02-08   Nagoya    follow-up        56
U004         2026-02-11   Fukuoka   shipped          230
U005         2026-02-15   Sapporo   address needed   15

2026/2/1, 20260203, 2026年2月8日, 2026-02-11, and 2/15/2026 all became YYYY-MM-DD. The full-width points value ０８ became 08. Leading and trailing spaces (including a full-width space after Nagoya) were stripped. The second U001 and the second U003 were dropped; the blank row was dropped.

One thing not to do: after merging unrelated schemas, do not dedupe on a column that is empty for some of the rows. In a trial clean of the five-file merge, --dedupe-cols order_id treated every customer row (blank order_id) as a duplicate of the first blank key and deleted most of the customer data. Dedupe on a key that actually identifies the row, or clean each kind of file on its own.

What the free tool does not do

sheetstack-lite version 1.0.0-lite loads each file into memory. It will not help if Excel already refuses to open the file, and it has no chunked reads, no split-into-pieces command, and no Excel summary workbook. The tests in the repo (python3 tests/test_lite.py) cover the README examples; they passed on the same machine used for this article.

If you only need to repair a file's encoding and would rather not use the command line, there is a free CSV encoding fixer that runs entirely in the browser. The file never leaves your computer.

If you later need chunked reads for large CSVs, a split into Excel-friendly pieces, an Excel summary with conditional formatting, or a one-step run pipeline, those are in the paid toolkit (Sheetstack on Gumroad). The free repository does not include that source, and the two editions have different licenses.

For the everyday case — a handful of exports, mixed encodings, messy dates, a few duplicates — the commands above are enough, and they stay on your machine.

DEV Community: ebhow

How to merge Shift_JIS, UTF-8 BOM, and Windows-1252 CSVs without guessing the encoding

How to merge Shift_JIS, UTF-8 BOM, and Windows-1252 CSVs without guessing the encoding

What the sample files actually contain

Why a default pandas read fails

Merge the four store exports

Clean dates, drop the empty row, remove duplicates

Ambiguous dates: 3/6/2026 is not obvious

A second, smaller clean: messy customer dates and duplicates

What the free tool does not do

Ambiguous dates: `3/6/2026` is not obvious