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!")
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
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.
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
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.
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!!!
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.
Thank you