从csv使用ado和vbscript更新数据库没有记录集循环？ [英] Updating Database from csv using ado and vbscript without recordset loop?

问题描述

我想使用Vb Script& Plugin从csv文件将数据上传到Access数据库。 ADO所以我不必打开Access或Excel。

I want to upload data in to an Access database from csv files using Vb Script & ADO so I don't have to open either Access or Excel.

到目前为止我有一个工作脚本，但是有没有更好的方法来上传数据在一个批处理，而不必创建一个记录集和循环通过每个记录？

So Far I have a working script below, but is there a better way to upload the data in one batch without having to create a record set and loop through each record?

我创建了一个示例，其中有2个原始数据文件（csv）文件People.csv包含字段PERSON和COMPANY，另一个文件包含COMPANY和STATUS。要更新的数据库具有一个具有字段PERSON和STATUS的表'tblPeople'。

I've created an example where I have 2 raw data files (csv) the file People.csv contains the fields 'PERSON' and 'COMPANY' and the other file contains 'COMPANY' and 'STATUS'. The database to be updated has one table 'tblPeople' with the Fields 'PERSON' and 'STATUS'.

Const ST_FILES = "C:\test\Files\"
Const ST_DB = "C:\test\db\mydb.accdb"

'Connection to Raw Files
Dim stCS: stCS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ST_FILES & _
                ";Extended Properties=""text;HDR=YES;FMT=Delimited(,)"";"
Dim objCnFiles: set objCnFiles = CreateObject("ADODB.Connection"):objCnFiles.open stCS

'Connection to DB
stCS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ST_DB
Dim objCnDb: set objCnDb = CreateObject("ADODB.Connection"):objCnDb.Open stCS

'Recordset of Raw Files
Dim objRS: set objRS = CreateObject("ADODB.RecordSet")
Dim stSQL: stSQL = "SELECT T1.PERSON, T2.STATUS " & _
                    "FROM People#csv T1 " & _
                    "INNER JOIN Company#csv T2 "& _
                    "ON T1.COMPANY = T2.COMPANY"
objRS.open stSQL, objCnFiles

'The part Below is the part I want to improve:
'Loops through the recordset inserting record into db line by line.
objRS.MoveFirst
Do until objRS.EOF
    stSQL = "INSERT INTO tblPeople (PERSON, STATUS) " & _
            "VALUES('" & objRS.Fields("PERSON") & "','" & _
            objRS.Fields("STATUS") & "')"

    objCnDb.Execute stSQL
    objRS.MoveNext
Loop

objRS.close
objCnFiles.close
objCnDb.close

WScript.echo "Done"
WScript.Quit

编辑：解决方案下面使用由Ekkehard.Horner提供的解决方案

Solution Below using the solution provided by Ekkehard.Horner

Const ST_FILES = "C:\test\Files\"
Const ST_DB = "C:\test\db\mydb.accdb"

'Connection to Raw Files
Dim stCS: stCS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ST_FILES & _
                ";Extended Properties=""text;HDR=YES;FMT=Delimited(,)"";"
Dim objCnFiles: set objCnFiles = CreateObject("ADODB.Connection"):objCnFiles.open stCS

Dim stSQL: stSQL = "INSERT INTO [tblPeople] IN '" & ST_DB & "'" &_
                    "SELECT T1.PERSON, T2.STATUS " & _
                    "FROM People#csv T1 " & _
                    "INNER JOIN Company#csv T2 "& _
                    "ON T1.COMPANY = T2.COMPANY"

objCnFiles.Execute stSQL
objCnFiles.close

WScript.echo "Done"
WScript.Quit

推荐答案

使用INSERT / SELECT INTO IN外部数据库。对于您的特定任务（文本 - > mdb，
您的查询）语句将类似于

Use "INSERT/SELECT INTO IN 'External Database'". For your specific task (text -> mdb, your query) the statements would look like

Create new table:
SELECT T1.PERSON AS PERSON, T2.STATUS AS STATUS INTO [tblPeople] IN "P:\athto\your.mdb.mdb" FROM [People#csv] T1 INNER JOIN [Company#csv] T2 ON T1.COMPANY = T2.COMPANY

Append to existing table:
INSERT INTO [tblPeople] IN "P:\athto\your.mdb.mdb" SELECT T1.PERSON AS PERSON, T2.STATUS AS STATUS FROM [People#csv] T1 INNER JOIN [Company#csv] T2 ON T1.COMPANY = T2.COMPANY

这篇关于从csv使用ado和vbscript更新数据库没有记录集循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！