構造化CSVによるインボイス表現とSQL定義

このブログ記事では、インボイスデータを構造化CSVとして定義し、MySQL上に再現・活用するためのSQL定義とその使い方を解説します。検証環境は、Windows 11のMySQL Server 8.0.71です。

1. 構造化CSVの特徴と活用方法

従来のアプリ開発では、定義しているリレーショナルテーブルを単純なクエリーでダンプしたCSVがインターフェイスとして使用されていたため、重複データを含むテーブルか重複を回避した複数のテーブルを出力していました。

構造化CSVでは、重複のない一つのファイルで階層構造のあるデータを出力することができます。また、出力された構造化CSVを読み込んだテーブルを対象に階層構造（ディメンション）情報を選択することで容易にデータをアプリ対応に復元することが可能です。

この記事では、

複数のテーブルからどのような条件で構造化CSVを出力するためのテーブルを定義するか
構造化CSVに対応するテーブルからどのような条件を指定して元のテーブルに相当する情報を抽出するか

をSQL文を使用して解説しています。

2. テスト環境

2.1. テーブル定義

以下に、Buyer（買い手）、Seller（売り手）、Invoice（請求書ヘッダ）、TaxBreakdown（税区分内訳）、InvoiceLine（明細行）、Item（商品）の6つのテーブルを定義します。日本の適格請求書発行事業者番号（”T”＋12桁）に対応する制約も追加しています。

買い手テーブル定義

-- 買い手テーブル
CREATE TABLE Buyer (
    ID VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    taxID VARCHAR(13),
    address TEXT NOT NULL,
    CONSTRAINT chk_Buyer_TaxID_Format CHECK (
        taxID IS NULL OR taxID REGEXP '^T[0-9]{12}$'
    )
);

売り手テーブル定義

-- 売り手テーブル
CREATE TABLE Seller (
    ID VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    taxID VARCHAR(13),
    address TEXT NOT NULL,
    CONSTRAINT chk_Seller_TaxID_Format CHECK (
        taxID IS NULL OR taxID REGEXP '^T[0-9]{12}$'
    )
);

請求書テーブル定義

-- 請求書テーブル
CREATE TABLE Invoice (
    ID VARCHAR(20) PRIMARY KEY,
    issueDate DATE NOT NULL,
    typeCode VARCHAR(10) NOT NULL,
    dueDate DATE NOT NULL,
    buyerID VARCHAR(20) NOT NULL,
    sellerID VARCHAR(20) NOT NULL,
    sumOfLineNetAmount INT NOT NULL,
    totalAmountWithoutTax INT NOT NULL,
    totalTaxAmount INT NOT NULL,
    totalAmountWithTax INT NOT NULL,
    FOREIGN KEY (buyerID) REFERENCES Buyer(ID),
    FOREIGN KEY (sellerID) REFERENCES Seller(ID)
);

税区分内訳テーブル定義

-- 税区分内訳テーブル
CREATE TABLE TaxBreakdown (
    invoiceID VARCHAR(20) NOT NULL,
    taxCategoryCode VARCHAR(10) NOT NULL,
    taxCategoryRate DECIMAL(5,2) NOT NULL,
    taxCategoryTaxableAmount INT NOT NULL,
    taxCategoryTaxAmount INT NOT NULL,
    FOREIGN KEY (invoiceID) REFERENCES Invoice(ID)
);

請求書明細行テーブル定義

-- 請求書明細行テーブル
CREATE TABLE InvoiceLine (
    ID VARCHAR(20) PRIMARY KEY,
    invoiceID VARCHAR(20) NOT NULL,
    netAmount INT NOT NULL,
    itemID VARCHAR(20) NOT NULL,
    quantity DECIMAL(10,3) NOT NULL,
    uom VARCHAR(10) NOT NULL,
    FOREIGN KEY (invoiceID) REFERENCES Invoice(ID),
    FOREIGN KEY (itemID) REFERENCES Item(ID)
);

商品テーブル定義

-- 商品テーブル
CREATE TABLE Item (
    ID VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price INT NOT NULL,
    baseQuantity DECIMAL(10,3) NOT NULL,
    uom VARCHAR(10) NOT NULL,
    taxCategoryCode VARCHAR(10) NOT NULL,
    taxCategoryRate DECIMAL(5,2) NOT NULL
);

2.2. 初期データ登録

テストデータを次のように登録します。

環境準備

外来キー参照などのチェックを無効にし、テーブルからデータ削除します。

SET SQL_SAFE_UPDATES = 0;

