テキストファイルに保存された売上データ、顧客一覧、ログデータなどをExcelで扱いたい場面は少なくありません。
しかし、TXTやCSVをそのまま開くと、内容が1列にまとまる、文字化けする、先頭のゼロが消えるといった問題が起こることがあります。
Excelでは、区切り文字を指定して読み込む方法、テキストファイルウィザードを使う方法、複数のファイルを一括で取り込む方法を選べます。
単発の変換ならファイルを開く方法、繰り返し処理ならデータタブからの取得、複数ファイルならPower Queryの活用が便利です。
テキストファイルをExcelへ変換するときの要点
・区切り文字がカンマかタブかを確認する
・1行目を見出しとして扱うか決める
・郵便番号や商品コードは文字列として読み込む
・文字化け時は文字コードをUTF-8またはShift_JISに切り替える
この記事では、TXT形式やCSV形式のテキストファイルをExcelの表へ変換し、保存まで行う手順をわかりやすく解説します。
テキストファイルをExcelへ変換する基本手順
それではまず、テキストファイルをExcelの表として読み込んで保存する基本手順について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 商品コード | 商品名 | 数量 |
| 00125 | ノート | 12 |
| 00126 | ペン | 8 |
このように、テキスト内で区切られていた値をA列、B列、C列へ分けることが変換作業の中心です。
ファイルを開く操作
Excelを起動し、ファイルタブから開くを選び、参照をクリックします。
ファイル選択画面の右下にあるファイルの種類を、すべてのファイルまたはテキストファイルへ変更しましょう。
目的のTXTファイルを選択して開くと、Excelのバージョンによっては区切り位置の指定画面、または読み込みプレビュー画面が表示されます。
ファイルが一覧に表示されないときは、拡張子TXTやCSVが表示対象から外れている可能性があります。
【操作のポイント】ファイルの種類を変更しても見つからない場合は、拡張子表示を確認し、保存場所も再確認します。
区切り文字の選択
カンマで区切られたCSVならカンマ、タブで区切られたTXTならタブを選択します。
プレビュー内で項目ごとに列が分かれていれば、選択した区切り文字は適切です。
たとえば、商品コード,商品名,数量という文字列ならカンマ区切りです。
一方、表計算ソフトから書き出されたテキストでは、見た目では空白に見えても実際にはタブ文字が使われているケースがあります。
区切り文字を誤ると、すべての項目がA列に入るため、プレビューで列の分かれ方を必ず確認します。
区切り文字の見分け方
カンマ区切りはCSV形式で多く使われます。
タブ区切りはTSV形式とも呼ばれ、文章内にカンマが含まれるデータに向いています。
【操作のポイント】データ内にカンマが多い住所や説明文がある場合は、引用符で囲まれているかも確認しましょう。
Excel形式での保存
読み込みが完了したら、先頭行が見出しになっていること、各列の値がずれていないことを確認します。
商品コードの00125が125に変わっている場合は、元に戻してから列の表示形式を文字列に設定すると安心です。
続いて、ファイルタブの名前を付けて保存を選択し、ファイルの種類でExcelブックを指定します。
保存形式をExcelブックに変更することで、列幅、数式、書式、フィルターなどを維持できるようになります。
【操作のポイント】元のTXTファイルを残したい場合は、別名でExcelブックとして保存し、上書きを避けます。
データタブから読み込む方法
続いては、Excelのデータタブを利用してテキストファイルを取り込む方法を確認していきます。
| 読み込み元 | 選択する機能 | 向いている用途 |
|---|---|---|
| TXT | テキストまたはCSVから | 定期的な取り込み |
| CSV | テキストまたはCSVから | 売上・顧客データ |
データタブから取り込む方法では、変換設定をクエリとして保持できるため、同じ形式のファイルを何度も扱う業務に適しています。
テキストまたはCSVからの選択
Excelのリボンにあるデータタブを開き、データの取得と変換からテキストまたはCSVからを選択します。
表示されるファイル選択画面で、取り込みたいテキストファイルを指定します。
この操作では、Excelがファイル内容を解析し、読み込み前にプレビューを表示します。
プレビュー画面を経由すると、実際にシートへ読み込む前に文字コードと区切り方を確認できます。
【操作のポイント】Excelのデータタブから開始すると、後から更新できる取り込み設定として保存されます。
文字コードと区切り記号の確認
プレビュー画面では、ファイルの元の文字コードと区切り記号を選択できます。
日本語が文字化けしているなら、文字コードをUTF-8、932 日本語 Shift_JIS、またはファイル作成元が指定した形式へ切り替えます。
文字コードは、文字をコンピューター上で扱うための変換ルールです。
海外サービスから出力したCSVはUTF-8であることが多く、古い業務システムのTXTはShift_JISで作成されていることがあります。
文字化けを手作業で修正するより、読み込み時の文字コードを正しく選び直すほうが安全です。
文字コード選択の目安
日本語が意味不明な記号になる場合は、UTF-8とShift_JISを順に切り替えてプレビューを比較します。
正しい文字列が表示された時点で、その設定を維持して読み込みます。
【操作のポイント】一部だけ文字化けする場合は、元ファイルの作成元で文字コードが混在していないか確認します。
読み込み先の指定
プレビューが正しければ、読み込みをクリックすると、新しいワークシートへテーブル形式で取り込めます。
編集を選ぶと、Power Queryエディターで不要な列の削除、列名の変更、データ型の設定を行えます。
取り込み先を既存のシート上に指定したい場合は、読み込み先の設定からセル位置を選択します。
毎月同じレイアウトのファイルを受け取るなら、最初に列の型を整えておくと更新作業が短縮されます。
【操作のポイント】取り込み後にデータが追加された場合は、テーブル内で右クリックし、更新を選ぶと再取得できます。
テキストファイルウィザードによる列分割
続いては、すでにA列へ貼り付いてしまった文字列を、区切り位置指定ウィザードで複数列へ分ける方法を確認していきます。
| 変換前のA列 | 変換後のA列 | 変換後のB列 |
|---|---|---|
| 00125,ノート,12 | 00125 | ノート |
| 00126,ペン,8 | 00126 | ペン |
区切り位置指定ウィザードは、外部ファイルを読み込む前だけでなく、シート内にあるデータの修正にも使えます。
変換対象の列選択
まず、1行目にヘッダーがあるサンプルデータなら、商品コード,商品名,数量と入力されたA列全体を選択します。
次に、データタブのデータツールにある区切り位置を選択しましょう。
| A | B | C | |
|---|---|---|---|
| 1 | 商品コード,商品名,数量 | ||
| 2 | 00125,ノート,12 |
選択範囲に空白の列がないかも確認します。
分割後のデータは右隣の列へ展開されるため、B列以降に必要な値がある場合は、別の場所へ移しておくと安全です。
【操作のポイント】変換前に対象列をコピーしておくと、区切り設定を誤った場合でも元の文字列を確認できます。
区切り記号の指定
ウィザードの1ページ目では、カンマやタブなどの文字で項目が区切られている場合に区切り記号付きを選択します。
次へ進み、区切り文字としてカンマにチェックを入れると、プレビューの縦線が各項目の境目に表示されます。
固定長を選ぶのは、文字数が決まった位置で分割する帳票形式のテキストです。
カンマ区切りのデータを固定長として処理すると、内容の途中で不自然に分割されるため注意が必要です。
固定長の利用例
社員番号が先頭6文字、部署コードが次の4文字というように、区切り記号なしで桁位置が決まっているテキストに利用します。
【操作のポイント】プレビューの線が期待した場所にないときは、区切り文字の選択を変更してから次へ進みます。
列データ形式の設定
ウィザードの最終画面では、各列を標準、文字列、日付、取り込まない列として指定できます。
商品コードや郵便番号のように先頭のゼロを残したい列は、プレビューで該当列をクリックして文字列を選択します。
日付データは、元データの並びに合わせてYMD、MDYなどを選ぶと、Excelの日付として計算できるようになります。
1行目にヘッダーがある場合の確認例
商品コード列を文字列にすると、00125の先頭ゼロを維持できます。
数量列を標準にすると、合計の数式を利用できます。
合計は数量がC2からC100の場合、=SUM(C2:C100)で計算できます。
数式を入力する列は数値として取り込まれている必要があり、文字列のままでは正しい集計結果にならないことがあります。
【操作のポイント】コード列と金額列で形式を分けることが、変換後のトラブルを減らす基本です。
複数テキストファイルの一括取り込み
続いては、複数のTXTファイルやCSVファイルをまとめてExcelへ取り込む方法を確認していきます。
| ファイル名 | 対象月 | 取り込み結果 |
|---|---|---|
| sales-april.csv | 4月 | 一覧へ追加 |
| sales-may.csv | 5月 | 一覧へ追加 |
毎月のデータが同じ列構成で保存されているなら、フォルダー単位で読み込むと手作業を減らせます。
フォルダーからのデータ取得
データタブからデータの取得を選び、ファイルから、フォルダーからの順にクリックします。
取り込み対象のCSVやTXTだけを保管したフォルダーを指定すると、その中のファイル一覧が表示されます。
一括処理用のフォルダーには、列構成が異なるファイルや不要なバックアップを混在させないことが重要です。
【操作のポイント】月ごとのファイル名に年月を含めておくと、取り込み後に出所を確認しやすくなります。
ファイルの結合と変換
一覧画面で結合して変換を選ぶと、Power Queryが各ファイルの共通した列を一つの表へ結合します。
最初に表示されるサンプルファイルで、区切り記号、文字コード、ヘッダーの扱いを確認します。
先頭行をヘッダーとして使用する設定にすると、商品コードや商品名といった列名を保ったまま結合できます。
サンプルファイルの設定はフォルダー内の他ファイルにも適用されるため、代表的なファイルを選びます。
一括取り込みに向く条件
すべてのファイルで列名、列の順序、区切り文字がほぼ同じであることが理想です。
異なる形式のファイルが混ざる場合は、先にフォルダーを分けておくと安定します。
【操作のポイント】結合前に1ファイルだけを開き、不要な空行や列名の違いがないか確認します。
更新による追加データの反映
新しいテキストファイルを同じフォルダーへ保存した後、Excelのクエリを更新すると一覧に追加データを反映できます。
手動でコピーと貼り付けを繰り返す必要がなく、集計用のピボットテーブルやグラフも更新しやすくなります。
ただし、同じデータを別名で保存すると重複行として取り込まれる可能性があります。
【操作のポイント】更新前にフォルダー内のファイル一覧を確認し、重複やテスト用ファイルを除外します。
文字化けとデータ形式の調整
続いては、変換後に起こりやすい文字化け、日付のずれ、先頭ゼロの消失を調整する方法を確認していきます。
| 症状 | 主な原因 | 対処 |
|---|---|---|
| 文字化け | 文字コードの不一致 | UTF-8またはShift_JISを選択 |
| 00125が125になる | 数値として認識 | 文字列として取り込み |
UTF-8とShift_JISの使い分け
文字化けの多くは、保存時と読み込み時の文字コードが一致していないことによって発生します。
WebサービスやクラウドシステムからダウンロードしたCSVはUTF-8、国内の旧来システムから出力されたテキストはShift_JISであることが一般的です。
Excelで直接開いて文字化けしたファイルも、データタブから読み込み直せば文字コードを指定できます。
【操作のポイント】元ファイルをメモ帳で開き、別名保存の文字コード表示を確認する方法も役立ちます。
先頭ゼロと長い番号の保持
郵便番号、社員番号、口座番号、バーコードなどは、数値ではなく識別用の文字列として扱うのが基本です。
特に16桁以上の番号は、Excelが数値として処理すると末尾の桁が丸められる場合があります。
読み込み段階で文字列を選択するか、事前に対象列の表示形式を文字列へ変更しましょう。
表示形式を後から文字列に変えても、すでに消えた先頭ゼロや丸められた桁は自動では戻りません。
【操作のポイント】番号を扱う列は、内容が計算対象か識別子かを最初に判断します。
日付と数値の再設定
日付が月日年の順に解釈される、金額が文字列として左寄せになるといった問題も、列のデータ型を見直すことで改善できます。
Power Queryでは、列を選択してデータ型を日付、整数、10進数、テキストなどへ変更できます。
変換後の合計を確認するには、たとえば金額がD2からD100にある場合、=SUM(D2:D100)を入力します。
数式の確認例
1行目にヘッダーがあり、D列が金額の場合、D101へ=SUM(D2:D100)を入力します。
先頭セルに入力後、集計範囲を確認してからオートフィルやテーブル集計を利用します。
【操作のポイント】数式の結果が0になる場合は、金額列が文字列として読み込まれていないか確認します。
まとめ テキストファイルをエクセルに変換する方法
テキストファイルをExcelへ変換する際は、データの量と作業頻度に合った方法を選ぶことが大切です。
単発のTXTやCSVなら、Excelで開いて区切り文字を指定し、Excelブックとして保存するとスムーズです。
定期的に同じ形式のファイルを扱う場合は、データタブのテキストまたはCSVから読み込む方法が特に便利です。
A列にデータがまとまってしまったときは、区切り位置指定ウィザードでカンマやタブを選択すると列へ分割できます。
複数ファイルを一括で処理したい場合は、フォルダーから取得してPower Queryで結合すると、更新だけで新しいデータを反映できます。
文字化けは文字コード、先頭ゼロの消失は列データ形式を確認することで防ぎやすくなります。
変換後は、見出し、列の並び、数値の集計結果を確認し、元データを残したうえでExcelブックとして保存しましょう。
