ひどい横持ちデータを八方駆使してなんとか縦持ちデータにする

# ここにひどいデータがある。
こんな感じの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 | 

これをちゃんとセルが分かれた横持ちデータにする。
1. date_colを列選択した状態で『データ＞区切り位置』を選択する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/a5909978-b17e-fd0c-20a2-f8caba08d10b.png)
2.コンマ区切りでの分割を選択してひどいデータをとりあえずセル区切りにする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/8110b6a9-135d-2df8-98f7-953f68dee586.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/ac1e6c0a-3ebf-cdc8-d9ce-ad9701551228.png)
3. Ctrl+Endを押し、最終列にジャンプする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/47a5aeec-6991-7f79-9434-69db42859e3d.png)
4. 1行目へ移動し、=COLUMN()を入力し列番号をつける。
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/b318f5d4-b7d2-994e-c97d-158fdc75c251.png)
5. ユニークコード以外の列名を番号にする。
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/b210a51c-128b-0e66-655d-d0405cddef13.png)

## ピボットテーブルウィザードでテーブルに変換する。
呪文の時間です。
1. Alt->D->Pと入力し、ピボットテーブルウィザードを表示し、複数のワークシート範囲を選択する。次へ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/8adf36ad-aa9a-b9bd-3ebd-8a11ed89544e.png)
2.指定を選択し、先程のデータ範囲を指定する。
※データ件数が多いとかなり待つ・・・
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/4a6a5f0c-ae9e-c875-bf0d-996deb1fa95e.png)
3.新規ワークシートに作成するとピボットテーブルができます。右下をダブルクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/f0cb7be1-f05b-2f9f-afa9-49e40b68a9e3.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/84c67710-c48c-fc9b-f220-b07fde07b182.png)
4. 値と列の組み合わせのデータができました。あとはこれを合体させれば完了です。よかったですね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/55f1229b-fb34-39bc-bc48-ed9b441de925.png)

## よくなかった
例のデータは量がザコザコでしたが、実際にやりたかったデータは3万行×4千列。縦持ちにすると1億2千万行となり、このざまです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/ad65d727-bfb7-2698-e2ce-6aa4d26735b6.png)

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

# 次の手-PowerShellでなんとかする
## TSVファイルでデータを取り出す。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89777/7b5cb2ab-65a0-debd-41d3-afca71322fe0.png)
中身はこんな感じ
| UNIQUE | date |
|:-:|:-|
|  1 | 2023/01/01,2023/01/15,2023/02/01   |
|  2 |  1998/10/01,2010/1/1   |


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

## これをこうしてこうじゃ

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

```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
    }
}
```