DELETE FROM InvoiceLine;
DELETE FROM TaxBreakdown;
DELETE FROM Invoice;
DELETE FROM Buyer;
DELETE FROM Seller;
DELETE FROM Item;

買い手データ登録

INSERT INTO Buyer (
    ID, name, taxID, address
)
VALUES
    ('B001', '山田商事株式会社', 'T012345678901', '東京都港区');

INSERT INTO Buyer (
    ID, name, address
)
VALUES
    ('B002', 'Greenwood High School', '456 School Rd, NY');

Buyer

売り手データ登録

INSERT INTO Seller (
    ID, name, taxID, address
)
VALUES
    ('S001', '佐藤製作所', 'T098765432109', '大阪市中央区'),
    ('S002', 'Liberty Office Supply', 'T765432109876', '123 Main St, New York, NY');

SellerA

商品データ登録

INSERT INTO Item (
    ID, name, price, baseQuantity, uom, taxCategoryCode, taxCategoryRate
)
VALUES
    ('1001', 'オフィスチェア', 15000, 1, 'EA', 'S', 0.10),
    ('2001', 'ミネラルウォーター', 1200, 1, 'BOX', 'AA', 0.08),
    ('A001', 'Notebook', 500, 1, 'EA', 'S', 0.10),
    ('A002', 'Ballpoint Pen', 120, 1, 'EA', 'S', 0.10);

Item

請求書ヘッダデータ登録

INSERT INTO Invoice (
    ID, issueDate, typeCode, dueDate,
    buyerID, sellerID,
    sumOfLineNetAmount, totalAmountWithoutTax, totalTaxAmount, totalAmountWithTax
)
VALUES
    ('INV001', '2024-06-30', '380', '2024-07-15', 'B001', 'S001', 16200, 16200, 246, 18446),
    ('INV002', '2024-07-01', '380', '2024-07-15', 'B002', 'S002', 620, 620, 62, 682);

Invoice

税区分内訳データ登録

INSERT INTO TaxBreakdown (
    invoiceID, taxCategoryCode, taxCategoryRate,
    taxCategoryTaxableAmount, taxCategoryTaxAmount
)
VALUES
    ('INV001', 'S', 0.10, 15000, 150),
    ('INV001', 'AA', 0.08, 1200, 96),
    ('INV002', 'S', 0.10, 620, 62);

TaxBreakdown

明細行データ登録

INSERT INTO InvoiceLine (
    ID, invoiceID, netAmount, itemID, quantity, uom
)
VALUES
    ('L001', 'INV001', 15000, '1001', 1, 'EA'),
    ('L002', 'INV001', 1200, '2001', 1, 'BOX'),
    ('L003', 'INV002', 500, 'A001', 1, 'EA'),
    ('L004', 'INV002', 120, 'A002', 1, 'EA');

InvoiceLine

外来キー制約復元

SET SQL_SAFE_UPDATES = 1;

3. 請求書番号を指定した構造化CSVテーブル形式の検索

請求書ヘッダ、税区分内訳、明細行それぞれのテーブルの指定された請求書番号の行を取り出して結合した結果が返されます。

-- 1. 請求書ヘッダ + 買い手 + 売り手
SELECT
  1 AS dInvoice,
  '' AS dTaxBreakdown,
  '' AS dInvoiceLine,
  IFNULL(i.ID, '') AS `Invoice.ID`,
  IFNULL(i.issueDate, '') AS issueDate,
  IFNULL(i.typeCode, '') AS typeCode,
  IFNULL(i.dueDate, '') AS dueDate,
  IFNULL(b.name, '') AS `Buyer.name`,
  IFNULL(s.name, '') AS `Seller.name`,
  IFNULL(s.taxID, '') AS `Seller.taxID`,
  IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,
  IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,
  IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,
  IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,
  '' AS `TaxBreakdown.taxCategoryCode`,
  '' AS `TaxBreakdown.taxCategoryRate`,
  '' AS `TaxBreakdown.taxCategoryTaxAmount`,
  '' AS `InvoiceLine.ID`,
  '' AS `InvoiceLine.netAmount`,
  '' AS `InvoiceLine.quantity`,
  '' AS `InvoiceLine.uom`,
  '' AS `Item.ID`,
  '' AS `Item.name`,
  '' AS `Item.price`,
  '' AS `Item.baseQuantity`,
  '' AS `Item.uom`,
  '' AS `Item.taxCategoryCode`,
  '' AS `Item.taxCategoryRate`
