この記事では、PowershellでSQLiteのテーブルにCSVファイルのデータをインポートする方法についてご説明します。
今回どのようにインポートするのかというと、SQLのINSERT文を使って行います。
【動画】PowershellでSQLiteのテーブルにCSVファイルのデータをインポートする実際の動き
本題に入る前に、まずは次の動画をご覧ください。
CSVファイルからデータを読み込み、INSERT文を実行してSQLiteのテーブルにインポートしています。
コードの流れ
「System.Data.SQLite.dll」を読み込むことで、PowerShellからSQLiteへの接続やSQLクエリの実行などが可能になります。
「System.Data.SQLite.dll」がないとPowerShellからSQLiteへの接続やSQLクエリの実行などができないので、「System.Data.SQLite.dll」を読み込みます。
INSERT文を実行するのに、インポート先のテーブルのフィールド名が必要なので取得します。
CSVファイルとSQLiteのデータベース・テーブルの例
CSVファイル
今回は下のCSVファイルを用意しました。
データが10件存在しています。
SQLiteのデータベースとテーブル
今回は下のSQLiteのデータベースとテーブルを用意しました。
「syain」のテーブルに10件データが存在しています。
このテーブルに、先ほどのCSVファイルのデータをインポートします。
インポートした結果は下のとおりです。
CSVファイルのデータが追加されています。
PowerShellのコード(例)
# フォルダパスを設定する
$folderPath = "C:\work\10_勉強\18_PowerShell\0023\"
# PowerShellからSQLiteを操作するための「System.Data.SQLite.dll」を読み込む
Add-Type -Path ($folderPath, "System.Data.SQLite.dll" -join '')
# SQLiteのデータベースファイルのフルパスを設定する
$databasePath = ($folderPath, "0023_DB01.db" -join '')
# インポート元のCSVファイル名を設定する
$csvFileName = $folderPath + "data.csv"
# インポート先のテーブル名
$tableName = "syain"
# 接続文字列を設定する
$connectionString = "Data Source=$databasePath;Version=3;"
# SQLiteConnectionオブジェクトを生成する
$connection = New-Object System.Data.SQLite.SQLiteConnection($connectionString)
# SQLiteに接続する
$connection.Open()
# SQLiteCommandオブジェクトを生成する
$command = New-Object System.Data.SQLite.SQLiteCommand($connection)
# インポート先のテーブルのフィールド名を取得するPRAGMAステートメントを用意する
$command.CommandText = "PRAGMA table_info($tableName)"
# PRAGMAステートメントを実行する
$reader = $command.ExecuteReader()
# 空の配列を用意する
$columnNames = @()
# 取得したデータの最終行まで処理を繰り返すwhileループ
while ($reader.Read()) {
# readerの値をcolumnNamesに格納する
$columnNames += $reader["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
}
# SQLiteとの接続を閉じる
$connection.Close()
注目すべきコード①
最初に見て頂きたいのは2行目から5行目です。
# フォルダパスを設定する $folderPath = "C:\work\10_勉強\18_PowerShell\0023\" # PowerShellからSQLiteを操作するための「System.Data.SQLite.dll」を読み込む Add-Type -Path ($folderPath, "System.Data.SQLite.dll" -join '')
コードの説明
以上のコードは、PowerShellからSQLiteを操作するための「System.Data.SQLite.dll」を読み込むコードです。
今回はSQLiteのデータベースファイルに接続するので「System.Data.SQLite.dll」を読み込みます。
「System.Data.SQLite.dll」を読み込むことで、データベースにアクセスするために必要なクラスやメソッドを使用することができるようになります。
注目すべきコード②
次に見て頂きたいのは8行目です。
# SQLiteのデータベースファイルのフルパスを設定する $databasePath = ($folderPath, "0023_DB01.db" -join '')
コードの説明
以上のコードは、SQLiteのデータベースファイルのフルパスを取得するコードです。
注目すべきコード③
次に見て頂きたいのは11行目です。
# インポート元のCSVファイル名を設定する $csvFileName = $folderPath + "data.csv"
コードの説明
以上のコードは、取得したデータを出力するCSVファイル名を設定するコードです。
注目すべきコード④
次に見て頂きたいのは14行目から17行目です。
# インポート先のテーブル名 $tableName = "syain" # 接続文字列を設定する $connectionString = "Data Source=$databasePath;Version=3;" # SQLiteConnectionオブジェクトを生成する $connection = New-Object System.Data.SQLite.SQLiteConnection($connectionString)
コードの説明
以上のコードは、インポート先のテーブル名の設定、および接続文字列を作成してSQLiteConnectionオブジェクトを生成するコードです。
今回CSVファイルのデータをインポートするテーブル名は「syain」です。
また、SQLiteのデータベースに接続するための接続情報をSQLiteConnectionオブジェクトに格納します。
このコードにより、どのSQLiteのデータベースに接続するのか、PowerShellが認識できるようになります。
注目すべきコード⑤
次に見て頂きたいのは23行目です。
# SQLiteに接続する $connection.Open()
コードの説明
以上のコードは、PowerShellがデータベースに接続する処理のコードです。
注目すべきコード⑥
次に見て頂きたいのは26行目から32行目です。
# SQLiteCommandオブジェクトを生成する $command = New-Object System.Data.SQLite.SQLiteCommand($connection) # インポート先のテーブルのフィールド名を取得するPRAGMAステートメントを用意する $command.CommandText = "PRAGMA table_info($tableName)" # PRAGMAステートメントを実行する $reader = $command.ExecuteReader()
コードの説明
以上のコードは、コマンドオブジェクトを生成し、インポート先のテーブルのフィールド名を取得します。
PRAGMAステートメントを使用することで、テーブルのフィールド名を取得することができます。
コードの詳細
26行目のコードは、PRAGMAステートメントを実行するためのSQLiteCommandオブジェクトを生成します。
29行目のコードでは、テーブルのフィールド名を取得するためのPRAGMAステートメントを用意します。
PRAGMAステートメントを実行した結果は下のとおりです。
「name」の列にテーブルのフィールド名が出力されています。
32行目のコードでは、PRAGMAステートメントを実行します。
注目すべきコード⑦
次に見て頂きたいのは35行目です。
# 空の配列を用意する $columnNames = @()
コードの説明
以上のコードは、実行したSELECT文が抽出したフィールド名を格納する配列を用意するコードです。
フィールド名を格納しておく「箱」をここで用意しておきます。
注目すべきコード⑧
次に見て頂きたいのは38行目から45行目です。
# 取得したデータの最終行まで処理を繰り返すwhileループ
while ($reader.Read()) {
# readerの値をcolumnNamesに格納する
$columnNames += $reader["name"]
}
# readerを閉じる
$reader.Close()
コードの説明
以上のコードは、readerに格納されているフィールド名をcolumnNamesに格納する処理のコードです。
フィールド名をcolumnNamesに格納し終わったらreaderを閉じます。
コードの詳細
38行目のコードは取得したデータの最終行まで処理を繰り返すwhileループです。
41行目のコードでは、readerに格納されているフィールド名をcolumnNamesに格納します。
readerの引数に「name」を指定することで、PRAGMAステートメントを実行結果の「name」列名の値を取得することができます。
「name」列名の値はテーブルの列名なので、ここでテーブルの列名がcolumnNamesに格納されます。
45行目のコードでは、readerを閉じます。
注目すべきコード⑨
次に見て頂きたいのは48行目です。
# CSVファイルからデータを読み込む $csvData = Import-Csv -Path $csvFileName
コードの説明
以上のコードは、CSVファイルからデータを読み込む処理のコードです。
Import-CsvのコマンドレットにCSVファイルのフルパスを指定して実行すると、CSVファイルからデータが取得されてcsvDataに格納されます。
注目すべきコード⑩
次に見て頂きたいのは51行目です。
# 取得したCSVデータの最終行まで処理を繰り返すforeachループ
foreach ($row in $csvData) {
コードの説明
以上のコードは、取得したCSVデータの最終行まで処理を繰り返すforeachループです。
注目すべきコード⑪
次に見て頂きたいのは53行目から69行目です。
# フィールド名に対する値を繰り返してすべて取得し、その値を「'」(カンマ)で囲って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文を実行する処理のコードです。
コードの詳細
54行目のコードでは、フィールド名に対応するCSVファイルの値を繰り返してすべて取得し、その値を「’」(カンマ)で囲ってinsertValuesに格納しています。
ForEach-ObjectはcolumnNamesの数だけ処理を繰り返すループ文で、今回はフィールドの数が4つなので4回ループしてフィールドに対応する値を取得します。
57行目のコードでは、columnNamesの値を「,」カンマ区切りで連結しinsertColumnStringに格納しています。
INSERT文のフィールド名の指定では、フィールド名をカンマ区切りで指定するので、「-join」演算子を使ってcolumnNamesの値を「,」カンマ区切りで連結します。
60行目のコードでは、insertValuesの値を「,」カンマ区切りで連結しinsertValueStringに格納しています。
INSERT文の値の指定では、追加したい値をカンマ区切りで指定するので、「-join」演算子を使ってinsertValuesの値を「,」カンマ区切りで連結します。
63行目のコードでは、insertColumnString(フィールド名)とinsertValues(追加する値)をINSERT文に指定し、そのINSERT文をinsertQueryに格納しています。
以上のコードで生成されたINSERT文は下のとおりです。
66行目ではCommandTextプロパティにINSERT文を設定し、69行目のExecuteNonQueryメソッドでINSERT文を実行します。
注目すべきコード⑫
次に見て頂きたいのは74行目です。
# SQLiteとの接続を閉じる $connection.Close()
コードの説明
以上のコードは、SQLiteとの接続を閉じる処理のコードです。
動作確認
「CSVファイルとSQLiteのデータベース・テーブルの例」をご覧ください。
最後に
この記事では、PowershellでSQLiteのテーブルにCSVファイルのデータをインポートする方法についてご説明しました。
PowershellでSQLiteのテーブルにCSVファイルのデータをインポートしたい場合は本記事を参考にして頂けたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
