vbscript:如何使用ado查询csv并返回csv

Question

我正在努力让这个脚本发挥作用（我有一些vba知识，但不知道vbs的差异。我试图适应的解决方案如下：用于将SQL Server表转储为CSV的VB脚本https://msdn.microsoft.com/en-us/library/ms974559.aspx

它应该读取csv文件，对其运行ACE SQL查询，并将结果返回到另一个csv

它只生成一个空的Output.csv文件，并且被锁定以进行编辑。你能帮我吗：

On Error Resume Next 
Const adOpenStatic = 3 
Const adLockOptimistic = 3 
Const adCmdText = &H0001 
Set cn = CreateObject("ADODB.Connection") 
Set rs = CreateObject("ADODB.Recordset") 
strPathtoTextFile = "C:Databases" 
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
          "Data Source=" & strPathtoTextFile & ";" & _ 
          "Extended Properties=""text;HDR=YES;FMT=Delimited""" 
rs.Open "SELECT * FROM PhoneList.csv", _ 
          objConnection, adOpenStatic, adLockOptimistic, adCmdText 

Set fso=CreateObject("Scripting.FileSystemObject")
Set ts=fso.OpenTextFile("c:Databasesoutput.csv",2,TRUE)
line=""
For Each tmp In objRecordset.Fields
   line=line & tmp.Name & ","
Next
ts.WriteLine Left(line,Len(line)-1)
While Not rs.EOF
 line=""
 For Each tmp In rs.Fields
  If IsNull(tmp.Value) Then
   line=line & """" & Replace(tmp.Value,"""","""""") & ""","
  Else
   line=line & """" & tmp.Value & ""","
  End If
 Next
 ts.WriteLine Left(line,Len(line)-1)
 rs.MoveNext
Wend
Set rs = Nothing
ts.close
rs.close
fso.close
cn.Close

我理解一些VBA，但在这个VBS中找不到出路。你能帮我找出我做错了什么吗？

第二。。。是否可以使所有数据路径都与脚本本身相关，以便分发解决方案？我知道如何在vba中做到这一点，但不知道用vbscript是否可行？

我相信这样的模板对所有社区都会很有帮助。如果我的问题表述不当，我会谦虚地接受批评。

Answer 1

我在这里添加代码的工作版本。删除"On Error Resume Next"并尝试修复突出显示的错误应该会有所帮助。数量不多，它们不难修复，但练习应该提供信息。这是代码的工作版本：

On Error Resume Next 
Const adOpenStatic = 3 
Const adLockOptimistic = 3 
Const adCmdText = &H0001 
Set cn = CreateObject("ADODB.Connection") 
Set rs = CreateObject("ADODB.Recordset") 
strPathtoTextFile = "C:Databases"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
          "Data Source=" & strPathtoTextFile & ";" & _ 
          "Extended Properties=""text;HDR=YES;FMT=Delimited""" 
rs.Open "SELECT * FROM PhoneList.csv", _ 
          cn, adOpenStatic, adLockOptimistic, adCmdText 

Set fso=CreateObject("Scripting.FileSystemObject")
Set ts=fso.OpenTextFile(strPathtoTextFile & "output.csv",2,TRUE)
line=""
For Each tmp In rs.Fields
   line=line & tmp.Name & ","
Next
ts.WriteLine Left(line,Len(line)-1)
While Not rs.EOF
 line=""
 For Each tmp In rs.Fields
  If IsNull(tmp.Value) Then
   line=line & """" & Replace(tmp.Value,"""","""""") & ""","
  Else
   line=line & """" & tmp.Value & ""","
  End If
 Next
 ts.WriteLine Left(line,Len(line)-1)
 rs.MoveNext
Wend
rs.close
Set rs = Nothing
ts.close
cn.Close