0

I have a VBScript file that extracts data from a SQL Server database table and then write the result in a CSV file. My problem is that each record gets written to the file one line at a time. I would like the recordset to be pasted into the CSV file in a similar way when using CopyFromRecordset in Excel VBA. Is there a way to do this? Here is my current code that reads the recordset one line at a time,

Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set outFile = CreateObject("Scripting.FileSystemObject").CreateTextFile("C:\Users\JASON SAMUELS\Desktop\VBS\test.csv")

con.Open "Provider=SQLOLEDB.1;Password=pwd123;Persist Security Info=True;User ID=TEST;Initial Catalog=DemoDB;Data Source=JASONPC\SQLEXPRESS"

rs.Open "SELECT * FROM MyTable", con, 1,3

rs.MoveFirst

Do
  'WScript.Echo rs("Name") & " " & rs("Surname")
  outFile.WriteLine rs("Name") & "," & rs("Surname")
  rs.MoveNext

Loop Until rs.EOF

rs.close
con.Close
Set con = Nothing
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Jason Samuels
  • 951
  • 6
  • 22
  • 40

2 Answers2

0

I doubt there is any built in function that let you export all the recordset in a csv file in one go. But you have IMHO some options : - Batch the write output by creating a buffer string holding the csv data :

 'vbcrlf is a carriage return+line feed : Chr(10) & Chr(11)
Do

 strOutputString = strOutputString & rs("Name") & "," & rs("Surname") & vbcrlf 
rs.MoveNext

Loop Until rs.EOF
 outFile.WriteLine strOutputString
  • You could also write to the file every 1000 lines for examples (just by using a counter in the loop)
  • also do not forget to close your file handle when you have finish to write into it.

Hope that helps.

Dypso
  • 563
  • 1
  • 5
  • 15
0

This can be done much easier via PowerShell. Assuming you have SSMS installed on the system you're running the script on:

invoke-sqlcmd -serverinstance 'JASONPC\SQLEXPRESS' -username TEST -password pwd123 -query "select * from mytable" | export-csv -notypeinfo -path "C:\Users\JASON SAMUELS\Desktop\VBS\test.csv" -encoding ASCII -noclobber

But there are still faster options like bcp

Community
  • 1
  • 1
alroc
  • 27,574
  • 6
  • 51
  • 97