Posted on by C. Peter Chen

Writing SQL output to CSV with VBScript

Enter your specific information in the “Configuration” section near the top of the script. For the “dbType” variable, the only accepted values are “oracle”, “sqlserver”, or “mysql”. Once this is done, just run the script and you should have your quote-delimited comma-separated CSV file!

The email-related variables are optional. To enable the emailing functionality (send the generated CSV file to the address as an attachment), enter the recipient email address in the box. If you do not wish to email, just leave that variable as empty string (“”), and the other email related variable such as smtp and smtpPort will be ignored.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Data Exporter                                                 '
'                                                               '
' Description: Allows the output of data to CSV file from a SQL '
'       statement to either Oracle, SQL Server, or MySQL        '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20080414 Original version                         '
'	1.1   20080807 Added email functionality                '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                 ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "dbhost"                 ' Hostname of the database server
dbName = "dbname"                 ' Name of the database/SID
dbUser = "username"               ' Name of the user
dbPass = "password"               ' Password of the above-named user
outputFile = "c:\output.csv"      ' Path and file name of the output CSV file
email = "email@me.here"           ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
subj = "Email Subject"            ' The subject of your email; required only if you send the CSV over email
body = "Put a message here!"      ' The body of your email; required only if you send the CSV over email
smtp = "mail.server.com"          ' Name of your SMTP server; required only if you send the CSV over email
smtpPort = 25                     ' SMTP port used by your server, usually 25; required only if you send the CSV over email
sqlStr = "select user from dual"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''



dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
	dim a, showList, intcount
	set a = fso.createtextfile(fPath)
	
	set showList = conn.execute(sqlstr)
	for intcount = 0 to showList.fields.count -1
		if intcount <> showList.fields.count-1 then
			a.write """" & showList.fields(intcount).name & ""","
		else
			a.write """" & showList.fields(intcount).name & """"
		end if
	next
	a.writeline ""
	
	do while not showList.eof
		for intcount = 0 to showList.fields.count - 1
			if intcount <> showList.fields.count - 1 then
				a.write """" & showList.fields(intcount).value & ""","
			else
				a.write """" & showList.fields(intcount).value & """"
			end if
		next
		a.writeline ""
		showList.movenext
	loop
	showList.close
	set showList = nothing

	set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
	dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = "Test Email from vbs"
	objMessage.From = email
	objMessage.To = email
	objMessage.TextBody = "Please see attached file."
	objMessage.AddAttachment outputFile
	
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

'You're all done!!  Enjoy the file created.
msgbox("Data Writer Done!")

24 Replies to “Writing SQL output to CSV with VBScript”

  2. Hi Peter,

    My requirement is i want a vbscript which will connect to SQL server and run a query in DB and if any output then send mail if no output then no need of mail just end the script. Can you please help me i am new to VBS so i am finding it difficult

    Reply

    1. Thanks for visiting Dev-Notes, Thanushree. What if you change the SUB in this example to a FUNCTION, and have it return the number of records written? This way you can do a simple IF statement, only sending email if the returned value is greater than zero.

      Reply

      1. Hi Peter,

        This is my first VB script and i am not getting how to do. can you please given an example code or part of code so that i can refer ?

        It would be a great help!!!

        Reply

        1. You can perhaps simplify it by eliminating the SUB and move all its contents outside, thus making it procedural. Hopefully that reduces the complexity of the VBScript by a degree. Once the logic is outside the SUB, you can just make a number variable, initiated at 0, that increments by 1 every time you go through the FOR loop. Finally, set up an IF statement around the lines that handles emailing so that those lines only run if the variable is greater than 0.

          Reply

      1. I have created the batch file to run this vb script but it seems not working,…I have done the changes in the configuration part only.
        Output is not getting geneated in the csv file. I have provided all the details…Do i need to do any other settings or config change…

        Reply

        1. Ashok, can you tell me more about “but it seems not working”? Are you getting any error messages indicating database connection is not being made successfully (username/password issue, security issue, server name typographical error, etc.)? Does the SQL statement work when you run it in your database development tool (SQL*Worksheet, PhpMyAdmin etc.)?

          Reply

          1. Hi, My SQL query is running in database…When i execute the vb script it just ends up…shows nothing…

          2. Are you running it manually (ie. double-clicking on the VBScript file) or as a scheduled job via Task Scheduler? Try to make sure it runs ok manually first; if there are errors, you will want to see what the error pop ups are. Pop ups may be hidden for scheduled tasks.

    1. Hm then it may be something environmental on your machine. Sorry I won’t be much help there. One last suggestion I have for you is that you can try to add msgbox at various points of the code, displaying relevant info at each step, and see where things may be failing for you. Is the database connection being made? How many records being returned? File being opened ok? Etc. I hope that will help you.

      Thanks again for visiting Dev-Notes!

      Reply

  5. Giving this error..

    —————————
    Windows Script Host
    —————————
    Script: C:\Users\525657\OneDrive – Cognizant\Desktop\VB Script.vbs
    Line: 27
    Char: 2
    Error: Provider cannot be found. It may not be properly installed.
    Code: 800A0E7A
    Source: ADODB.Connection

    —————————
    OK
    —————————

    Reply

    1. Please find this section in the code and modify as appropriate:

      DRIVER={MySQL ODBC 3.51 Driver}

      When I wrote this article back in 2008, and version 3.51 is quite old now. You may wish to update it to match the version number of the ODBC provider installed on your machine.

      Hope this helps.

      Reply

  6. I am using here dbtype as “Oracle”. Since its a oracle database. Do you have any idea what is the provider name of oracle.

    Reply

  9. I’m getting this error with a long SQL script:
    Unterminated String constant.

    my sql code is about 30 lines long. Is there a way to use one that spans multiple lines?

    Reply

    1. You will either have to reformat your SQL statement to a single line, or you can enclose each line in double-quotes and add the “&_” symbol to indicate a multi-line string. Example below:

      Dim sql
      sql = “select a.field1, b.field2, c.field3 ” & _
      “from table_a a, table_b b, table_c c ” & _
      “where a.id=b.id ” & _
      “and b.id=c.id ” & _
      “and a.status=’Y’ ” & _
      “and b.field2 is not null “

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Proudly powered by WordPress