ここにひどいデータがある。

こんな感じのUIで表示されているが

ユニークコード :1
編集履歴メモ:

編集日	メモ
2023/01/01	いろは
2023/01/15	にほへ
2023/02/01	とちる

データベース上ではこんな感じで保存されている。

unique_cd	date_col	text_col
1	2023/01/01,2023/01/15,2023/02/01	いろは,にほへ,とちる
2	1998/10/01,2010/1/1
3	2020/01/20	ABC
4	2020/01/20,2020/02/20	,ふがふが

こんなデータが数万ユニークコード分ある。これをExcelでなんとかしていきたいと思う。

前提

カンマ区切りのデータになっており、メモ内の記号はエスケープされていないが、デリミタ文字は利用されていない。
日付とメモの個数は『基本』合致しているが、移行の影響で日付のみ登録されているデータがある。
正常に登録されているデータはメモが空白でもデリミタで区切られている。
データベース上のデータは任意のデリミタファイルへ出力できる。

やっていきます。

まずユニーク対１データのペアにする。

unique_cd	date_col
1	2023/01/01,2023/01/15,2023/02/01
2	1998/10/01,2010/1/1
3	2020/01/20
4	2020/01/20,2020/02/20

これをちゃんとセルが分かれた横持ちデータにする。

ピボットテーブルウィザードでテーブルに変換する。

呪文の時間です。

Alt->D->Pと入力し、ピボットテーブルウィザードを表示し、複数のワークシート範囲を選択する。次へ

2.指定を選択し、先程のデータ範囲を指定する。
※データ件数が多いとかなり待つ・・・

3.新規ワークシートに作成するとピボットテーブルができます。右下をダブルクリック
値と列の組み合わせのデータができました。あとはこれを合体させれば完了です。よかったですね。

よくなかった

例のデータは量がザコザコでしたが、実際にやりたかったデータは3万行×4千列。縦持ちにすると1億2千万行となり、このざまです。

続行するとピボットまではなんとかできますが、テーブルに展開することは当然できません。Excelの限界を超えています。

次の手-PowerShellでなんとかする

TSVファイルでデータを取り出す。

中身はこんな感じ

UNIQUE	date
1	2023/01/01,2023/01/15,2023/02/01
2	1998/10/01,2010/1/1

UNIQUE	memo
1	いろは,にほへ,とちる
2

これをこうしてこうじゃ

泥くさいPowershellを書きます。前提として商品コード順にそれぞれソートされているので、値の合致が取れている事を前提にデータを分割し、再構成、バッファをもたせながら書き込みを行います。

$date = (Import-Csv -Path .\dates.tsv -Delimiter "`t")
$memos = (Import-Csv -Path .\memos.tsv -Delimiter "`t")
#出力ファイルパス
$exportfile = ".\tatemochi.csv"
#出力ファイルにヘッダを書き込む&初期化
Set-Content $exportfile "UNIQUE,date,memo"
$total = $date.Count
$tmpData = ""
# 追記する頻度
$writePer = 1000
$writeCnt = -1
for ($i = 0; $i -lt $date.Count;$i++){
    Write-Progress -Activity "進捗" -Status $i"/"$total  -PercentComplete ($i/$total)
    # 値をSplitし、件数を比較する。
    $datearray = $date[$i].date.Split(",")
    $memoarray = $memos[$i].memo.Split(",")
    if($datearray.count -eq $memoarray.Count){ #値が合致したら縦持ちにする。
        for($c=0;$c -lt $datearray.Count;$c++){
            $tmpData += (@($date[$i].UNIQUE , $datearray[$c] , $memoarray[$c]) -join ",")+"`r`n"
        }
    }
    if($writeCnt -eq $writePer ){
        Add-Content $exportfile $tmpData -NoNewline
        $tmpData = ""
        $writeCnt = 0
    }
}