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

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

新規登録して質問してみよう
ただいま回答率
85.50%
CSV

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

PostgreSQL

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

VBScript

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

Q&A

解決済

2回答

11543閲覧

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

---stax---

総合スコア148

CSV

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

PostgreSQL

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

VBScript

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

0グッド

0クリップ

投稿2017/12/14 02:27

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

VBScript

1'定義されていない変数の使用制限 2Option Expicit 3 4'変数宣言 5Dim objConn 6Dim SQLTest 7Dim objADO 8 9 10'SQL文発行 11SQLTest = "INSERT INTO datatest VALUES()" 12 13 14'DB接続 15Set objConn = CreateObject("ADODB.Connection") 16objConn.Open "DRIVER={PostgreSQL Unicode};" &_ 17 "SERVER=localhost;" &_ 18 "DATABASE=Test;" &_ 19 "UID=postgres;" &_ 20 "PWD=Test;" &_ 21 22 23'ADOを使いCSVファイルを扱う準備 24Set objADO = CreateObject("ADODB.Connection") 25objADO.Open "Driver={Microsoft Text Driver(*.txt; *.csv)};" &_ 26 "DBQ=C:\FTP_Test\LOG01\00006401;" &_ 27 "ReadOnly=1"

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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

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

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

VBS

1Option Explicit 2'--------------------------------------------------------------- 3'定数 4'--------------------------------------------------------------- 5'ODBC Connection 6Private Const DB_SERVER = "SERVER=localhost;" 'DBサーバー名 7Private Const DATABASE = "DATABASE=dadabase_name;" 'DB名 8 9Private Const DB_DRIVER = "DRIVER={PostgreSQL ODBC Driver(UNICODE)};" 'ODBCドライバー名 10Private Const DB_PORT = "PORT=5432;" 'ポート 11Private Const DB_UID = "UID=userid;" 'ユーザー名 12Private Const DB_PWD = "PWD=password;" 'パスワード 13'---------------------------------------------------------------- 14'環境設定部 15'---------------------------------------------------------------- 16 'ADOオブジェクトを作成します 17Dim objADO: Set objADO = CreateObject("ADODB.Connection") 18Dim objRS 19Dim Connection: Connection = DB_DRIVER & DB_SERVER & DB_PORT & DB_UID & DB_PWD & DATABASE 20 21 objADO.ConnectionTimeout=0 'コネクションタイムアウト(無制限) 22 objADO.CommandTimeout=0 'コマンドタイムアウト(無制限) 23 24 'ADOを使いデータソースをオープンします 25 objADO.Open Connection 26 27'---------------------------------------------------------------- 28'実行部 29'---------------------------------------------------------------- 30Dim wSQL 31 '処理の実行 32 wSQL = "何某かのSQL文" 33 Set objRS = objADO.Execute(wSQL) 34 '何某かの後処理 35'---------------------------------------------------------------- 36 'データベースをクローズします 37 objADO.Close 38 39 'オブジェクトの破棄 40 Set objRS = Nothing 41 Set objADO = Nothing 42Wscript.Quit

投稿2017/12/14 04:59

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

---stax---

2017/12/14 07:04

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

2017/12/14 07:08

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

2017/12/14 07: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)
sazi

2017/12/14 08:16

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

2017/12/14 14: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' 上記のようにエスケープしなくても大丈夫だと思いますけど。
---stax---

2017/12/15 00: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)
sazi

2017/12/15 01:31

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

2017/12/15 01:45

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

2017/12/15 07: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
guest

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 04:30

編集2017/12/14 04:32
hsk

総合スコア728

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

---stax---

2017/12/14 07:06

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

2017/12/14 08:10

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

2017/12/14 08:17

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

2017/12/14 08:43

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

2017/12/15 00:25

お2人共ありがとうございます saziさん>>質問を混同させてしまいすいません COPYではCOPY datatest FROM 'C:\FTP_530325\LOG01\00006401\000064A0.CSV' WITH CSV のようにファイルの実態を指定しています。 教えていただいた”CSVそのものから3行除去した後”というのはファイルのデータを加工してから再度ファイルを作り、そのファイルをCOPYのパスにするという事でしょうか?
sazi

2017/12/15 01:23

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問