CSVに入っていた商品コード「00123」が、Excelで開くと「123」になってしまいます。数字は合っているように見えても、商品コードとしては別の値になってしまい、地味に困りました。
この記事では、Power Queryで先頭の0を残したままCSVを取り込む方法を紹介します。すでに0が消えたときの確認ポイントと、取り込み後に更新しても設定を保つ方法までまとめます。
先に結論：CSVは「テキスト」として取り込む
最短の手順
- 元のCSVをバックアップする。
- Excelの［データ］→［テキストまたはCSVから］で取り込む。
- 取り込み画面でデータ型の自動検出をオフにし、［データの変換］を選ぶ。
- 商品コード列を［テキスト］、数量列を［整数］にする。
- ［閉じて読み込む］でExcelに戻し、商品コードの0を確認する。
対象はWindows版ExcelのPower Queryです。画面名や選択肢はバージョンにより異なります。自動検出の設定が見当たらない場合や、すでに取り込んでいる場合は、後述の「変更された型」の見直しを行ってください。
なぜ先頭の0が消えるのか
CSVには、Excelブックのようなセルの表示形式は保存されません。読み込む側が「これは数値だ」と判断すると、00123は数値の123になります。数量なら困らなくても、商品コードなどの識別用の値では問題になります。
注意したいのは、Excelで直接開くときの変換と、Power Query内の型変換は別だという点です。Power Queryから取り込んでも、自動的に作られた型変換の処理で、商品コードが数値になることがあります。
| データの用途 | 値の例 | この記事で使う型 |
|---|---|---|
| 商品を識別するコード | 00123、00007 | テキスト |
| 商品の名前 | ノート、ペン | テキスト |
| 計算に使う数量 | 2、3 | 整数 |
見た目が数字でも、足し算する値なのか、識別するための値なのかで扱いを分けると整理しやすくなります。
お試し用CSVを用意する
以下はこの記事用に用意した架空のデータです。コピーしてメモ帳へ貼り付け、ファイル名をproducts.csv、文字コードをUTF-8で保存してください。
※ 保存時のファイルの種類は［すべてのファイル］にし、拡張子が「xxx.csv.txt」にならないようにします。
商品コード,商品名,数量
00123,ノート,2
00007,ペン,3
01020,ファイル,4
123,付せん,1この例では、あえて「00123」と「123」を別の商品にしています。数値に変換すると両方とも123になり、後から商品一覧と照合する際に区別できなくなるためです。すべてのコードを同じ桁数にそろえれば解決する、とは限りません。
元のCSVは、Excelで開いて上書き保存しないでください。中身の確認や編集にはメモ帳を使います。元データに0が残っていることが、正しく取り込み直すための前提です。
Power Queryで先頭の0を残して取り込む
1．ExcelからCSVを選ぶ
空のExcelブックを開き、［データ］→［テキストまたはCSVから］を選択します。見当たらない場合は、［データの取得］→［ファイルから］→［テキストまたはCSVから］を探し、先ほど保存したproducts.csvを指定してください。
取り込み画面で、文字コードがUTF-8、区切り記号がコンマになっていることを確認します。日本語が崩れている場合は、先頭の0とは別に文字コードの確認が必要です。
2．データ型の自動検出をオフにする
取り込み画面に［データ型検出］の選択肢がある場合は、［データ型を検出しない］を選びます。そのうえで［読み込み］ではなく［データの変換］を選択します。
自動検出をオフにすると、各列をテキストとして読み込めます。この仕組みはMicrosoftのText/CSVコネクタの説明でも確認できます。
3．商品コードと数量の型を分ける
Power Queryエディターで、商品コードが「00123」「00007」「01020」「123」と表示されていることを確認します。1行目がデータ扱いになっている場合は、［1行目をヘッダーとして使用］で列名にします。
商品コード列を選択し、列名の左にあるデータ型のアイコン、または［変換］タブの［データ型］から［テキスト］を選びます。商品名もテキスト、数量は［整数］にします。すでに正しい型なら変更は不要です。
この時点で123や7になっている場合は、そのまま進めず、次の節で型変換の順番を見直してください。最後のステップでテキストにしても、消えた0は戻りません。
4．読み込み結果を確認し、ブックを保存する
［ホーム］→［閉じて読み込む］でExcelのシートへ読み込みます。このサンプルの期待結果はデータ4行・数量合計10です。商品コードの0だけでなく、行数と数量も確認すると、途中で行を落としていないかを確認できます。
作業したブックは.xlsx形式で保存してください。CSVとして保存し直すのではなく、Power Queryの取り込み設定をExcelブック側に残します。以後も元のCSVは必要なので、保存場所を変えたり削除したりしないようにします。
すでに0が消えたときは「変更された型」を確認する
［データ］→［クエリと接続］から対象のクエリを編集し、右側の［適用したステップ］を確認します。自動で追加された［変更された型］など、データ型を変えるステップが手がかりです。
- 型変換の直前のステップを選び、商品コードに0が残っているか確認する。
- 次のステップで0が消えるなら、その型変換を見直す。
- この練習用の単純なクエリなら、問題の［変更された型］を削除し、商品コードをテキスト、数量を整数として設定し直す。
- 後続のステップで、再び商品コードが数値に変換されていないか確認する。
業務用のクエリでは、ステップを削除すると後続の計算などに影響することがあります。先にブックのコピーを保存し、どの列を変換している処理なのか確認してから修正してください。
もし元のCSV自体がすでに123になっている場合、Power Queryだけでは元が123・0123・00123のどれだったのか分かりません。バックアップや出力元のシステムから、元データを取り直すのが基本です。
更新しても0が残るか確認する
一度取り込めたら終わりではなく、元データが増えた場合も確認します。練習用のproducts.csvをメモ帳で開き、末尾の次の行に以下を追加して、同じ文字コードで保存してください。
00042,クリップ,5Excelに戻り、［データ］→［すべて更新］を実行します。データが5行・数量合計15になり、追加したコードが00042のままであれば、このサンプルの確認は完了です。Power Queryは更新時にも保存した変換手順を適用します。
よくある勘違いと注意点
表示形式「00000」と、文字列の「00123」は別
セルの表示形式を00000にすると、数値の123を00123と表示できます。ただし、セルの中身は数値の123のままです。元から5桁と決まっている番号を表示する用途には使えますが、今回のように123と00123を区別したい商品コードの取り込み対策にはなりません。
CSVで引用符を付けるだけでは安心できない
CSVに「”00123″」と書かれていても、読み込む側が数値に変換する場合があります。引用符はCSVの区切りなどを扱うためのもので、ExcelやPower Queryに文字列型を強制する指定ではありません。取り込み側の型も確認します。
長い番号も、数値に変える前にテキストへ
16桁以上の管理番号などは、Excelで数値として扱うと桁の精度が失われることもあります。後から表示形式を変えても、失われた桁は復元できません。計算しない識別番号は、先頭の0の有無にかかわらずテキストで取り込む方が適しています。
なお、Excelの一部のバージョンには、自動データ変換を変更する設定もあります。ただし、Power Queryの［適用したステップ］とは別の設定です。今回の方法では、クエリ側で列の型を明示して管理します。
まとめ
- 商品コードは、数字に見えてもテキストとして扱う。
- CSVの取り込み時に自動の型変換を確認する。
- すでに消えた場合は、元のCSVと、0が消える直前のステップを確認する。
- 取り込み直後だけでなく、更新後もコード・行数・数量を確認する。
ポイントは、0を後から足して見た目を整えるのではなく、元のコードを変えずに取り込むことです。商品一覧との照合や集計を始める前に、まずこの部分を確認しておきましょう。
コメント