いつもお世話になっております。
RfromL.comです。
今回はExcel表で作成したデータ表から簡単にインサート文を作成する手順についてです。
なお、インサート文作成に際してサクラエディタを使用していますが、置換機能と正規表現が扱えるテキストエディタ、もしくは置換機能と矩形選択が扱えるテキストエディタであれば同様のことができます。
CONTENTS
1.はじめに
1-1.作成例
1-2.実例で使用するテーブル
1-3.検証環境
2.置換機能と正規表現で作成
2-1.作成方法の簡単な流れ
2-2.手順の実施
3.置換機能と正規表現と矩形選択で作成
3-1.作成方法の簡単な流れ
3-2.手順の実施
4.おわりに
1.はじめに
データベース上のデータを参照し、テーブルの値によって処理の変わるプログラムを作成、または修正する際にはテストケースとケースに合わせたデータが必要です。
既存のデータでテストケース全てを網羅したデータが存在していればいいですが、新規の項目値が追加されたことによる修正だった場合はテスト用にデータを用意する必要があります。
データの用意の仕方については人それぞれですが、大体がExcelでテストケースに合わせてデータ表を作成します。
作成したExcelデータ表のデータをテーブルに登録する際、一旦CSVファイルにしてBCPコマンドでインポートする、Excel関数でinsert文を生成する等やりかたはいろいろありますが、今回はサクラエディタを使用してExcelデータ表から簡単にinsert文を作成して登録する方法について説明します。
なお、作成の手順としては、以下の２種類の方法を記載します。
①置換機能と正規表現で作成
②置換機能と正規表現と矩形選択で作成
いずれもそんなに手間は変わらないので、どっちを使うかは完全に好みです。
1-1.作成例
今回手順説明で使用する例としては、以下のようなExcel表のデータをテーブルに登録するInsertコマンドを作成します。
[作成元Excelデータ表]
上記のExcelデータをサクラエディタで編集して以下のinsert文を作成します。
[作成Insert文]
例として作成するinsert文は、1データレコードを1インサート文の形で作成しますが、ちょっとやり方を変えれば1インサート文で登録するSQL文も作れます。
1-2.実例で使用するテーブル
今回紹介するSQL文を実行するにあたって使用するテーブル、及びデータについて記載しておきます。
[使用テーブル]
テーブルは以下のテーブルを用意します。
・「銘柄基本」
「銘柄基本」テーブル定義
| No | 項目名 | 型 | 桁 | Null | KEY |
| 1 | 銘柄コード | char | 4 | NG | 1 |
| 2 | 銘柄名 | char | 60 | NG | |
| 3 | 銘柄区分 | char | 1 | NG | |
| 4 | 運用資産区分 | char | 2 | NG | |
| 5 | 基準年月日 | char | 3 | NG | 2 |
1-3.検証環境
今回の手順作成実施で使用した環境は以下のバージョンとなっています。
| OS | Windows11 Home 64ビット |
| DBMS | SQL Server 2019 |
| 統合環境 | SQL Server Management Studio v18.10 |
2.置換機能と正規表現で作成
ひとつめの手順は「置換機能」、「正規表現」でインサート文を作成する方法です。
2-1.作成方法の簡単な流れ
作成方法の簡単な流れを記述すると以下のように行います。
①Excel表から、データ部分をコピーしてサクラエディタに貼り付ける
②置換機能と正規表現で各行の先頭に「insert into テーブル名 values(‘」を付与する
③置換機能で正規表現で「タブ」を「’,’」に置換する
④置換機能と正規表現で各行の末尾に「’);」を付与する
※「②」の「テーブル名」の箇所は、インサート先のテーブル名を記載します。
上記手順を一言で表現すると「３回置換する」という方法です。
2-2.手順の実施
手順に沿って実際にinsert文を作成していきます。
手順① Excel表から、データ部分をコピーしてサクラエディタに張り付ける
項目名が記載されたヘッダーを除いたデータ部分を選択してコピーします。
サクラエディタを開き、コピーした内容を貼り付けます。
手順② 置換機能と正規表現で各行の先頭に「insert into テーブル名 values(‘」を付与する
[CTRL]キー ＋ [R]キー を押下して「置換」ウィンドウを表示します。
ウィンドウが表示されたらそれぞれ以下のように指定を行い「すべて置換」ボタンをクリックします。
| 1 | 「置換前(N)」入力欄 | ^ |
| 2 | 「置換後(P)」入力欄 | insert into 銘柄基本 values(‘ |
| 3 | 「正規表現(E)」チェックボックス | チェック（正規表現を有効にする） |
※「置換前」入力欄に指定している「^」は「行頭」を意味する正規表現です。
※「すべて置換(A)」ボタンを押さなくても、キーボードの[ALT]キー ＋ [A]キー を押下することでも置換を実行できます。
置換が実行されると、以下のように各行の先頭に「insert into 銘柄基本 values(‘」が付与されます。
手順③ 置換機能で「タブ」を「’,’」に置換する
[CTRL]キー ＋ [R]キー を押下して「置換」ウィンドウを表示します。
ウィンドウが表示されたらそれぞれ以下のように指定を行い「すべて置換」ボタンをクリックします。
| 1 | 「置換前(N)」入力欄 | ¥t |
| 2 | 「置換後(P)」入力欄 | ‘,’ |
| 3 | 「正規表現(E)」チェックボックス | チェック（正規表現を有効にする） |
※「置換前」入力欄に指定している「¥t」は「水平タブ」を意味する正規表現です。
置換が実行されると、以下のように「タブ」が設定されていた箇所が「’,’」に置き換わります。
手順④ 置換機能と正規表現で各行の末尾に「’);」を付与する
[CTRL]キー ＋ [R]キー を押下して「置換」ウィンドウを表示します。
ウィンドウが表示されたらそれぞれ以下のように指定を行い「すべて置換」ボタンをクリックします。
| 1 | 「置換前(N)」入力欄 | $ |
| 2 | 「置換後(P)」入力欄 | ‘); |
| 3 | 「正規表現(E)」チェックボックス | チェック（正規表現を有効にする） |
※「置換前」入力欄に指定している「$」は「行末」を意味する正規表現です。
置換が実行されると、以下のように各行の末尾に「’);」が付与されます。
これでinsert文が完成しました。
作成したinsert文を実際に実行してみます。
正常に実行できることが確認できました。
3.置換機能と正規表現と矩形選択で作成
ふたつめの手順は「置換機能」、「正規表現」、「矩形選択」でインサート文を作成する方法です。
3-1.作成方法の簡単な流れ
作成方法の簡単な流れを記述すると以下のように行います。
①Excel表から、データ部分をコピーしてサクラエディタに貼り付ける
②1行目の先頭に「insert into テーブル名 values(‘」を記述する
③「②」で記述した内容をコピーする
④矩形選択で2行目以降の行頭部分を選択して、「③」でコピーした内容を貼り付ける
⑤置換機能で正規表現で「タブ」を「’,’」に置換する
⑥1行目の末尾に「’);」を記述する
⑦「⑥」で記述した内容をコピーする
⑧矩形選択で2行目以降の行末部分を選択して、「⑦」でコピーした内容を張り付ける
※「②」の「テーブル名」の箇所は、インサート先のテーブル名を記載します。
3-2.手順の実施
手順に沿って実際にinsert文を作成していきます。
手順① Excel表から、データ部分をコピーしてサクラエディタに張り付ける
項目名が記載されたヘッダーを除いたデータ部分を選択してコピーします。
サクラエディタを開き、コピーした内容を貼り付けます。
手順② 1行目の先頭に「insert into テーブル名 values(‘」を記述する
今回の例ではinsert先のテーブルが「銘柄基本」なので「insert into 銘柄基本 values(‘」と記述します。
手順③ 「②」で記述した内容をコピーする
手順④ 矩形選択で2行目以降の行頭部分を選択して、「③」でコピーした内容を貼り付ける
カーソルを2行目の先頭に配置して、[ALT]キー ＋ [矢印]キー（[←][↑][→][↓]いずれかのキー）を押下して矩形選択モードにします。
矩形選択モードのまま10行目までカーソルを移動して[CTRL]キー ＋ [V]キー で、コピーしておいた「insert into 銘柄基本 values(‘」を貼り付けます。
手順⑤ 置換機能で正規表現で「タブ」を「’,’」に置換する
[CTRL]キー ＋ [R]キー を押下して「置換」ウィンドウを表示します。
ウィンドウが表示されたらそれぞれ以下のように指定を行い「すべて置換」ボタンをクリックします。
| 1 | 「置換前(N)」入力欄 | ¥t |
| 2 | 「置換後(P)」入力欄 | ‘,’ |
| 3 | 「正規表現(E)」チェックボックス | チェック（正規表現を有効にする） |
※「置換前」入力欄に指定している「¥t」は「水平タブ」を意味する正規表現です。
置換が実行されると、以下のように「タブ」が設定されていた箇所が「’,’」に置き換わります。
手順⑥ 1行目の末尾に「’);」を記述する
手順⑦ 「⑥」で記述した内容をコピーする
手順⑧ 矩形選択で2行目以降の行末部分を選択して、「⑦」でコピーした内容を張り付ける
カーソルを2行目の末尾に配置して、[ALT]キー ＋ [矢印]キー（[←][↑][→][↓]いずれかのキー）を押下して矩形選択モードにします。
矩形選択モードのまま10行目までカーソルを移動して、[CTRL]キー ＋ [V]キー でコピーしておいた「’);」を貼り付けます。
これでinsert文が完成しました。
作成したinsert文を実際に実行してみます。
正常に実行できることが確認できました。
4.おわりに
今回はExcel表のデータからサクラエディタを使用してinsert文を作成する手順を２種類紹介しました。
最終的に出来上がるinsert文はどちらも同じものなので、結果だけみると同じものです。
僕個人の場合はその時の気分でどっちも良く使っていたので、結論としてはどっちの方法で作ってもいいのですが（たぶん好みもあります）無理やり使い分けを考えるなら、以下のパターンかなと思います。
①置換機能と正規表現
⇒カーソル移動しなくても実施できるので、行数が多くても手間が変わらない。
行数の多いデータからinsert文を作りたい時
②置換機能と正規表現と矩形選択
⇒カーソル移動が若干多いので、行数が多いと少し面倒。
行数の少ないデータからinsert文を作りたい時
以上です。
宜しくお願い致します。