FROM Invoice i
JOIN Buyer b ON i.buyerID = b.ID
JOIN Seller s ON i.sellerID = s.ID
WHERE i.ID = 'INV001'
-- 2. 税区分内訳
UNION ALL
SELECT
  1 AS dInvoice,
  ROW_NUMBER() OVER () AS dTaxBreakdown,
  '' AS dInvoiceLine,
  '' AS `Invoice.ID`,
  '', '', '', '', '', '', '', '', '', '',
  IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,
  IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,
  IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,
  '', '', '', '',
  '', '', '', '', '', '', ''
FROM TaxBreakdown tb
WHERE tb.invoiceID = 'INV001'
-- 3. 明細行 + 商品
UNION ALL
SELECT
  1 AS dInvoice,
  '' AS dTaxBreakdown,
  ROW_NUMBER() OVER () AS dInvoiceLine,
  '' AS `Invoice.ID`,
  '', '', '', '', '', '', '', '', '', '', '', '', '',
  IFNULL(il.ID, '') AS `InvoiceLine.ID`,
  IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,
  IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,
  IFNULL(il.uom, '') AS `InvoiceLine.uom`,
  IFNULL(it.ID, '') AS `Item.ID`,
  IFNULL(it.name, '') AS `Item.name`,
  IFNULL(it.price, '') AS `Item.price`,
  IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,
  IFNULL(it.uom, '') AS `Item.uom`,
  IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,
  IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`
FROM InvoiceLine il
JOIN Item it ON il.itemID = it.ID
WHERE il.invoiceID = 'INV001';

4. 構造化CSVの生成

ここでは、Invoice（請求書）に関するデータを統合した構造化CSVを生成するSQLを解説します。CSVは、請求書ごとに請求書ヘッダ、税区分内訳、明細行を1つの表にまとめた形式で出力され、dInvoice, dTaxBreakdown, `dInvoiceLine`の各列によって階層が表現されます。

4.1. 構造化CSV生成SQL

以下のSQLは、請求書ごとに連番（dInvoice）を振りつつ、各データ（請求書ヘッダ、税区分内訳、明細行）を統一されたフォーマットでUNIONして1つのビュー（またはテーブル）として出力します。

-- 既に定義されていたら削除
DROP TABLE IF EXISTS InvoiceStructuredCSV;

CREATE table InvoiceStructuredCSV AS
-- (1) WITH句で請求書ごとにその順序番号をdInvoiceに割り当てる
WITH InvoiceIndex AS (
  SELECT
    ID AS invoiceID,
    ROW_NUMBER() OVER (ORDER BY ID) AS dInvoice
  FROM Invoice
)
-- (2) ヘッダ行を抽出（dInvoice のみ設定）
SELECT
  ii.dInvoice,
  '' AS dTaxBreakdown,
  '' AS dInvoiceLine,
  IFNULL(i.ID, '') AS `Invoice.ID`,
  IFNULL(i.issueDate, '') AS issueDate,
  IFNULL(i.typeCode, '') AS typeCode,
  IFNULL(i.dueDate, '') AS dueDate,
  IFNULL(b.name, '') AS `Buyer.name`,
  IFNULL(s.name, '') AS `Seller.name`,
  IFNULL(s.taxID, '') AS `Seller.taxID`,
  IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,
  IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,
  IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,
  IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,
  '' AS `TaxBreakdown.taxCategoryCode`,
  '' AS `TaxBreakdown.taxCategoryRate`,
  '' AS `TaxBreakdown.taxCategoryTaxAmount`,
  '' AS `InvoiceLine.ID`,
  '' AS `InvoiceLine.netAmount`,
  '' AS `InvoiceLine.quantity`,
  '' AS `InvoiceLine.uom`,
  '' AS `Item.ID`,
  '' AS `Item.name`,
  '' AS `Item.price`,
  '' AS `Item.baseQuantity`,
  '' AS `Item.uom`,
  '' AS `Item.taxCategoryCode`,
  '' AS `Item.taxCategoryRate`
FROM Invoice i
JOIN InvoiceIndex ii ON i.ID = ii.invoiceID
JOIN Buyer b ON i.buyerID = b.ID
JOIN Seller s ON i.sellerID = s.ID
-- (3) 税内訳を抽出（dTaxBreakdown のみ設定）
UNION ALL
SELECT
  ii.dInvoice,
  ROW_NUMBER() OVER (PARTITION BY tb.invoiceID ORDER BY tb.taxCategoryCode) AS dTaxBreakdown,
  '' AS dInvoiceLine,
  '', '', '', '', '', '', '', '', '', '', '',
  IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,
  IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,
  IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,
  '', '', '', '',
  '', '', '', '', '', '', ''
FROM TaxBreakdown tb
JOIN InvoiceIndex ii ON tb.invoiceID = ii.invoiceID
-- (4) 明細＋商品情報を抽出（dInvoiceLine のみ設定）
UNION ALL
SELECT
  ii.dInvoice,
  '' AS dTaxBreakdown,
  ROW_NUMBER() OVER (PARTITION BY il.invoiceID ORDER BY il.ID) AS dInvoiceLine,
  '', '', '', '', '', '', '', '', '', '', '', '', '', '',
  IFNULL(il.ID, '') AS `InvoiceLine.ID`,
  IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,
  IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,
  IFNULL(il.uom, '') AS `InvoiceLine.uom`,
  IFNULL(it.ID, '') AS `Item.ID`,
  IFNULL(it.name, '') AS `Item.name`,
  IFNULL(it.price, '') AS `Item.price`,
  IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,
  IFNULL(it.uom, '') AS `Item.uom`,
  IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,
  IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`
