VB Script export from database into file use Pipe column seperator

[fcomino]

Hi

I am using the following script from:

http://dev-notes.com/code.php?q=18

The problem I am having is my database contains fields with quotes and commas, and when the file is exported it is getting confused. I was wondering if there is any way I can modify this script to instead of exporting using a comma column delimiter, use a PIPE column delimiter ?

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 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, [URL unfurl="true"]http://dev-notes.com[/URL]                   '
' 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 ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
	objMessage.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = smtp
	objMessage.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

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

Thanks

 

[fcomino]

All good, worked it out.

Change

a.write """" & showList.fields(intcount).name & ""","

to

a.write """" & showList.fields(intcount).name & """|"