質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

91.26%

  • PostgreSQL

    749questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

  • CSV

    425questions

    CSV(Comma-Separated Values)はコンマで区切られた明白なテキスト値のリストです。もしくは、そのフォーマットでひとつ以上のリストを含むファイルを指します。

  • VBScript

    163questions

    VBScript(Visual Basic Scripting Edition)はMicrosftが開発したスクリプト言語であり、Visual Basicのサブセットです。

[VBS]CSVファイルのデータをデータベースにインサートする方法

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 95

---t---

score 30

表題の件で質問させてください
CSVファイルのデータをDBにインサートする方法について悩んでいます
使用するDBはpostgreSQLです
今回初めてVBSを扱うのでサンプル等を見ながらまずはDB接続まで書きましたが、CSVを扱ったりDBにインサートする部分をどう記述するのかがわかりません
質問するに当たりどのような記述が他に必要か分からないため、分かりにくい質問で申し訳ありませんがアドバイスよろしくお願いいたします

'定義されていない変数の使用制限
Option Expicit

'変数宣言
Dim objConn
Dim SQLTest
Dim objADO


'SQL文発行
SQLTest = "INSERT INTO datatest VALUES()"


'DB接続
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "DRIVER={PostgreSQL Unicode};" &_
             "SERVER=localhost;" &_
             "DATABASE=Test;" &_
             "UID=postgres;" &_
             "PWD=Test;" &_


'ADOを使いCSVファイルを扱う準備
Set objADO = CreateObject("ADODB.Connection")
objADO.Open "Driver={Microsoft Text Driver(*.txt; *.csv)};" &_
            "DBQ=C:\FTP_Test\LOG01\00006401;" &_
            "ReadOnly=1"


             

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

0

同じようなことをやっているので、手元にあるものをサンプルとして提供します。
接続情報等は適宜変更して下さい。

私が実際に行っているのは、DB側にストアドを準備して、そのストアドを呼び出す為に行っています。
VBS側では起動の制御とその進捗状況をIEに表示させています(※このサンプルでは進捗表示は割愛)

尚、CSVファイルのインポートはCOPYコマンドを使用するとINSERTなどを使用するより格段に高速です。

Option Explicit
'---------------------------------------------------------------
'定数
'---------------------------------------------------------------
'ODBC Connection
Private Const DB_SERVER = "SERVER=localhost;"                                   'DBサーバー名
Private Const DATABASE = "DATABASE=dadabase_name;"                              'DB名

Private Const DB_DRIVER = "DRIVER={PostgreSQL ODBC Driver(UNICODE)};"           'ODBCドライバー名
Private Const DB_PORT = "PORT=5432;"                                            'ポート
Private Const DB_UID = "UID=userid;"                                            'ユーザー名
Private Const DB_PWD = "PWD=password;"                                          'パスワード
'----------------------------------------------------------------
'環境設定部
'----------------------------------------------------------------
  'ADOオブジェクトを作成します
Dim objADO: Set objADO = CreateObject("ADODB.Connection")
Dim objRS
Dim Connection: Connection = DB_DRIVER & DB_SERVER & DB_PORT & DB_UID & DB_PWD & DATABASE

  objADO.ConnectionTimeout=0  'コネクションタイムアウト(無制限)
  objADO.CommandTimeout=0     'コマンドタイムアウト(無制限)

  'ADOを使いデータソースをオープンします
  objADO.Open Connection

'----------------------------------------------------------------
'実行部
'----------------------------------------------------------------
Dim wSQL
  '処理の実行
  wSQL = "何某かのSQL文"
  Set objRS = objADO.Execute(wSQL)
   '何某かの後処理
'----------------------------------------------------------------
  'データベースをクローズします
  objADO.Close

  'オブジェクトの破棄
  Set objRS = Nothing
  Set objADO = Nothing