FROM InvoiceLine il
JOIN InvoiceIndex ii ON il.invoiceID = ii.invoiceID
JOIN Item it ON il.itemID = it.ID
-- (5) ソート：ヘッダ→税内訳→明細
ORDER BY
  dInvoice,
  CASE
    WHEN dTaxBreakdown IS NULL AND dInvoiceLine IS NULL THEN 0
    WHEN dTaxBreakdown IS NOT NULL THEN 1
    WHEN dInvoiceLine IS NOT NULL THEN 2
    ELSE 3
  END,
  dTaxBreakdown,
  dInvoiceLine;

4.2. 解説

(1) WITH InvoiceIndex AS (…)
請求書ID順に連番（dInvoice）を付与し、すべてのUNION対象に共通のキーとして利用します。

(2) 請求書ヘッダ（基本情報）を売り手、買い手の情報と共に1行にまとめて出力します。`dInvoice`以外の階層キーはNULLになります。

(3) 請求書ごとの税区分内訳を`TaxBreakdown`テーブルから取得し、`dTaxBreakdown`に連番を振って出力します。ヘッダ情報は含みません。

(4) 請求書明細行`InvoiceLine`と商品情報`Item`を結合し、`dInvoiceLine`に連番を振って出力します。こちらもヘッダ情報は含みません。

(5) 最後に ORDER BY によって、`dInvoice`ごとにヘッダ→税区分内訳→明細行の順に並べ替えを行います。これにより、人間にも機械にも処理しやすい構造化CSVが得られます。

InvoiceStructuredCSV2

この構造化CSVは、行レベルで意味が異なるデータ（ヘッダ・税区分内訳・明細行）を1つのCSVファイルにまとめています。
後続処理では、dInvoice, dTaxBreakdown, dInvoiceLine のいずれが非NULLかで行の種類を識別できます。

対象

dInvoice
請求書ヘッダ

dTaxBreakdown
税区分内訳

dInvoiceLine
明細行

請求書ヘッダ

数値

空欄

税区分内訳

数値

空欄

明細行

数値

空欄

数値

5. 抽出クエリ

請求書番号’INV001’の請求書ヘッダ情報や税区分内訳情報などを取り出すには、請求書番号’INV001’を条件に、対応するディメンジョンが定義されている行を選択します。

構造化CSVテーブルから、請求書番号’INV001’の請求書ヘッダのデータを検索するときには次のSELECT文を使用します。

SELECT DISTINCT
  `Invoice.ID` AS ID,
  issueDate,
  typeCode,
  dueDate,
  sumOfLineNetAmount,
  totalAmountWithoutTax,
  totalTaxAmount,
  totalAmountWithTax,
  `Buyer.name`,
  `Seller.name`,
  `Seller.taxID`
FROM InvoiceStructuredCSV
WHERE `Invoice.ID` = 'INV001';

InvoiceFromStructuredCSV

構造化CSVテーブルから、請求書番号’INV001’の税区分内訳データを検索するときには次のSELECT文を使用します。

SELECT
  d.dInvoice AS invoiceRef,
  d.`TaxBreakdown.taxCategoryCode`,
  d.`TaxBreakdown.taxCategoryRate`,
  d.`TaxBreakdown.taxCategoryTaxAmount`
FROM InvoiceStructuredCSV d
WHERE d.dInvoice = (
  SELECT dInvoice
  FROM InvoiceStructuredCSV
  WHERE `Invoice.ID` = 'INV001'
)
AND d.dTaxBreakdown IS NOT NULL;

TaxBreakdownFromStructuredCSV

構造化CSVテーブルから、請求書番号’INV001’の明細行データを検索するときには次のSELECT文を使用します。

