Sales CSVs in different formats into one monthly Excel report

Reads the sales CSVs exported from the registers of three stores and produces one monthly Excel report. Differences in encoding, column names, and date formats are reconciled on import, and problem rows are listed on a Review sheet with the file name and row number.

Input: One month of sales CSVs per store, plus a product master
Output: One Excel file with 5 sheets
Checks: 7 kinds, including negative quantities, amount mismatches, and duplicate rows

The CSVs it read

September sales of three fictitious variety stores. Each store is assumed to use a different register, so the files don't line up.

Main store　Shift_JIS　honten_202609.csv

日付,商品コード,商品名,数量,単価,金額,区分
2026/08/31,2000000000053,布巾 3枚組,2,550,1100,売上
2026/09/01,2000000000015,ブレンドコーヒー豆 200g,3,1280,3840,売上
2026/09/01,2000000000091,紅茶ティーバッグ 20袋,6,760,4560,売上
…

Station store　UTF-8 with BOM　ekimae_202609.csv

売上日,JAN,品名,個数,販売単価,金額,取引区分
20260901,2000000000084,キャンバストート,4,2200,8800,通常
20260901,2000000000046,ステンレスボトル 350ml,7,2232,15624,通常
20260901,2000000000114,アロマキャンドル,2,1320,2640,通常
…

Suburban store　Shift_JIS　kogai_202609.csv

date,商品コード,数量,単価,金額
令和8年9月1日,2000000000039,5,"1,650","8,250"
令和8年9月1日,2000000000046,5,"2,232","11,160"
令和8年9月1日,2000000000077,2,880,"1,760"
…

	Main store	Station store	Suburban store
Encoding	Shift_JIS	UTF-8 with BOM	Shift_JIS
Date column	日付 (date)	売上日 (sales date)	date
Product column	商品コード (product code)	JAN	商品コード (product code)
Qty and price columns	数量・単価	個数・販売単価	数量・単価
Date format	2026/09/01	20260901	令和8年9月1日 (Japanese era)
How returns are marked	区分 = 返品	取引区分 = 返品	No column for it

The Excel it produced

Built from the three files above: net sales of ¥2,753,347 (after returns), with 11 rows flagged for review. The tables below are translated from the Japanese output.

Store × month sheet

Month	Store	Net sales	Returns	Units	Rows	Flagged
2026-09	Main store	1,170,986	-1,280	850	172	4
2026-09	Station store	854,537	-2,480	661	178	3
2026-09	Suburban store	727,824	0	532	159	4
2026-09	Total	2,753,347	-3,760	2,043	509	11

Returns for the suburban store are 0 because its CSV has no row-type column, so returns cannot be identified. Its negative rows are flagged instead.

Review sheet

No	Type	File	Row	Qty	Unit price	Amount	Details	In totals
1	Date outside the month	honten_202609.csv	2	2	550	1,100	The date is outside the target month (2026-09). Excluded from totals.	Excluded
2	Negative quantity	honten_202609.csv	63	-2	1,650	-3,300	The quantity is negative, but the row type is 売上 (sale), not 返品 (return).	Included
3	Amount mismatch	honten_202609.csv	91	3	980	2,490	Qty × unit price = 2,940, but the amount is 2,490 (difference -450).	Included
4	Duplicate row	honten_202609.csv	127	6	1,650	9,900	Same as row 126.	Included
5	Unit price far off	ekimae_202609.csv	87	2	198	396	¥198 against the standard price of ¥1,980 (-90%), beyond ±50%.	Included
6	Unknown product code	ekimae_202609.csv	109	1	300	300	The product code is not in the product master.	Included
7	Date outside the month	ekimae_202609.csv	180	4	760	3,040	The date is outside the target month (2026-09). Excluded from totals.	Excluded
8	Negative quantity	kogai_202609.csv	22	-1	880	-880	The quantity is negative. This file has no row-type column, so it cannot tell whether this is a return.	Included
9	Duplicate row	kogai_202609.csv	74	5	1,280	6,400	Same as row 73.	Included
10	Amount mismatch	kogai_202609.csv	112	2	1,480	29,600	Qty × unit price = 2,960, but the amount is 29,600 (difference +26,640).	Included
11	Unreadable	kogai_202609.csv	161	1	690	690	Cannot read the date 「令和8年9月31日」.	Excluded

The store, date, and product code columns are omitted. The sample CSVs contain these 11 rows on purpose, along with rows that must not be flagged (rows marked as returns, and a row at exactly half price). Tests check that exactly these 11 are flagged. Row 11 is flagged because September 31 does not exist.

Other sheets

Product ranking (商品ランキング): By net sales, with share and a breakdown by store
Daily trend (日別推移): Sales per day, by store and in total
Files read (読込ファイル): Files read, the encoding detected, row counts, and rows that could not be read

Download this Excel file (original, in Japanese)12KB

How it was built with Claude Code

This tool was built by Claude Code, an AI coding tool, from a single set of instructions that laid out the specification. The work went in this order:

Write up the request.
List what the request leaves undecided as questions, and set assumptions.
Configure what Claude Code must respect (files it may not read, commands it may run).
Create sample CSVs with deliberately bad rows.
Write the tests before the code, and confirm they fail.
Write the code until the tests pass.
Run it for real and read through the Excel output. For each problem found, add a test first, then fix it.
Review the code, and fix what turns up the same way.
List what a person still needs to check.

After that, a different AI (OpenAI Codex) reviewed the code in read-only mode. Of its 8 findings, 7 were fixed, each by first adding a test and confirming it failed. They included double counting caused by duplicate store names, and an unclosed quote that made the rows after it disappear. The one left as is: numbers longer than 17 digits lose precision when written to Excel. That comes from Excel itself, which keeps 15 significant digits, and sales amounts and quantities are not expected to be that long.

The folder for a client's real data is set so that Claude Code cannot read it. All testing uses fictitious samples.

Limitations

One month per run. No comparisons such as year over year.
No charts. The daily trend is a table only.
Of the Japanese eras, only Reiwa (the current one) is read.
For a store without a row-type column, returns cannot be told apart, so all its negative rows are flagged.
It does not correct the CSVs. It only flags rows.
No monthly scheduling and no email.

Technology

A command-line tool in Python 3.10. Its only library is openpyxl. 116 tests; tested on macOS.