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!<\/p>\n
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.<\/p>\n
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''\n' Data Exporter '\n' '\n' Description: Allows the output of data to CSV file from a SQL '\n' statement to either Oracle, SQL Server, or MySQL '\n' Author: C. Peter Chen, http:\/\/dev-notes.com '\n' Version Tracker: '\n' 1.0 20080414 Original version '\n'\t1.1 20080807 Added email functionality '\n'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''\noption explicit\ndim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr\n\n'''''''''''''''''\n' Configuration '\n'''''''''''''''''\ndbType = \"oracle\" ' Valid values: \"oracle\", \"sqlserver\", \"mysql\"\ndbHost = \"dbhost\" ' Hostname of the database server\ndbName = \"dbname\" ' Name of the database\/SID\ndbUser = \"username\" ' Name of the user\ndbPass = \"password\" ' Password of the above-named user\noutputFile = \"c:\\output.csv\" ' Path and file name of the output CSV file\nemail = \"email@me.here\" ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string \"\"\nsubj = \"Email Subject\" ' The subject of your email; required only if you send the CSV over email\nbody = \"Put a message here!\" ' The body of your email; required only if you send the CSV over email\nsmtp = \"mail.server.com\" ' Name of your SMTP server; required only if you send the CSV over email\nsmtpPort = 25 ' SMTP port used by your server, usually 25; required only if you send the CSV over email\nsqlStr = \"select user from dual\" ' SQL statement you wish to execute\n'''''''''''''''''''''\n' End Configuration '\n'''''''''''''''''''''\n\n\n\ndim fso, conn\n\n'Create filesystem object \nset fso = CreateObject(\"Scripting.FileSystemObject\")\n\n'Database connection info\nset Conn = CreateObject(\"ADODB.connection\")\nConn.ConnectionTimeout = 30\nConn.CommandTimeout = 30\nif dbType = \"oracle\" then\n\tconn.open(\"Provider=MSDAORA.1;User ID=\" & dbUser & \";Password=\" & dbPass & \";Data Source=\" & dbName & \";Persist Security Info=False\")\nelseif dbType = \"sqlserver\" then\n\tconn.open(\"Driver={SQL Server};Server=\" & dbHost & \";Database=\" & dbName & \";Uid=\" & dbUser & \";Pwd=\" & dbPass & \";\")\nelseif dbType = \"mysql\" then\n\tconn.open(\"DRIVER={MySQL ODBC 3.51 Driver}; SERVER=\" & dbHost & \";PORT=3306;DATABASE=\" & dbName & \"; UID=\" & dbUser & \"; PASSWORD=\" & dbPass & \"; OPTION=3\")\nend if\n\n' Subprocedure to generate data. Two parameters:\n' 1. fPath=where to create the file\n' 2. sqlstr=the database query\nsub MakeDataFile(fPath, sqlstr)\n\tdim a, showList, intcount\n\tset a = fso.createtextfile(fPath)\n\t\n\tset showList = conn.execute(sqlstr)\n\tfor intcount = 0 to showList.fields.count -1\n\t\tif intcount <> showList.fields.count-1 then\n\t\t\ta.write \"\"\"\" & showList.fields(intcount).name & \"\"\",\"\n\t\telse\n\t\t\ta.write \"\"\"\" & showList.fields(intcount).name & \"\"\"\"\n\t\tend if\n\tnext\n\ta.writeline \"\"\n\t\n\tdo while not showList.eof\n\t\tfor intcount = 0 to showList.fields.count - 1\n\t\t\tif intcount <> showList.fields.count - 1 then\n\t\t\t\ta.write \"\"\"\" & showList.fields(intcount).value & \"\"\",\"\n\t\t\telse\n\t\t\t\ta.write \"\"\"\" & showList.fields(intcount).value & \"\"\"\"\n\t\t\tend if\n\t\tnext\n\t\ta.writeline \"\"\n\t\tshowList.movenext\n\tloop\n\tshowList.close\n\tset showList = nothing\n\n\tset a = nothing\nend sub\n\n' Call the subprocedure\ncall MakeDataFile(outputFile,sqlstr)\n\n' Close\nset fso = nothing\nconn.close\nset conn = nothing\n\nif email <> \"\" then\n\tdim objMessage\n\tSet objMessage = CreateObject(\"CDO.Message\")\n\tobjMessage.Subject = \"Test Email from vbs\"\n\tobjMessage.From = email\n\tobjMessage.To = email\n\tobjMessage.TextBody = \"Please see attached file.\"\n\tobjMessage.AddAttachment outputFile\n\t\n\tobjMessage.Configuration.Fields.Item (\"http:\/\/schemas.microsoft.com\/cdo\/configuration\/sendusing\") = 2\n\tobjMessage.Configuration.Fields.Item (\"http:\/\/schemas.microsoft.com\/cdo\/configuration\/smtpserver\") = smtp\n\tobjMessage.Configuration.Fields.Item (\"http:\/\/schemas.microsoft.com\/cdo\/configuration\/smtpserverport\") = smtpPort\n\t\nobjMessage.Configuration.Fields.Update\n\t\n\tobjMessage.Send\nend if\n\n'You're all done!! Enjoy the file created.\nmsgbox(\"Data Writer Done!\")\n<\/pre>\n","protected":false},"excerpt":{"rendered":"This VBScript allows you to output the result set of a SQL statement for Oracle, SQL Server, or MySQL database to a CSV file; all you have to do is to supply the database connection information and a SQL statement. Optionally, you may also configure the CSV file to be emailed via your SMTP server.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21,20,39,11],"tags":[],"class_list":["post-65","post","type-post","status-publish","format-standard","hentry","category-mysql","category-oracle","category-sql-server","category-vbscript"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/65","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=65"}],"version-history":[{"count":4,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/65\/revisions"}],"predecessor-version":[{"id":399,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/65\/revisions\/399"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=65"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=65"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=65"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}