この記事では、PowershellでMySQLのテーブルにCSVファイルのデータをインポートする方法についてご説明します。
今回どのようにインポートするのかというと、bulkLoaderを使います。
【動画】PowershellでMySQLのテーブルにCSVファイルのデータをインポートする実際の動き
本題に入る前に、まずは次の動画をご覧ください。
bulkLoaderにインポート先のテーブル名やインポート元のCSVファイルのフルパスなどの情報を指定して実行しています。
コードの流れ
接続先のサーバ名、データベース名、ユーザ名、パスワードをここで指定します。
CSVファイルとMySQLのデータベース・テーブルの例
CSVファイル
今回は下のCSVファイルを用意しました。
データが10件存在しています。
MySQLのデータベースとテーブル
今回は下のMySQLのデータベースとテーブルを用意しました。
「syain」のテーブルに10件データが存在しています。
このテーブルに、先ほどのCSVファイルのデータをインポートします。
インポートした結果は下のとおりです。
CSVファイルのデータが追加されています。
【注意】bulkLoaderを使う際のCSVファイルの置き場について
bulkLoaderを使う際のCSVファイルの置き場について気を付けることがあります。
何に気を付けるのかというと、MySQLの設定のとおりに行う必要があるということです。
MySQLで設定された場所以外のフォルダに置いたCSVファイルをインポートすることができないので注意が必要です。
ではMySQLの設定は何かというと、「secure_file_priv」に設定されたパスのことです。
「secure_file_priv」の設定は「my.ini」(※Windowsの場合)で定義されています。
なので、もし「my.ini」ファイルの中身を書き換えたい場合は、事前に管理者と相談するようお願いします。(「my.ini」へのアクセスや触ること自体も危ないので事前に管理者に相談してください)
もし書き換える場合は、管理者から許可を頂いた上で自己責任でお願いします。
私の環境では「C:¥ProgramData¥MySQL¥MySQL Server 8.0¥Uploads¥」となっているので、「C:¥ProgramData¥MySQL¥MySQL Server 8.0¥Uploads¥」配下にインポート元のCSVファイルを格納します。
相談して問題ないことを確認してからパスへのアクセスやファイルを格納するようにしましょう。
以上でbulkLoaderを使う場合にCSVファイルのデータが読み込まれるようになります。
もし以上の置き場以外の場所(今回は下の「C:¥work¥01¥test」)をPowerShellで指定した場合は、CSVファイルを置いたとしても以下のエラーが発生します。
「secure_file_priv」に設定されたパスを確認して同じに指定するようにしましょう。(「secure_file_priv」に設定されたパス以外のパスを指定しないようにする)
PowerShellのコード(例)
# PowerShellからMySQLを操作するための「MySql.Data.dll」のフルパスを取得する
$diiPath = "C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySql.Data.dll"
# 「MySql.Data.dll」を読み込む
[System.Reflection.Assembly]::LoadFile($diiPath) | Out-Null
# MySQL接続情報
$server = "localhost" # 接続するサーバ名
$database = "testdb" # 接続するデータベース名
$user = "root" # ログインユーザ名
$password = "testPass@123456" # ログインユーザのパスワード
$table = "syain" # MySQLのテーブル名
# フォルダパスを設定する
$folderPath = "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\"
# 取得したデータを出力するCSVファイル名を設定する
$csvFile = $folderPath + "data.csv"
# CSVの区切り文字を指定する
$delimiter = ","
# 接続文字列を設定する
$connectionString = "server=$server;user=$user;password=$password;database=$database;"
# MySqlConnectionオブジェクトを生成する
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
# データベースに接続する
$connection.Open()
# MySqlBulkLoaderオブジェクトを生成する
$bulkLoader = New-Object MySql.Data.MySqlClient.MySqlBulkLoader($connection)
# bulkLoader オブジェクトのプロパティを設定する
$bulkLoader | ForEach-Object {
# インポート先のテーブル名を設定する
$_.TableName = $table
# 区切り文字を設定する
$_.FieldTerminator = $delimiter
# 改行文字を指定する
$_.LineTerminator = "\r\n"
# インポート元のCSVファイルを設定する
$_.FileName = $csvFile
# 1行目を読み込まないよう設定する(2行目から読み込む)
$_.NumberOfLinesToSkip = 1
}
# $bulkLoaderを使用してCSVファイルのデータをインポートする
$bulkLoader.Load() | Out-Null
# 接続を閉じる
$connection.Close()
注目すべきコード①
最初に見て頂きたいのは2行目から5行目です。
# PowerShellからMySQLを操作するための「MySql.Data.dll」のフルパスを取得する $diiPath = "C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySql.Data.dll" # 「MySql.Data.dll」を読み込む [System.Reflection.Assembly]::LoadFile($diiPath) | Out-Null
コードの説明
以上のコードは、PowerShellからMySQLを操作するための「MySql.Data.dll」を読み込むコードです。
「MySql.Data.dll」はPowerShellからMySQLを操作するのに必要なファイルで、PowerShellからMySQLへの接続やSQLクエリの実行などが可能になります。
「MySql.Data.dll」を読み込まないとMySQLへの操作ができないので、「MySql.Data.dll」の読み込みを忘れないようにしましょう。
「MySql.Data.dll」の読み込みはLoadFileメソッドの引数に「MySql.Data.dll」のフルパスを指定して実行します。
ちなみに、私のPCの環境では「C:\Program Files (x86)\MySQL\MySQL Installer for Windows」配下にある「MySql.Data.dll」を参照しています。
注目すべきコード②
次に見て頂きたいのは8行目から11行目です。
# MySQLの接続情報を設定する $server = "localhost" # 接続するサーバ名 $database = "testdb" # 接続するデータベース名 $user = "root" # ログインユーザ名 $password = "testPass@123456" # ログインユーザのパスワード
コードの説明
以上のコードは、MySQLの接続情報を設定するコードです。
今回はMySQL接続で接続するので、接続先のサーバ名、データベース名、ユーザ名、パスワードをここで指定します。
【参考】MySQLへの接続情報
注目すべきコード③
次に見て頂きたいのは14行目から20行目です。
# インポート先のテーブル名 $table = "syain" # フォルダパスを設定する $folderPath = "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\" # 取得したデータを出力するCSVファイル名を設定する $csvFile = $folderPath + "data.csv"
コードの説明
以上のコードは、MySQLのインポート先のテーブル名とCSVファイルのフルパスを設定するコードです。
17行目のCSVファイルのパスは、「secure_file_priv」で設定されているパスを指定しないとエラーになるので注意が必要です。
詳しくは「【注意】bulkLoaderを使う際のCSVファイルの置き場について」で説明しているのでそちらをご覧ください。
注目すべきコード④
次に見て頂きたいのは26行目から29行目です。
# 接続文字列を設定する $connectionString = "server=$server;user=$user;password=$password;database=$database;" # MySqlConnectionオブジェクトを生成する $connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
コードの説明
以上のコードは、接続文字列を作成してMySqlConnectionオブジェクトを生成するコードです。
MySQLのデータベースに接続するための接続情報をconnectionオブジェクトに格納します。
このコードにより、どのMySQLのデータベースに接続するのか、PowerShellが認識できるようになります。
注目すべきコード⑤
次に見て頂きたいのは32行目です。
# MySQLに接続する $connection.Open()
コードの説明
以上のコードは、PowerShellがデータベースに接続する処理のコードです。
注目すべきコード⑥
次に見て頂きたいのは35行目です。
$bulkLoader = New-Object MySql.Data.MySqlClient.MySqlBulkLoader($connection)
コードの説明
以上のコードは、MySqlBulkLoaderオブジェクトを生成するコードです。
MySqlBulkLoaderオブジェクトは、bulkLoaderの実行やbulkLoaderの各設定値を設定するのに必要なオブジェクトです。
注目すべきコード⑦
次に見て頂きたいのは43行目です。
# bulkLoader オブジェクトのプロパティを設定する
$bulkLoader | ForEach-Object {
# インポート先のテーブル名を設定する
$_.TableName = $table
# 区切り文字を設定する
$_.FieldTerminator = $delimiter
# 改行文字を指定する
$_.LineTerminator = "\r\n"
# インポート元のCSVファイルを設定する
$_.FileName = $csvFile
# 1行目を読み込まないよう設定する(2行目から読み込む)
$_.NumberOfLinesToSkip = 1
}
コードの説明
以上のコードは、bulkLoaderの各設定値を設定する処理のコードです。
コードの詳細
41行目のコードでは、TableNameプロパティにインポート先のテーブル名を設定しています。
44行目のコードでは、FieldTerminatorプロパティに区切り文字を設定しています。($delimiterには「,」(カンマ)が格納されています)
47行目のコードでは、LineTerminatorプロパティに改行文字(¥r¥n)を設定しています。
50行目のコードでは、FileNameプロパティにインポート元のCSVファイルを設定しています。
53行目のコードでは、NumberOfLinesToSkipプロパティにスキップする行を設定しています。
今回の場合、CSVファイルの1行目が項目名なので1行目をスキップするために1を指定しています。(2行目から読み込む)
注目すべきコード⑧
次に見て頂きたいのは46行目から53行目です。
# $bulkLoaderを使用してCSVファイルのデータをインポートする $bulkLoader.Load() | Out-Null
コードの説明
以上のコードは、readerに格納されているフィールド名をcolumnNamesに格納する処理のコードです。
フィールド名をcolumnNamesに格納し終わったらreaderを閉じます。
コードの詳細
46行目のコードは取得したデータの最終行まで処理を繰り返すwhileループです。
49行目のコードでは、readerに格納されているフィールド名をcolumnNamesに格納します。
53行目のコードでは、readerを閉じます。
注目すべきコード⑨
次に見て頂きたいのは56行目です。
# bulkLoaderを使用してCSVファイルのデータをインポートする $bulkLoader.Load() | Out-Null
コードの説明
以上のコードは、bulkLoaderを使用してCSVファイルのデータをインポートする処理のコードです。
Loadメソッドを実行することで、CSVファイルのデータがテーブルにインポートされます。
注目すべきコード⑩
次に見て頂きたいのは60行目です。
# MySQLとの接続を閉じる $connection.Close()
コードの説明
以上のコードは、MySQLとの接続を閉じる処理のコードです。
動作確認
「CSVファイルとMySQLのデータベース・テーブルの例」をご覧ください。
最後に
この記事では、PowershellでMySQLのテーブルにCSVファイルのデータをインポートする方法についてご説明しました。
今回はCSVファイルのデータをインポートするのにbulkLoaderを使用しています。
CSVファイルの置き場に気を付ける必要がありますが、PowershellでMySQLのテーブルにCSVファイルのデータをインポートしたい場合は本記事を参考にして頂けたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
