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!")