明細行データ検索

SELECT
  d.dInvoice AS invoiceRef,
  d.`InvoiceLine.ID`,
  d.`InvoiceLine.netAmount`,
  d.`InvoiceLine.quantity`,
  d.`InvoiceLine.uom`,
  d.`Item.ID`,
  d.`Item.name`,
  d.`Item.price`,
  d.`Item.baseQuantity`,
  d.`Item.uom`,
  d.`Item.taxCategoryCode`,
  d.`Item.taxCategoryRate`
FROM InvoiceStructuredCSV d
WHERE d.dInvoice = (
  SELECT dInvoice
  FROM InvoiceStructuredCSV
  WHERE `Invoice.ID` = 'INV001'
  AND dInvoice IS NOT NULL
)
AND d.dInvoiceLine IS NOT NULL;

InvoiceLineFromStructuredCSV

6. まとめ

構造化CSVは、階層構造をフラットに1つのCSVに統合しながら、各階層をdInvoice, dTaxBreakdown, dInvoiceLineなどの識別子で示すことにより、効率的な集計・抽出を可能にします。日本の適格請求書制度に対応した設計も可能です。

NULLに対して、文字’NULL’を出力せず、空欄とするために次のようにIFNULL関数を使用します。

SELECT
  IFNULL(dInvoice, '') AS dInvoice,
  IFNULL(dTaxBreakdown, '') AS dTaxBreakdown,
  IFNULL(dInvoiceLine, '') AS dInvoiceLine,
  IFNULL(`Invoice.ID`, '') AS `Invoice.ID`,
  IFNULL(issueDate, '') AS issueDate,
  IFNULL(typeCode, '') AS typeCode,
  IFNULL(dueDate, '') AS dueDate,
  IFNULL(`Buyer.name`, '') AS `Buyer.name`,
  IFNULL(`Seller.name`, '') AS `Seller.name`,
  IFNULL(`Seller.taxID`, '') AS `Seller.taxID`,
  IFNULL(sumOfLineNetAmount, '') AS sumOfLineNetAmount,
  IFNULL(totalAmountWithoutTax, '') AS totalAmountWithoutTax,
  IFNULL(totalTaxAmount, '') AS totalTaxAmount,
  IFNULL(totalAmountWithTax, '') AS totalAmountWithTax,
  IFNULL(`TaxBreakdown.taxCategoryCode`, '') AS `TaxBreakdown.taxCategoryCode`,
  IFNULL(`TaxBreakdown.taxCategoryRate`, '') AS `TaxBreakdown.taxCategoryRate`,
  IFNULL(`TaxBreakdown.taxCategoryTaxAmount`, '') AS `TaxBreakdown.taxCategoryTaxAmount`,
  IFNULL(`InvoiceLine.ID`, '') AS `InvoiceLine.ID`,
  IFNULL(`InvoiceLine.netAmount`, '') AS `InvoiceLine.netAmount`,
  IFNULL(`InvoiceLine.quantity`, '') AS `InvoiceLine.quantity`,
  IFNULL(`InvoiceLine.uom`, '') AS `InvoiceLine.uom`,
  IFNULL(`Item.ID`, '') AS `Item.ID`,
  IFNULL(`Item.name`, '') AS `Item.name`,
  IFNULL(`Item.price`, '') AS `Item.price`,
  IFNULL(`Item.baseQuantity`, '') AS `Item.baseQuantity`,
  IFNULL(`Item.uom`, '') AS `Item.uom`,
  IFNULL(`Item.taxCategoryCode`, '') AS `Item.taxCategoryCode`,
  IFNULL(`Item.taxCategoryRate`, '') AS `Item.taxCategoryRate`
FROM InvoiceStructuredCSV;

構造化CSV

