この記事では、PowershellでMySQLのテーブルにCSVファイルのデータをインポートする方法についてご説明します。
今回どのようにインポートするのかというと、SQLのINSERT文を使って行います。
【動画】PowershellでMySQLのテーブルにCSVファイルのデータをインポートする実際の動き
本題に入る前に、まずは次の動画をご覧ください。
CSVファイルからデータを読み込み、INSERT文を実行してMySQLのテーブルにインポートしています。
コードの流れ
接続先のサーバ名、データベース名、ユーザ名、パスワードをここで指定します。
INSERT文を実行するのに、インポート先のテーブルのフィールド名が必要なので取得します。
CSVファイルとMySQLのデータベース・テーブルの例
CSVファイル
今回は下のCSVファイルを用意しました。
データが10件存在しています。
MySQLのデータベースとテーブル
今回は下のMySQLのデータベースとテーブルを用意しました。
「syain」のテーブルに10件データが存在しています。
このテーブルに、先ほどのCSVファイルのデータをインポートします。
インポートした結果は下のとおりです。
CSVファイルのデータが追加されています。
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
$server = "localhost" # 接続するサーバ名
$database = "testdb" # 接続するデータベース名
$user = "root" # ログインユーザ名
$password = "testPass@123456" # ログインユーザのパスワード
# フォルダパスを設定する
$folderPath = "C:\work\10_勉強\18_PowerShell\0014\"
# インポート元のCSVファイル名を設定する
$csvFileName = $folderPath + "data.csv"
# インポート先のテーブル名
$tableName = "syain"
# 接続文字列を設定する
$connectionString = "Server=$server;Database=$database;Uid=$user;Pwd=$password;"
# MySqlConnectionオブジェクトを生成する
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
# データベースに接続する
$connection.Open()
# コマンドオブジェクトを生成する
$command = $connection.CreateCommand()
# インポート先のテーブルのフィールド名を取得するSELECT文を用意する
$command.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS " +
"WHERE TABLE_SCHEMA = '$database' " +
"AND TABLE_NAME = '$tableName'"
# SELECT文を実行し、取得結果をreaderに格納する
$reader = $command.ExecuteReader()
# 空の配列を用意する
$columnNames = @()
# 取得したデータの最終行まで処理を繰り返すwhileループ
while ($reader.Read()) {
# readerの値をcolumnNamesに格納する
$columnNames += $reader['COLUMN_NAME']
}
# readerを閉じる
$reader.Close()
# CSVファイルからデータを読み込む
$csvData = Import-Csv -Path $csvFileName
# 取得したCSVデータの最終行まで処理を繰り返すforeachループ
foreach ($row in $csvData) {
# フィールド名に対する値を繰り返してすべて取得し、その値を「'」(カンマ)で囲ってinsertValuesに格納する
$insertValues = $columnNames | ForEach-Object { "'$($row.$_)'"}
# columnNamesの値を「,」カンマ区切りで連結しinsertColumnStringに格納する
$insertColumnString = $columnNames -join ", "
# insertValuesの値を「,」カンマ区切りで連結しinsertValueStringに格納する
$insertValueString = $insertValues -join ", "
# insertColumnString(フィールド名)とinsertValues(追加する値)をINSERT文に指定し、そのINSERT文をinsertQueryに格納する
$insertQuery = "INSERT INTO $tableName ($insertColumnString) VALUES ($insertValueString)"
# INSERT文をCommandTextプロパティに設定する
$command.CommandText = $insertQuery
# INSERT文を実行する
$command.ExecuteNonQuery() | Out-Null
}
# MySQLとの接続を閉じる
$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行目です。
# インポート先のテーブル名 $tableName = "syain" # フォルダパスを設定する $folderPath = "C:\work\10_勉強\18_PowerShell\0014\" # インポート元のCSVファイル名を設定する $csvFileName = $folderPath + "data.csv"
コードの説明
以上のコードは、MySQLのインポート先のテーブル名とCSVファイルのフルパスを設定するコードです。
注目すべきコード④
次に見て頂きたいのは23行目から26行目です。
# 接続文字列を設定する $connectionString = "Server=$server;Database=$database;Uid=$user;Pwd=$password;" # MySqlConnectionオブジェクトを生成する $connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
コードの説明
以上のコードは、接続文字列を作成してMySqlConnectionオブジェクトを生成するコードです。
MySQLのデータベースに接続するための接続情報をconnectionオブジェクトに格納します。
このコードにより、どのMySQLのデータベースに接続するのか、PowerShellが認識できるようになります。
注目すべきコード⑤
次に見て頂きたいのは29行目です。
# MySQLに接続する $connection.Open()
コードの説明
以上のコードは、PowerShellがデータベースに接続する処理のコードです。
注目すべきコード⑥
次に見て頂きたいのは32行目から40行目です。
# コマンドオブジェクトを生成する
$command = $connection.CreateCommand()
# インポート先のテーブルのフィールド名を取得するSELECT文を用意する
$command.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS " +
"WHERE TABLE_SCHEMA = '$database' " +
"AND TABLE_NAME = '$tableName'"
# SELECT文を実行し、取得結果をreaderに格納する
$reader = $command.ExecuteReader()
コードの説明
以上のコードは、コマンドオブジェクトを生成してインポート先のテーブルのフィールド名を取得するSELECT文を用意し、SELECT文を実行します。
コードの詳細
32行目のコードは、SELECT文を実行するためのコマンドオブジェクトを生成します。
35行目から37行目のコードでは、テーブルのフィールド名を取得するためのSELECT文を用意します。
TABLE_SCHEMAにデータベース名、TABLE_NAMEにはテーブル名を指定します。
SELECT文を実行した結果は下のとおりです。
「syain」のすべてのフィールド名が取得されました。
40行目のコードでは、SELECT文を実行します。
注目すべきコード⑦
次に見て頂きたいのは43行目です。
# 空の配列を用意する $columnNames = @()
コードの説明
以上のコードは、実行したSELECT文が抽出したフィールド名を格納する配列を用意するコードです。
フィールド名を格納しておく「箱」をここで用意しておきます。
注目すべきコード⑧
次に見て頂きたいのは46行目から53行目です。
# 取得したデータの最終行まで処理を繰り返すwhileループ
while ($reader.Read()) {
# readerの値をcolumnNamesに格納する
$columnNames += $reader['COLUMN_NAME']
}
# readerを閉じる
$reader.Close()
コードの説明
以上のコードは、readerに格納されているフィールド名をcolumnNamesに格納する処理のコードです。
フィールド名をcolumnNamesに格納し終わったらreaderを閉じます。
コードの詳細
46行目のコードは取得したデータの最終行まで処理を繰り返すwhileループです。
49行目のコードでは、readerに格納されているフィールド名をcolumnNamesに格納します。
53行目のコードでは、readerを閉じます。
注目すべきコード⑨
次に見て頂きたいのは56行目です。
# CSVファイルからデータを読み込む $csvData = Import-Csv -Path $csvFileName
コードの説明
以上のコードは、CSVファイルからデータを読み込む処理のコードです。
Import-CsvのコマンドレットにCSVファイルのフルパスを指定して実行すると、CSVファイルからデータが取得されてcsvDataに格納されます。
注目すべきコード⑩
次に見て頂きたいのは59行目です。
# 取得したCSVデータの最終行まで処理を繰り返すforeachループ
foreach ($row in $csvData) {
コードの説明
以上のコードは、取得したCSVデータの最終行まで処理を繰り返すforeachループです。
注目すべきコード⑪
次に見て頂きたいのは62行目から77行目です。
# フィールド名に対する値を繰り返してすべて取得し、その値を「'」(カンマ)で囲ってinsertValuesに格納する
$insertValues = $columnNames | ForEach-Object { "'$($row.$_)'"}
# columnNamesの値を「,」カンマ区切りで連結しinsertColumnStringに格納する
$insertColumnString = $columnNames -join ", "
# insertValuesの値を「,」カンマ区切りで連結しinsertValueStringに格納する
$insertValueString = $insertValues -join ", "
# insertColumnString(フィールド名)とinsertValues(追加する値)をINSERT文に指定し、そのINSERT文をinsertQueryに格納する
$insertQuery = "INSERT INTO $tableName ($insertColumnString) VALUES ($insertValueString)"
# INSERT文をCommandTextプロパティに設定する
$command.CommandText = $insertQuery
# INSERT文を実行する
$command.ExecuteNonQuery() | Out-Null
コードの説明
以上のコードは、取得したデータをINSERT文用に加工してINSERT文を実行する処理のコードです。
コードの詳細
61行目のコードでは、フィールド名に対応するCSVファイルの値を繰り返してすべて取得し、その値を「’」(カンマ)で囲ってinsertValuesに格納しています。
ForEach-ObjectはcolumnNamesの数だけ処理を繰り返すループ文で、今回はフィールドの数が4つなので4回ループしてフィールドに対応する値を取得します。
64行目のコードでは、columnNamesの値を「,」カンマ区切りで連結しinsertColumnStringに格納しています。
INSERT文のフィールド名の指定では、フィールド名をカンマ区切りで指定するので、「-join」演算子を使ってcolumnNamesの値を「,」カンマ区切りで連結します。
67行目のコードでは、insertValuesの値を「,」カンマ区切りで連結しinsertValueStringに格納しています。
INSERT文の値の指定では、追加したい値をカンマ区切りで指定するので、「-join」演算子を使ってinsertValuesの値を「,」カンマ区切りで連結します。
70行目のコードでは、insertColumnString(フィールド名)とinsertValues(追加する値)をINSERT文に指定し、そのINSERT文をinsertQueryに格納しています。
以上のコードで生成されたINSERT文は下のとおりです。
73行目ではCommandTextプロパティにINSERT文を設定し、77行目のExecuteNonQueryメソッドでINSERT文を実行します。
注目すべきコード⑫
次に見て頂きたいのは81行目です。
# MySQLとの接続を閉じる $connection.Close()
コードの説明
以上のコードは、MySQLとの接続を閉じる処理のコードです。
動作確認
「CSVファイルとMySQLのデータベース・テーブルの例」をご覧ください。
最後に
この記事では、PowershellでMySQLのテーブルにCSVファイルのデータをインポートする方法についてご説明しました。
PowershellでMySQLのテーブルにCSVファイルのデータをインポートしたい場合は本記事を参考にして頂けたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
