Office 2019 Excel 2019

Excelでテキストファイルを読み込む(Power Queryとテキストファイルウィザード)

投稿日:2019年7月18日 更新日:

テキストファイルをExcelに取り込む方法は、外部データとして読み込む方法と、Excelで開く方法がありますが、Excel2019では外部データとして読み込むと、テキストファイルウィザードではなく、Power Query(パワークエリ)が起動するようになりました。

設定によって、従来のテキストファイルウィザードを利用することもできますが、Power Queryで表示してデータ型を変更して読み込むことも決して難しいことではありません。

PowerQueryエディター

ここでは、Excel2019(永続ライセンス) バージョン 1906での解説です。

参考Power Query(パワークエリ)については、以下の記事が参考になると思います。分かりやすく解説されています。Power Queryって何?って方は、ぜひお読みください。お勧めの記事です。

【初心者向け】Power Queryで元データを整形しよう(概要編)

以下は、Microsoftのページです。

Power Query の概要 - Excel(Microsoft)

Power Query (Excel で get & Transform と呼ばれる) を使用すると、外部データをインポートまたは接続し、そのデータを作成できます (たとえば、列の削除、データ型の変更、テーブルのマージなど)。次に、クエリをグラフに読み込Excelグラフとレポートを作成できます。定期的にデータを更新して最新の情報に更新できます。(上記ページより)

Excel2019とExcel2016の[データ]タブの違い

Excel2019では、[データ]タブの内容が変わりました。

グループ名[外部データの取り込み]は[データの取得と変換]になっています。

Excel2019の[データ]タブ

以下は、Excel2016(永続ライセンス)の[データ]タブです。

Excel2016の[データ]タブ

Excel2016の[テキストファイル]から取り込む方法については、以下の記事で解説しています。

テキストファイルウィザードで外部データを取り込む

Excelでは、外部からさまざまな形式のデータを取り込むことができます。 [データ]タブにある[外部データの取り込み]機能を使って、テキスト ...

続きを見る

Excel2016で、Power Queryを使用するには、[新しいクエリ]をクリックして[ファイルから]をポイントして[CSVから]または[テキストから]をクリックします。

Excel2016の[データ]タブの[新しいクエリ]

参考Power Queryは、Excel2010とExcel2013にもアドインとしてインストールして使用することができます。

Excel2013にPower Queryをインストールして[POWER QUERY]タブを表示

Excel2013にPower QueryアドインをMicrosoftのダウンロードセンターのページからインストールしてみました。OSは、W ...

続きを見る

テキストファイル

テキストファイルの形式は拡張子で判断することができます。

拡張子とは、ファイルの種類を識別するもので、ファイル名の後ろにアルファベットで表示されているものです。

拡張子

Windowsの設定で拡張子を表示することができます。

その方法については、以下の記事で解説しています。

登録されている拡張子を表示するには(Windows7からWindows11まで)

拡張子とは、どのアプリケーションで開くことができるファイルなのかを示すファイル名の最後に表示される3文字程度の文字列のことです。 Windo ...

続きを見る

よく使うテキストファイルは、txt形式のテキストファイルや、[,](カンマ)で区切ったCSV形式のテキストファイルです。

TXT形式のテキストファイル

以下は、[サンプル1.txt]というテキストファイルをメモ帳で開いた状態です。タブ区切りのテキストファイルです。

TXT形式のテキストファイル

CSV形式のテキストファイル

[サンプル2.csv]というCSVファイルをメモ帳で開いた状態です。CSVファイルとは、カンマで区切られたテキストファイルのことです。

CSV形式のテキストファイル

取り込み元のデータを確認しておきます。

Excelは、データ型を自動で判定しますが、先頭に[0]がつく場合は、数値として取り扱われるため、[0]が取り除かれてしまいます。また、[1-1]や[1/2]などと入力されたものは、日付と認識されてしまいます。

取り込み元のテキストのデータの先頭に0がつく文字列がないか、[1-1]や[1/2]と入力されたものがないかを確認してください。

テキストファイルをPower Queryで表示した後にデータ型を変換することもできますが、事前に確認しておくことをお勧めします。