dInvoice,dTaxBreakdown,dInvoiceLine,Invoice.ID,issueDate,typeCode,dueDate,Buyer.name,Seller.name,Seller.taxID,sumOfLineNetAmount,totalAmountWithoutTax,totalTaxAmount,totalAmountWithTax,TaxBreakdown.taxCategoryCode,TaxBreakdown.taxCategoryRate,TaxBreakdown.taxCategoryTaxAmount,InvoiceLine.ID,InvoiceLine.netAmount,InvoiceLine.quantity,InvoiceLine.uom,Item.ID,Item.name,Item.price,Item.baseQuantity,Item.uom,Item.taxCategoryCode,Item.taxCategoryRate
1,,,INV001,2024-06-30,380,2024-07-15,山田商事株式会社,佐藤製作所,T098765432109,16200.0,16200.0,246.0,18666.0,,,,,,,,,,,,,,
1,1,,,,,,,,,,,,,AA,0.08,96.0,,,,,,,,,,,
1,2,,,,,,,,,,,,,S,0.1,150.0,,,,,,,,,,,
1,,1,,,,,,,,,,,,,,,L001,15000.0,1.0,EA,1001,オフィスチェア,15000.0,1.0,EA,S,0.1
1,,2,,,,,,,,,,,,,,,L002,1200.0,1.0,BOX,2001,ミネラルウォーター,1200.0,1.0,BOX,AA,0.08
2,,,INV002,2024-07-01,380,2024-07-15,Greenwood High School,Liberty Office Supply,T765432109876,620.0,620.0,62.0,682.0,,,,,,,,,,,,,,
2,1,,,,,,,,,,,,,S,0.1,62.0,,,,,,,,,,,
2,,1,,,,,,,,,,,,,,,L003,500.0,1.0,EA,A001,Notebook,500.0,1.0,EA,S,0.1
2,,2,,,,,,,,,,,,,,,L004,120.0,1.0,EA,A002,Ballpoint Pen,120.0,1.0,EA,S,0.1

dInvoice	dTaxBreakdown	dInvoiceLine	Invoice.ID	issueDate	typeCode	dueDate	Buyer.name	Seller.name	Seller.taxID	sumOfLineNetAmount	totalAmountWithoutTax	totalTaxAmount	totalAmountWithTax	TaxBreakdown.taxCategoryCode	TaxBreakdown.taxCategoryRate	TaxBreakdown.taxCategoryTaxAmount	InvoiceLine.ID	InvoiceLine.netAmount	InvoiceLine.quantity	InvoiceLine.uom	Item.ID	Item.name	Item.price	Item.baseQuantity	Item.uom	Item.taxCategoryCode	Item.taxCategoryRate
1			INV001	2024-06-30	380	2024-07-15	山田商事株式会社	佐藤製作所	T098765432109	16200	16200	246	18446
1	1													S	0.10	150
1	2													AA	0.08	96
1		1															L001	15000	1.000	EA	1001	オフィスチェア	15000	1.000	EA	S	0.10
1		2															L002	1200	1.000	BOX	2001	ミネラルウォーター	1200	1.000	BOX	AA	0.08

本記事で使用しているテーブルのE-R図を以下に示します。

7. 構造化CSVファイル出力Pythonスクリプト

Pythonプログラムでファイル出力するには、pymysqlとpandasライブラリを使用する次のようなプログラムで処理します。

import pymysql
import pandas as pd

# --- Configuration ---
db_config = {
    "host": "localhost",
    "user": "root",
    "password": "user password",
    "database": "structured csv database",
    "charset": "utf8mb4"
}
db_config = {
    "host": "localhost",
    "user": "root",
    "password": "$V1-mysql-N0",
    "database": "structured_csv",
    "charset": "utf8mb4"
}
output_csv_path0 = "structured_invoice_export0.csv"
output_csv_path1 = "structured_invoice_export1.csv"
invoice_id = "{invoice_id}"

