この記事では、SQL ServerのテーブルにCSVファイルのデータをインポートする方法についてご説明します。
【動画】SQL ServerのテーブルにCSVファイルのデータをインポートする実際の動き
本題に入る前に、まずは次の動画をご覧ください。
PowershellからSQL Serverのインスタンスを生成してTransferTextメソッドを実行し、SQL ServerのテーブルにCSVファイルのデータをインポートします。
コードの流れ
今回はSQL Server接続で接続するので、接続先のサーバ名、データベース名、ユーザ名、パスワードをここで指定します。
BULK INSERTはcsvなどのデータを、SQL Serverのテーブルにインポートできるコマンドです。
CSVファイルとSQL Serverのデータベース・テーブルの例
SQL CSVファイル
今回は下のCSVファイルを用意しました。
データが10件存在しています。
SQL Serverのデータベースとテーブル
今回は下のSQL Serverのデータベースとテーブルを用意しました。
「tbl_test」のテーブルに10件データが存在しています。
このテーブルに、先ほどのCSVファイルのデータをインポートします。
インポートした結果は下のとおりです。
CSVファイルのデータが追加されています。
PowerShellのコード(例)
# SQL Serverの接続情報を設定する
$serverName = "(LocalDB)\MSSQLLocalDB" # 接続するサーバ名
$databaseName = "CS_01" # 接続するデータベース名
$userId = "test_user" # ログインユーザ名
$password = "pass_str1" # ログインユーザのパスワード
# インポート先のテーブル名を取得する
$tableName = "tbl_test"
# フォルダパスを設定する
$folderPath = "C:\work\10_勉強\18_PowerShell\0009\"
# 取得したデータを出力するCSVファイル名を設定する
$csvFileName = $folderPath + "data.csv"
# 接続文字列を設定する(SQL Server認証の場合)
$connectionString = "Server=$serverName;Database=$databaseName;User Id=$userId;Password=$password;"
# SqlConnectionオブジェクトを生成する
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
# BULK INSERTのクエリを用意する
$query = @"
BULK INSERT $tableName
FROM '$csvFileName'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
"@
# SqlCommandオブジェクトを生成する
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
# SQL Serverに接続する
$connection.Open()
# BULK INSERTを実行する
$command.ExecuteNonQuery() | Out-Null
# SQL Serverとの接続を閉じる
$connection.Close()
注目すべきコード①
最初に見て頂きたいのは2行目から5行目です。
# SQL Serverの接続情報を設定する $serverName = "(LocalDB)\MSSQLLocalDB" # 接続するサーバ名 $databaseName = "CS_01" # 接続するデータベース名 $userId = "test_user" # ログインユーザ名 $password = "pass_str1" # ログインユーザのパスワード
コードの説明
以上のコードは、SQL Serverの接続情報を設定するコードです。
今回はSQL Server接続で接続するので、接続先のサーバ名、データベース名、ユーザ名、パスワードをここで指定します。
注目すべきコード②
次に見て頂きたいのは8行目から14行目です。
# インポート先のテーブル名を取得する $tableName = "tbl_test" # フォルダパスを設定する $folderPath = "C:\work\10_勉強\18_PowerShell\0009\" # 取得したデータを出力するCSVファイル名を設定する $csvFileName = $folderPath + "data.csv"
コードの説明
以上のコードは、SQL Serverのインポート先のテーブル名とCSVファイルのフルパスを設定するコードです。
注目すべきコード③
次に見て頂きたいのは17行目から20行目です。
# 接続文字列を設定する(SQL Server認証の場合) $connectionString = "Server=$serverName;Database=$databaseName;User Id=$userId;Password=$password;" # SqlConnectionオブジェクトを生成する $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
コードの説明
以上のコードは、接続文字列を作成してSqlConnectionオブジェクトを生成するコードです。
SQL Serverのデータベースに接続するための接続情報をconnectionオブジェクトに格納します。
このコードにより、どのSQL Serverのデータベースに接続するのか、PowerShellが認識できるようになります。
注目すべきコード④
次に見て頂きたいのは23行目から35行目です。
# BULK INSERTのクエリを用意する
$query = @"
BULK INSERT $tableName
FROM '$csvFileName'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
"@
# SqlCommandオブジェクトを生成する
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
コードの説明
以上のコードは、BULK INSERTを実行するためのSQL文を用意して、そのSQL文をSqlCommandメソッドに渡してSqlCommandオブジェクトを生成するコードです。
SqlCommandオブジェクトを生成することで、BULK INSERTを実行することができるようになります。
コードの詳細
23行目のコードにある「@」は、SQL文が複数行にわたる文字列を定義するための「@」です。
SQL文が複数行に渡る場合、最初に「@」を付けて、最後に「@」を記述して閉じます。(32行目)
24行目はBULK INSERTを実行するために、BULK INSERTの文字列を記述し、その後にCSVファイルのデータをインポートする先のテーブル名を指定します。
25行目では、FROM句の後にCSVファイルを指定します。
26行目ではBULK INSERTコマンドのオプションを指定するために必要なWITH句です。
28行目から30行目のコードは、BULK INSERTのオプションを指定しています。
28行目の「FIELDTERMINATOR」オプションは、CSVファイルのデータがカンマ区切りであることを認識させるオプションです。
29行目の「ROWTERMINATOR」オプションは、改行が「\n」の文字であることを認識させるオプションです。
30行目の「FIRSTROW」オプションは、CSVファイルのデータを読み込む開始行の位置を指定するオプションです。
35行目では、SqlCommandオブジェクトを生成しています。
SqlCommandオブジェクトを生成することで、BULKINSERTを実行することができるようになります。
注目すべきコード⑤
次に見て頂きたいのは38行目です。
# SQL Serverに接続する $connection.Open()
コードの説明
以上のコードは、PowerShellがデータベースに接続する処理のコードです。
注目すべきコード⑥
次に見て頂きたいのは41行目です。
# BULK INSERTを実行する $command.ExecuteNonQuery() | Out-Null
コードの説明
以上のコードは、ExecuteNonQueryメソッドを実行してBULKINSERTを実行する処理のコードです。
注目すべきコード⑦
次に見て頂きたいのは44行目です。
# SQL Serverとの接続を閉じる $connection.Close()
コードの説明
以上のコードは、SQL Serverとの接続を閉じる処理のコードです。
動作確認
「CSVファイルとSQL Serverのデータベースファイルの例」をご覧ください。
最後に
この記事では、SQL ServerのテーブルにCSVファイルのデータをインポートする方法についてご説明しました。
SQL ServerのテーブルにCSVファイルのデータをインポートしたい場合は本記事を参考にして頂けたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