ここでは、上記のサンプルファイルを使って解説します。

先頭の0が消えてしまった時はTEXT関数が便利

すでに手遅れで、セル先頭の0が消えてしまった場合は、TEXT関数を使用すると便利です。

=TEXT(値,表示形式)

表示形式に"0000"と指定すると、先頭に0を付けた4桁の文字列になります。

text関数

TEXT 関数 - Office サポート(Microsoft)

[テキストまたはCSVから]のボタンから取り込む

[データ]タブの[データの取得と変換]グループにある[テキストまたはCSVから]をクリックします。

テキストまたはCSVから

テキスト、コンマ区切りの値、または書式付きテキスト(スペース区切り)ファイルからデータをインポートします。

[データ]タブの[テキストまたはCSVから]

[データの取り込み]ダイアログボックスが表示されます。目的のテキストファイルを選択して、[インポート]ボタンをクリックします。

ここでは、[サンプル2.csv]を選択します。

[データの取り込み]ダイアログボックス

データの変換

次に以下のようなウィンドウが表示されます。

CSVファイルの場合は、[区切り記号]が[コンマ]になっています。タブ区切りのテキストファイルの場合は、[タブ]となります。

[商品コード]と[商品番号]のデータが間違って認識されています。[商品コード]の先頭の0が消えていますし、[商品番号]も日付になっています。

[データの変換]ボタンをクリックします。

PowerQueryで読み込まれたデータ

すると、Power Queryエディターが表示されます。

Power Queryエディター

[商品コード]と[商品番号]の列を選択します。[Ctrl]キーや[Shift]キーを使うと複数列を選択できます。

[ホーム]タブの[データ型]をクリックして[テキスト]を選択します。

[ホーム]タブの[データ型]をクリックして[テキスト]を選択

以下の[列タイプの変更]ウィンドウが表示されます。

[現在のものを置換]をクリックします。

列タイプの変更

選択された列には、既存の型変換があります。既存の変換を置き換えますか?または、既存の変換を保持して、別の手順で新しい変換を追加しますか?

[列タイプの変更]ウィンドウ

データ型が置換されて、正常な表示になったことを確認します。

データ型が[テキスト]に変換された

Excelに読み込み

Excelに読み込みます。

[ホーム]タブの[閉じて読み込む]の下側の文字の箇所をクリックします。すると、[閉じて読み込む]と[閉じて次に読み込む...]のメニューが表示されます。

[閉じて次に読み込む…]をクリックします。

このクエリへの変更を保存します。クエリエディターウィンドウを閉じて、結果の読み込み先を指定してください。

[ホーム]タブの[閉じて次に読み込む...]

[データのインポート]ダイアログボックスが表示されます。表示する形式とデータの出力先を選択することができます。

既定では、[テーブル]と[新規ワークシート]が選択されています。

[データのインポート]ダイアログボックス

ここでは、[テーブル]と[既存のワークシート]を選択して[OK]ボタンをクリックします。

[既存のワークシート]を選択

Excelにテーブルとして取り込まれます。

テーブルを解除したい場合の方法については、以下で解説しています。

テーブルを解除して通常の表に変換するには

Excelにテーブルとして取り込まれた

テーブルにはとても便利な機能があります。

表をテーブルに変換してデータベース管理などの作業効率をアップ

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

また、テーブルであればExcelでリレーションシップの設定ができます。

テーブル間でリレーションシップを設定してピボットテーブルを作成