# --- SQL Query ---
sql_query0 = f"""
SELECT
  1 AS dInvoice,
  '' AS dTaxBreakdown,
  '' AS dInvoiceLine,
  IFNULL(i.ID, '') AS `Invoice.ID`,
  IFNULL(i.issueDate, '') AS issueDate,
  IFNULL(i.typeCode, '') AS typeCode,
  IFNULL(i.dueDate, '') AS dueDate,
  IFNULL(b.name, '') AS `Buyer.name`,
  IFNULL(s.name, '') AS `Seller.name`,
  IFNULL(s.taxID, '') AS `Seller.taxID`,
  IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,
  IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,
  IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,
  IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,
  '' AS `TaxBreakdown.taxCategoryCode`,
  '' AS `TaxBreakdown.taxCategoryRate`,
  '' AS `TaxBreakdown.taxCategoryTaxAmount`,
  '' AS `InvoiceLine.ID`,
  '' AS `InvoiceLine.netAmount`,
  '' AS `InvoiceLine.quantity`,
  '' AS `InvoiceLine.uom`,
  '' AS `Item.ID`,
  '' AS `Item.name`,
  '' AS `Item.price`,
  '' AS `Item.baseQuantity`,
  '' AS `Item.uom`,
  '' AS `Item.taxCategoryCode`,
  '' AS `Item.taxCategoryRate`
FROM Invoice i
JOIN Buyer b ON i.buyerID = b.ID
JOIN Seller s ON i.sellerID = s.ID
WHERE i.ID = '{invoice_id}'
-- 2. Tax Breakdown Rows
UNION ALL
SELECT
  1 AS dInvoice,
  ROW_NUMBER() OVER () AS dTaxBreakdown,
  '' AS dInvoiceLine,
  '' AS `Invoice.ID`,
  '', '', '', '', '', '', '', '', '', '',
  IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,
  IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,
  IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,
  '', '', '', '',
  '', '', '', '', '', '', ''
FROM TaxBreakdown tb
WHERE tb.invoiceID = '{invoice_id}'
-- 3. Invoice Line + Item Rows
UNION ALL
SELECT
  1 AS dInvoice,
  '' AS dTaxBreakdown,
  ROW_NUMBER() OVER () AS dInvoiceLine,
  '' AS `Invoice.ID`,
  '', '', '', '', '', '', '', '', '', '', '', '', '',
  IFNULL(il.ID, '') AS `InvoiceLine.ID`,
  IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,
  IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,
  IFNULL(il.uom, '') AS `InvoiceLine.uom`,
  IFNULL(it.ID, '') AS `Item.ID`,
  IFNULL(it.name, '') AS `Item.name`,
  IFNULL(it.price, '') AS `Item.price`,
  IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,
  IFNULL(it.uom, '') AS `Item.uom`,
  IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,
  IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`
FROM InvoiceLine il
JOIN Item it ON il.itemID = it.ID
WHERE il.invoiceID = '{invoice_id}';
"""

sql_query1 = f"""
-- (1) 請求書ごとにその順序番号を定義
WITH InvoiceIndex AS (
  SELECT
    ID AS invoiceID,
    ROW_NUMBER() OVER (ORDER BY ID) AS dInvoice
  FROM Invoice
)
-- (2) ヘッダ行を抽出（dInvoice のみ設定）
SELECT
  ii.dInvoice,
  '' AS dTaxBreakdown,
  '' AS dInvoiceLine,
  IFNULL(i.ID, '') AS `Invoice.ID`,
  IFNULL(i.issueDate, '') AS issueDate,
  IFNULL(i.typeCode, '') AS typeCode,
  IFNULL(i.dueDate, '') AS dueDate,
  IFNULL(b.name, '') AS `Buyer.name`,
  IFNULL(s.name, '') AS `Seller.name`,
  IFNULL(s.taxID, '') AS `Seller.taxID`,
  IFNULL(i.sumOfLineNetAmount, '') AS sumOfLineNetAmount,
  IFNULL(i.totalAmountWithoutTax, '') AS totalAmountWithoutTax,
  IFNULL(i.totalTaxAmount, '') AS totalTaxAmount,
  IFNULL(i.totalAmountWithTax, '') AS totalAmountWithTax,
  '' AS `TaxBreakdown.taxCategoryCode`,
  '' AS `TaxBreakdown.taxCategoryRate`,
  '' AS `TaxBreakdown.taxCategoryTaxAmount`,
  '' AS `InvoiceLine.ID`,
  '' AS `InvoiceLine.netAmount`,
  '' AS `InvoiceLine.quantity`,
  '' AS `InvoiceLine.uom`,
  '' AS `Item.ID`,
  '' AS `Item.name`,
  '' AS `Item.price`,
  '' AS `Item.baseQuantity`,
  '' AS `Item.uom`,
  '' AS `Item.taxCategoryCode`,
  '' AS `Item.taxCategoryRate`
FROM Invoice i
JOIN InvoiceIndex ii ON i.ID = ii.invoiceID
JOIN Buyer b ON i.buyerID = b.ID
JOIN Seller s ON i.sellerID = s.ID
-- (3) 税内訳を抽出（dTaxBreakdown のみ設定）
UNION ALL
SELECT
  ii.dInvoice,
  ROW_NUMBER() OVER (PARTITION BY tb.invoiceID ORDER BY tb.taxCategoryCode) AS dTaxBreakdown,
  '' AS dInvoiceLine,
  '', '', '', '', '', '', '', '', '', '', '',
  IFNULL(tb.taxCategoryCode, '') AS `TaxBreakdown.taxCategoryCode`,
  IFNULL(tb.taxCategoryRate, '') AS `TaxBreakdown.taxCategoryRate`,
  IFNULL(tb.taxCategoryTaxAmount, '') AS `TaxBreakdown.taxCategoryTaxAmount`,
  '', '', '', '',
  '', '', '', '', '', '', ''
FROM TaxBreakdown tb
JOIN InvoiceIndex ii ON tb.invoiceID = ii.invoiceID
-- (4) 明細＋商品情報を抽出（dInvoiceLine のみ設定）
UNION ALL
SELECT
  ii.dInvoice,
  '' AS dTaxBreakdown,
  ROW_NUMBER() OVER (PARTITION BY il.invoiceID ORDER BY il.ID) AS dInvoiceLine,
  '', '', '', '', '', '', '', '', '', '', '', '', '', '',
  IFNULL(il.ID, '') AS `InvoiceLine.ID`,
  IFNULL(il.netAmount, '') AS `InvoiceLine.netAmount`,
  IFNULL(il.quantity, '') AS `InvoiceLine.quantity`,
  IFNULL(il.uom, '') AS `InvoiceLine.uom`,
  IFNULL(it.ID, '') AS `Item.ID`,
  IFNULL(it.name, '') AS `Item.name`,
  IFNULL(it.price, '') AS `Item.price`,
  IFNULL(it.baseQuantity, '') AS `Item.baseQuantity`,
  IFNULL(it.uom, '') AS `Item.uom`,
  IFNULL(it.taxCategoryCode, '') AS `Item.taxCategoryCode`,
  IFNULL(it.taxCategoryRate, '') AS `Item.taxCategoryRate`
FROM InvoiceLine il
JOIN InvoiceIndex ii ON il.invoiceID = ii.invoiceID
JOIN Item it ON il.itemID = it.ID
-- (5) ソート：ヘッダ→税内訳→明細
ORDER BY
  dInvoice,
  CASE
    WHEN dTaxBreakdown IS NULL AND dInvoiceLine IS NULL THEN 0
    WHEN dTaxBreakdown IS NOT NULL THEN 1
    WHEN dInvoiceLine IS NOT NULL THEN 2
    ELSE 3
  END,
  dTaxBreakdown,
  dInvoiceLine;
"""
# --- Execution ---
connection = pymysql.connect(**db_config)

