vbscript: how to query csv with ado and return csv

Question

I am trying to make this script work (having some vba knowledge and no idea about vbs differences. The solution I am trying to adapt is here: VB Script to dump an SQL Server table to CSV and https://msdn.microsoft.com/en-us/library/ms974559.aspx

It is supposed to read csv file, run ACE SQL query on it and return the result to another CSV

It generates only an empty Output.csv file and it is locked for editing. Can you help me out:

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:\Databases\output.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

I understand some VBA but am not able to find my way out in this VBS. Can you help me find out what I am doing wrong?

Secondly... is it possible to make all the paths to data relative to the script itself, so that the solution can be distributed? I know how to do it in vba but have no idea if it is possible with vbscript?

I am sure such a template can be very helpful to all the community. If my question is ill formulated I will humbly accept the critique.

1. objRecordset should probably be rs 2. `strPathtoTextFile` can be changed to change input path to relative 3. `Set ts=fso.OpenTextFile("c:\Databases\output.csv",2,TRUE)` can be changed to change output path to relative 4. Within the loop you could use `wscript.echo Left(line,Len(line)-1)` to determine that you're actually reading data from the input file — MikeC, Apr 06 '16 at 19:49
You are right. I corrected 1). Still there is some major issue... it produces an output file that is locked for editing and empty. — Jacek Kotowski, Apr 11 '16 at 07:38
Remove the "On Error Resume Next" and run again; you'' find a few changes that need to be made. "On Error Resume Next" has to be used judiciously; it can mask error you want to see. — MikeC, Apr 11 '16 at 14:55

score 0 · Answer 1 · answered Apr 11 '16 at 15:03

I'm adding the working version of the code here. It should be helpful for you to remove the "On Error Resume Next" and try to fix the errors that are highlighted. There are not many, they are not difficult to fix but the exercise should be informative. Here's the working version of the code:

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

Dear Mike. Thanks for help. I pasted the code into a sub in vba editor and it stops on the line 22 with a Run-time error '70' Permission denied. Run as vbs code it stops on line 12 claiming that "Provider cannot be found. It may not be properly installed. Code 800A0E7A Source: ADODB.Connection — Jacek Kotowski, Apr 12 '16 at 11:58
"Permission denied" likely because you're writing to a location you shouldn't be. You probably need to change the output location. As for "Provider cannot be found", that's unusual because required files have been part of Windows default installation for a long time now. The following SO discussion has several suggestions: http://stackoverflow.com/questions/2508037/vbscript-access-mdb-800a0e7a-provider-cannot-be-found-it-may-not-be-prop — MikeC, Apr 12 '16 at 13:50

vbscript: how to query csv with ado and return csv

1 Answers1