Excel2013以降では、テーブル間でリレーションシップを設定して1つのレポートを作成できるようになっています。 以下の記事では、[Pow ...

続きを見る

参考[ホーム]タブの[閉じて読み込む]を選択すると、[データのインポート]ダイアログボックスの既定どおり、[テーブル]で[新規ワークシート]に読み込まれます。それで、問題がなければ、[閉じて読み込む]をクリックしてください。

このクエリへの変更を保存します。クエリエディターウィンドウを閉じて、結果を既定の読み込み先に読み込んでください。

閉じて読み込む

読み込んだ後に編集するには

Excelに読み込んだあとに、データ型を変更する必要がある場合は[クエリ]タブをクリックして、[編集]グループの[編集]をクリックします。

編集

Power Queryエディターを使って、このクエリをフィルター処理または修正します。

[クエリ]タブの[編集]

Power Queryエディターが表示されますので、ここで修正することができます。

PowerQueryエディター

Power Queryエディターを起動するショートカットキーは、[Alt]+[F12]です。

キーボード[alt]+[f12]

参考[データ]タブの[データの取得と変換]グループにある[テキストまたはCSVから]をクリックして、すぐに[読み込み]ボタンをクリックしても、後で修正することもできます。また、自動認識されたデータ型に問題がなければ、[読み込み]ボタンをクリックしても構いません。

[読み込み]ボタンの▼ボタンをクリックすると、[読み込み]と[読み込み先...]がありますが、[読み込み先...]を選択した場合は、[データのインポート]ダイアログボックスが表示されますので、表示する形式や読み込み先を指定できます。

[読み込み]をクリックした場合は、既定どおり[新規ワークシート]に[テーブル]として読み込まれます。

[読み込み]と[読み込み先...]ボタン

テキストファイルを読み込んだブックを保存して再起動

Excelに読み込んだブックを保存して、再起動すると[セキュリティの警告 外部データ接続が無効になっています]のデータバーが表示されることがあります。

[コンテンツの有効化]をクリックすると、データの接続が有効になります。

セキュリティの警告[コンテンツの有効化]

参考Power Queryの入門書としておすすめです。

created by Rinker
★★★★★ マクロもVBAも必要なく標準機能だけでOK♪
Amazonのレビューを見る

データの更新を反映させる

データ元であるテキストファイルのデータが変更された場合は、データを更新して最新のデータを反映させることができます。

[データ]タブの[クエリと接続]グループにある[すべて更新]から[更新]をクリックします。

参考ブック内で複数の外部データとの接続がある場合、[すべて更新]をクリックすると、ブック内のすべての外部データの接続が更新されます。特定のデータのみ更新したい場合は、[更新]をクリックしてください。

ポップヒントで分かりますが、ショートカットキーは以下のとおりです。

  • すべて更新[Ctrl]+[Alt]+[F5]
  • 更新[Alt]+[F5]

Excel2016では、[テキストファイルのインポート]ダイアログボックスが表示されていましたが、Power Queryで読み込んだ場合は、すぐにデータが更新されます。

[データ]タブの[すべて更新]

または、[テーブルデザイン」タブの[外部のテーブルデータ]グループにある[更新]ボタンから操作します。

[テーブルデザイン」タブの[外部のテーブルデータ]グループにある[更新]ボタン

取り込みデータの変更

取り込み元のテキストファイルの保存場所が変更になったり、接続していたテキストファイルが別のファイルに変更になった場合も、Excelのデータを更新することができます。

[データ]タブの[クエリと接続]グループの[クエリと接続]をクリックします。ウィンドウ右側に[クエリと接続]作業ウィンドウが表示されます。

目的のクエリをダブルクリックします。

クエリと接続

[Power Query エディター]が表示されるので、クエリの設定にある[適用したステップ]の[ソース]をダブルクリックします。

クエリの設定の[ソース]をダブルクリック

以下のウィンドウが表示されます。

[ファイルパス]の[参照]ボタンをクリックします。

[ファイルパス]の[参照]ボタンをクリック

[データの取り込み]で目的のファイルを選択して[インポート]をクリックします。

[データの取り込み]で目的のファイルを選択して[インポート]

[ファイルパス]が変更されたことを確認して[OK]ボタンをクリックします。

ファイルパスの変更を確認

[Power Query エディター]に戻り、内容が変更されたことを確認して[ホーム]タブの[閉じて読み込む]をクリックします。

[Power Query エディター]を閉じて読み込む

以下のように内容が変更されます。

クエリでの変更が必要な場合は、目的のクエリで右クリックしてショートカットメニューから選択できます。

ファイルのパスの変更は[編集]をクリックしても[Power Query エディター]が表示されるので、上記と同じ操作ができます。

クエリを選択して右クリック

参考クエリの名前の変更や、削除も上記のショートカットメニューから行うことができます。

ショートカットメニューから[削除]をクリックすると、[クエリの削除]メッセージウィンドウが表示されます。クエリを削除すると、データの更新はできなくなります。

[クエリの削除]ウィンドウ

テーブルを解除して通常の表に変換するには

テーブルから範囲(通常の表)にしたい場合は、[デザイン]タブの[ツール]グループにある[範囲に変換]をクリックします。

([テーブルデザイン]タブとなっている場合もあります。Excelのバージョンによって、タブの表示が異なります。)

ただし、範囲に変換すると、クエリとの接続は解除されます。また、セルの背景色、文字色、罫線などの書式はテーブルのままになります。データはすべて保持されます。

テーブル内の1つのセルを選択して、[デザイン]タブの[ツール]グループにある[範囲に変換]をクリックします。

[デザイン]タブの[ツール]グループにある[範囲に変換]

[範囲に変換]をクリックすると、以下のメッセージウィンドウが表示されますので[OK]ボタンをクリックします。

この操作を行うと、シートからクエリ定義が解除され、テーブルが標準の範囲に変換されます。続行しますか?

メッセージウィンドウ

テーブルの書式をクリアしたい場合は、書式をクリアしたい範囲を選択して、[ホーム]タブの[編集]グループにある[クリア]から[書式のクリア]をクリックします。

[ホーム]タブの[編集]グループにある[クリア]

以下のように、すべての書式がクリアされます。

[取引日]などは、日付の書式もクリアされますので、必要であれば再度、日付の書式設定をしてください。

書式がクリアされた表

テーブルのスタイルをクリアして[範囲に変換]

もしくは、テーブルのスタイルをクリアして、[範囲に変換」を実行すると書式は残りませんので、こちらの方法もお勧めです。

[範囲に変換]を実行する前に、[デザイン]タブの[テーブルスタイル]グループの[その他]ボタンをクリックして、

[デザイン]タブの[テーブルスタイル]グループの[その他]ボタン

スタイル一覧の左上にある[なし]をクリックします。

そして、[デザイン]タブの[ツール]グループにある[範囲に変換]をクリックしてもいいと思います。

テーブルのスタイル一覧の左上にある[なし]をクリック

従来の[テキストファイルウィザード]を使用して読み込みたい場合

Power Queryを使用せずに従来と同じように[テキストファイルウィザード]を起動して読み込むこともできます。

[ファイル]タブをクリックしてBackstageビューを表示して、[オプション]をクリックします。

または、キーボードから[Alt]→[F]→[T]の順に押します。

キーボード[Alt]→[F]→[T]

[Excelのオプション]ダイアログボックスが表示されます。

[データ]タブの[レガシデータインポートウィザードの表示]にある[テキストから(レガシ)]のチェックボックスをオンにして[OK]ボタンをクリックします。

再起動の必要はありません。

Excelのオプション[データ]タブの[テキストから(レガシ)]のチェックボックスをオン

[データ]タブの[データの取得と変換]グループにある[データの取得]をクリックします。

[従来のウィザード]をポイントして[テキストから(レガシ)]をクリックします。

[データ]タブの[データの取得と変換]グループにある[データの取得]をクリック

テキストファイルウィザードで取り込む方法は、以下の記事で解説しています。

テキストファイルウィザードで外部データを取り込む

Excelでは、外部からさまざまな形式のデータを取り込むことができます。 [データ]タブにある[外部データの取り込み]機能を使って、テキスト ...

続きを見る

Excelで開くには

Excelを起動して、[ファイルを開く]ダイアログボックスを表示します。

[ファイルを開く]ダイアログボックスを表示する方法については、以下の記事を参照してください。

[ファイルを開く]ダイアログボックスを表示する

[Excelのオプション]でショートカットキー[Ctrl]+[O]で開くようにしておくと便利です。

ショートカットキー[Ctrl+O]で[ファイルを開く]を表示するには

Office2013では、[ファイルを開く]ダイアログボックスを開くためのショートカットキー[Ctrl]+[O]を押すと、Backstage ...

続きを見る

Excelのオプション[保存]タブ

タブ区切りのテキストファイルの場合

[ファイルを開く]ダイアログボックスの[すべてのExcelファイル]をクリックして、メニューの中から[テキストファイル]を選択します。

目的のテキスト(.txt)ファイルを選択して、[開く]ボタンをクリックします。

[ファイルを開く]ダイアログボックス

従来どおり、[テキストファイルウィザード]が起動しますので、[次へ]のボタンをクリックして進めていきます。

テキストファイルウィザードの進め方については、以下の記事で解説しています。

タブやカンマで区切られたテキストファイルをExcelで開くには

Excelでテキストファイルを開くには、テキストファイルウィザードを使います。 テキストファイルウィザードは、[ファイルを開く]ダイアログボ ...

続きを見る

テキストファイルウィザード

タブ区切りのテキストファイルの場合は、[ファイルを開く]ダイアログボックスから指定して開くことができます。

また、テキストファイルをメモ帳などで開いてコピーして、[貼り付けオプション]の[テキストファイルウィザードを使用]を選択する方法もあります。

貼り付けオプションの[テキストファイルウィザードの使用]が便利

Office2010の貼り付けのオプションは、リアルタイムプレビューという機能で貼り付けを確定する前に結果を確認することができます。 貼り付 ...

続きを見る

CSVファイルの場合

CSVファイルは、上記の方法([ファイルを開く]ダイアログボックスを表示して選択)で開いても、テキストファイルウィザードが起動しない場合があります。

すべてのファイルではないのですが、すぐにCSVファイルのデータがExcelに取り込まれてしまうことがあります。

CSVファイルを開いた場合

重要先頭に[0]がつく場合は、数値として取り扱われるため、[0]が取り除かれてしまいます。また、[1-1]や[1/2]などと入力されたものは、日付と認識されます。

取り込み元のテキストのデータの先頭に0がつく文字列がないか、[1-1]や[1/2]と入力されたものがないかを確認してください。

もし、上記に当てはまるデータであれば、[データ]タブの[データの取得と変換]グループにある[テキストまたはCSVから]機能を使って取り込んでから表示してください。

Microsoft 365のExcelには、自動データ変換の設定があります。

先頭にゼロが付くCSVファイルをダブルクリックで開くと、メッセージウィンドウが表示されるようになっています。

[開く]ダイアログボックスで開く場合も、メッセージウィンドウが表示されます。

オプションで「CSVの先頭の0を自動で変換しない」設定ができる

参考Excel2019でAccessのデータを取り込む方法は、以下の記事で解説しています。

Excel2019でAccessのデータをPower Queryで取り込むと更新も楽になる

AccessのデータベースをExcelに取り込むと、データベースの分析などはExcelの機能を使って行うことができます。 Excel2019 ...

続きを見る

PDFファイルのデータも取り込むことができます。

PDFファイルのデータをPower Queryでワークシートにインポート

Excel2021とMicrosoft 365のExcelでは、PDFファイルをPower Queryを使って取り込むことができます。 どん ...

続きを見る

Webページのデータを読み込む方法も解説しています。

Webページからデータをインポート(Power Queryで2つのテーブルの行を連結)

Excelでは、HTML形式のデータを取り込むことができます。社内イントラネットのデータや、一般的なWebページのデータなどを取り込むことが ...

続きを見る

Power Queryを使用すると、データベースの整形も行うことができます。

Power Queryでデータベースに整形(マトリックス表をテーブルに)

Power Query(パワークエリ)は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とE ...

続きを見る

先頭行に複数の同じ項目名がある表をPower Queryでテーブルに変換

Excel2016から標準で装備されているPower Query(パワークエリ)を使用すると、効率よくデータを加工できます。 Excelの表 ...

続きを見る

1列の複数行で1レコードになっている表をPower Queryで複数列のテーブルに変換

1列の複数行に1レコードのデータが並んでいる表も、Power Query(パワークエリ)エディターを使って、複数列のテーブルに整形することが ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Office 2019 Excel 2019

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.