df0 = pd.read_sql(sql_query0, connection)
# --- Cast dInvoice, dTaxBreakdown, dInvoiceLine to integer ---
df0["dInvoice"] = df0["dInvoice"].astype("Int64")
df0["dTaxBreakdown"] = df0["dTaxBreakdown"].astype("Int64")
df0["dInvoiceLine"] = df0["dInvoiceLine"].astype("Int64")
df0.to_csv(output_csv_path0, index=False, encoding="utf-8-sig")

df1 = pd.read_sql(sql_query1, connection)
# --- Cast dInvoice, dTaxBreakdown, dInvoiceLine to integer ---
df1["dInvoice"] = df1["dInvoice"].astype("Int64")
df1["dTaxBreakdown"] = df1["dTaxBreakdown"].astype("Int64")
df1["dInvoiceLine"] = df1["dInvoiceLine"].astype("Int64")
df1.to_csv(output_csv_path1, index=False, encoding="utf-8-sig")

connection.close()

print(f"✅ Exported invoice to {output_csv_path0} and {output_csv_path1}.")

1. 構造化CSVの特徴と活用方法
2. テスト環境
- 2.1. テーブル定義
- 2.2. 初期データ登録
3. 請求書番号を指定した構造化CSVテーブル形式の検索
4. 構造化CSVの生成
- 4.1. 構造化CSV生成SQL
- 4.2. 解説
5. 抽出クエリ
6. まとめ
7. 構造化CSVファイル出力Pythonスクリプト

三分一技術士事務所

構造化CSVによるインボイス表現とSQL定義

構造化CSVによるインボイス表現とSQL定義

1. 構造化CSVの特徴と活用方法

2. テスト環境

2.1. テーブル定義

2.2. 初期データ登録

3. 請求書番号を指定した構造化CSVテーブル形式の検索

4. 構造化CSVの生成

4.1. 構造化CSV生成SQL

4.2. 解説

5. 抽出クエリ

6. まとめ

7. 構造化CSVファイル出力Pythonスクリプト

コメント

コメントを残すコメントをキャンセル

2025年6月
月	火	水	木	金	土	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

構造化CSVによるインボイス表現とSQL定義

構造化CSVによるインボイス表現とSQL定義

1. 構造化CSVの特徴と活用方法

2. テスト環境

2.1. テーブル定義

2.2. 初期データ登録

3. 請求書番号を指定した構造化CSVテーブル形式の検索

4. 構造化CSVの生成

4.1. 構造化CSV生成SQL

4.2. 解説

5. 抽出クエリ

6. まとめ

7. 構造化CSVファイル出力Pythonスクリプト

コメント

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル