April 14, 2008June 10, 2020 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”

Karl Porschert says:

June 3, 2020 at 5:23 am

Hi,

Your code helped me a lot.

I would need the exported file in UTF-8.

Can you help me there

Thanks a lot
greeting
Porschert Karl

Reply
1. C. Peter Chen says:
  
  June 3, 2020 at 10:42 am
  
  Karl, check out ADODB.Stream and see if that helps you. It seems like it has a Charset property which accepts “utf-8” as its value.
  
  Reply
Thanushree says:

June 10, 2020 at 10:04 am

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. C. Peter Chen says:
  
  June 10, 2020 at 10:11 am
  
  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. Thanushree says:
    
    June 10, 2020 at 11:06 pm
    
    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. C. Peter Chen says:
      
      June 11, 2020 at 7:33 am
      
      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. Thanushree says:
        
        June 11, 2020 at 9:17 pm
        
        Thank you
Ashok says:

September 22, 2021 at 10:18 am

Hi,
I have tried this…but it seems its not working for me. Can you please help me out here.

Reply
1. C. Peter Chen says:
  
  September 22, 2021 at 10:31 am
  
  Thanks for visiting Dev-Notes Ashok. Can you tell me more about what is not working? Any specific error messsages?
  
  Reply
  1. Ashok says:
    
    September 23, 2021 at 2:57 am
    
    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. C. Peter Chen says:
      
      September 23, 2021 at 8:55 am
      
      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. Ashok says:
        
        September 23, 2021 at 10:29 am
        
        Hi, My SQL query is running in database…When i execute the vb script it just ends up…shows nothing…
      2. C. Peter Chen says:
        
        September 23, 2021 at 10:38 am
        
        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.
Ashok Kumar says:

September 23, 2021 at 10:58 am

I am running manually by clicking on the vbscript..if you have time…can we have a call….

Reply
1. C. Peter Chen says:
  
  September 23, 2021 at 11:02 am
  
  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
Ashok Kumar says:

September 23, 2021 at 11:22 am

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. C. Peter Chen says:
  
  September 23, 2021 at 1:25 pm
  
  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
Ashok Kumar says:

September 23, 2021 at 11:14 pm

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
1. C. Peter Chen says:
  
  September 24, 2021 at 8:31 am
  
  That will depend on the version installed on your machine. You can try this website for reference: https://www.connectionstrings.com/
  
  Reply
Ashok Kumar says:

September 30, 2021 at 5:43 am

How can we run 2 SQL query here….like
select * from tabl1;
select * from table2;

Reply
1. C. Peter Chen says:
  
  September 30, 2021 at 8:47 am
  
  If the two SQLs will return the same columns, you can look up something called “union” in Oracle.
  
  Reply
Ashok Kumar says:

September 30, 2021 at 9:01 am

I am fetching the count from the 2 tables

Reply
Parley says:

July 15, 2022 at 8:04 am

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. C. Peter Chen says:
  
  July 18, 2022 at 8:58 am
  
  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

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

Leave a Reply to C. Peter Chen Cancel reply