Wscript.Quit

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/14 16:04

    ありがとうございます
    参考にさせていただきました
    ただ実際スクリプトを組んで実行しようとするとPermission deniedが発生し読み取り用にオープンできませんでしたと表示されます
    CSVを格納しているファイルのアクセス権限画面を確認し、postgresのNETWORK SERVICEやLOCAL SERVICEをユーザーに追加するとコマンドプロンプトからはSQLが実行できるようになったのですが、ファイルからは実行できません・・・
    何か考えられる部分はありますでしょうか?

    キャンセル

  • 2017/12/14 16:08

    どのようなSQLを実行されていますか?
    もし、COPYコマンドを実行されているなら、指定するパスはDBサーバーから見たパスを指定する必要があります。

    キャンセル

  • 2017/12/14 16:13

    返答ありがとうございます
    申し訳ありません・・・
    DBから見たパスとはどういうものでしょうか・・・
    COPYのパスに指定しているのはフォルダを格納している場所を指定しています
    以下コードです
    '未使用変数Ck----------------------------------------

    Option Explicit


    '変数宣言----------------------------------------

    Dim objADO
    Dim objRS
    Dim Sql

    'DB接続部---ADOオブジェクト作成-----------------------------------------

    Set objADO = CreateObject("ADODB.Connection")
    objADO.Open "DRIVER={PostgreSQL Unicode};" &_
    "SERVER=localhost;" &_
    "DATABASE=DataTest;" &_
    "UID=postgres;" &_
    "PWD=Test;"


    'objADO.ConnectionTimeout=0 'コネクションタイムアウト(無制限)
    'objADO.CommandTimeout=0 'コマンドタイムアウト(無制限)


    '実行部---------------------------------------

    Sql = "COPY datatest FROM 'C:\\FTP_Test\\LOG01\\00006401'; "
    Set objRS = objADO.Execute(Sql)

    キャンセル

  • 2017/12/14 17:16

    COPYコマンドの実行はpostgresが行いますので、postgresがインストールされているサーバから見たパスである必要があります。詳しくはリファレンスを確認して下さい。
    DBサーバーとは別なPC上にあるファイルをインポートするのであれば、plsql経由で行う必要があります。

    キャンセル

  • 2017/12/14 23:56 編集

    postgresがlocalhostに配置されているなら、pgadmin などのDBツールで指定しているCOPYコマンドそのものを試してうまくいくかどうか確認して下さい。
    >COPY datatest FROM 'C:\\FTP_Test\\LOG01\\00006401'
    withオプションの指定がありませんが確認していますか?
    それから、エスケープするなら
    E'C:\\FTP_Test\\LOG01\\00006401'
    のように記述しないと駄目です。
    'C:\FTP_Test\LOG01\00006401'
    上記のようにエスケープしなくても大丈夫だと思いますけど。

    キャンセル

  • 2017/12/15 09:19

    ありがとうございます
    私の確認ミスでパスの指定のミスとWITH CSが抜けておりました
    "COPY datatest FROM 'C:\FTP_530325\LOG01\00006401\000064A0.CSV'WITH CSV
    ただ、上記の最後にskipを付けたのでエラーとなりました
    COPYなしで試したのですが最後のSQL文を作成している付近で構文エラーとなります
    どちらの方法を用いるか考えているのですがCOPYと1行ずつ読み取るのはCOPYの方が高速なのでしょうか?

    'CSV1行ずつ読み取り---------
    '対象のファイルのパスを指定
    strReadFilePath = "C:\FTP_Test\LOG01\00006401\000064A0.CSV"

    'ファイルシステムを扱うオブジェクトを作成
    Set objFileSys = CreateObject("Scripting.FileSystemObject")

    'ファイルを読み取り専用で開き、TextStream オブジェクトを取得
    Set objReadStream = objFileSys.OpenTextFile(strReadFilePath, 1)

    Do Until objReadStream.AtEndOfLine = True
    '1 行読み込み
    strLine = objReadStream.ReadLine

    Loop

    values = Split(strLine ,",")

    objReadStream.Close

    Set objFileSys = Nothing

    'SQL----------
    Sql = "INSERT INTO (time,index,test) VALUES (values)"
    Set objRS = objADO.Execute(Sql)

    キャンセル

  • 2017/12/15 10:31

    >どちらの方法を用いるか考えているのですがCOPYと1行ずつ読み取るのはCOPYの方が高速なのでしょうか?
    断然COPYの方が高速です。CSVを読み込むのはさほど時間は掛からないと思いますが、仮に1回のINSERTにしたとしても時間が掛かります。
    COPYコマンドは元々インポートやエクスポートを高速に行うために作られたコマンドなのです。

    キャンセル

  • 2017/12/15 10:45

    >COPYなしで試したのですが最後のSQL文を作成している付近で構文エラーとなります
    VBSでの変数を文字列中に書いてもDBでは認識されないのでエラーです。
    "INSERT INTO (time,index,test) VALUES ('" & values & "'," & ・・・)"
    のように属性insertする項目に合わせた文字区切り記号(')なども必要なので、項目に分解する必要があります。
    ※そもそも、splitで分解するならfor each で処理する必要があるのでは?

    キャンセル

  • 2017/12/15 16:37

    無理やりな部分もありますが以下のコードで先頭3行を無視してインサートできるようになりました
    あとは”今日の日付のファイルを読み取る”という条件を付けれたらと考えます
    ただデータ数は1000行なのに遅く感じるのでCOPYやバルクインサートを使用しなければいけないと感じました

    '未使用変数Ck----------------------------------------

    Option Explicit


    '変数宣言----------------------------------------

    Dim objADO
    Dim objRS
    Dim Sql


    Dim objFileSys
    Dim strReadFilePath
    Dim objReadStream
    Dim strLine
    Dim values

    Dim RowsCk

    'DB接続部---ADOオブジェクト作成-----------------------------------------

    Set objADO = CreateObject("ADODB.Connection")
    objADO.Open "DRIVER={PostgreSQL Unicode};" &_
    "SERVER=localhost;" &_
    "DATABASE=DataTest;" &_
    "UID=postgres;" &_
    "PWD=Test;"

    '----------
    '対象のファイルのパスを指定
    strReadFilePath = "C:\FTP_Test\LOG01\00006401\000064A0.CSV"

    'ファイルシステムを扱うオブジェクトを作成
    Set objFileSys = CreateObject("Scripting.FileSystemObject")

    'ファイルを読み取り専用で開き、TextStream オブジェクトを取得
    Set objReadStream = objFileSys.OpenTextFile(strReadFilePath, 1)

    Do Until objReadStream.AtEndOfLine = True
    '1 行読み込み
    strLine = objReadStream.ReadLine

    values = Split(strLine ,",")

    RowsCk = RowsCk + 1

    If RowsCk > 3 Then

    Sql = "INSERT INTO datatest(time,index,test) VALUES (" & "'" & values(0) & "'" &"," & values(1) &","& values(2) &")"
    Set objRS = objADO.Execute(Sql)

    End If


    Loop


    objReadStream.Close

    Set objFileSys = Nothing


    'Close---------------------------------------

    objADO.Close

    'オブジェクトの破棄
    Set objRS = Nothing
    Set objADO = Nothing
    Wscript.Quit

    キャンセル

0

csvファイルから一括取り込みをされたいようでしたら、たいていのDBMSに(Postgresにも)用意されている「バルクインサート」機能を使うことをお勧めします。

https://lets.postgresql.jp/documents/technical/bulkload/1

https://www.postgresql.jp/document/9.6/html/sql-copy.html

CSVファイルはそのままファイルとして扱い(Postgres が読んでくれます)、Postgres のほうにADOで接続し、SQL文として COPY を Executeメソッドで発行します。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/14 16:06

    ありがとうございます
    COPYで実行しようと考えたのですが、CSVファイルのデータの先頭3行を無視したいしたいのですがCOPYでもそのようなことは可能でしょうか?

    キャンセル

  • 2017/12/14 17:10

    copyでは行のスキップはできません。
    その3行が識別できるなら、取り込んだ後に削除する方が高速です。
    若しくは、CSVそのものから3行除去した後で、COPYすれば良いのではないでしょうか。

    キャンセル

  • 2017/12/14 17:17

    私宛のコメントと勘違いしてコメントしてしまいました。
    失礼しました。

    キャンセル

  • 2017/12/14 17:43

    saziさんの仰るとおり、Postgresのインポートコマンドではスキップする機能は無いようです。
    COPYコマンドではSTDINやSTDOUTを入力や出力として指定できるようですので、csvファイルをスクリプトが読み込んで、このときに先頭3行を捨てて残りの行をWScript.StdInやWScript.StdOutを経由して与えることが出来るかもしれません。手元に環境がなく試せないためスミマセン...

    キャンセル

  • 2017/12/15 09:25

    お2人共ありがとうございます
    saziさん>>質問を混同させてしまいすいません

    COPYではCOPY datatest FROM 'C:\FTP_530325\LOG01\00006401\000064A0.CSV' WITH CSV のようにファイルの実態を指定しています。
    教えていただいた”CSVそのものから3行除去した後”というのはファイルのデータを加工してから再度ファイルを作り、そのファイルをCOPYのパスにするという事でしょうか?

    キャンセル

  • 2017/12/15 10:23

    そうです。
    属性が異なるというなら、一旦全て文字型のテーブルにcopyしてそこから再度COPY TOでCSVを作成する方法も考えられますが、処理時間を考えるとCSV自体を加工した方が良いと思われます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

ただいまの回答率

91.26%

関連した質問

同じタグがついた質問を見る

  • PostgreSQL

    749questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

  • CSV

    425questions

    CSV(Comma-Separated Values)はコンマで区切られた明白なテキスト値のリストです。もしくは、そのフォーマットでひとつ以上のリストを含むファイルを指します。

  • VBScript

    163questions

    VBScript(Visual Basic Scripting Edition)はMicrosftが開発したスクリプト言語であり、Visual Basicのサブセットです。