ここにひどいデータがある。
こんな感じの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 |
これをちゃんとセルが分かれた横持ちデータにする。
- date_colを列選択した状態で『データ＞区切り位置』を選択する
2.コンマ区切りでの分割を選択してひどいデータをとりあえずセル区切りにする。
- Ctrl+Endを押し、最終列にジャンプする。
- 1行目へ移動し、=COLUMN()を入力し列番号をつける。
- ユニークコード以外の列名を番号にする。
ピボットテーブルウィザードでテーブルに変換する。
呪文の時間です。
- 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
}
}