Send query results to delimited text file (*.txt)

Maybe use the MSGBUILDER node to build the record and FILEACCESS to write to a file?
For example if results from query are QRY1_EMPLOYEE, QR1_FICA_NBR, QR1_LAST_NAME
in the MSGBUILDER node,
<!--QRY1_EMPLOYEE-->|<!--QR1_FICA_NBR-->|<!--QR1_LAST_NAME-->
This will create a pipe delimited line, which you can accumulate and then write to a file

I am working on the same thing. Normally I would use a message builder (as suggested) But in this case I only need to create the file and FTP it to the vendor AND there is A LOT of data. Since I'm most comfortable using SQL to create views, I created a view that contains all of the fields I need, formatted the way they need to be in the file. Essentially I'm using a syscmd node in IPA to create the file on the landmark server and then an ftp node to send it. The syscmd opens a cmd window and uses bcp to create my file. Unfortunately I do not have a lot of information on bcp as my dba set it up and told me the basics.
I'm sure others do it differently, but this work for me, for now.
my syscmd node:
bcp "select distinct HeaderRec as mldata from ghs.viewname UNION ALL select ML_EligData as mldata from ghs.viewname" queryout -c -T -S -d LAWSON

You can create a SQL Query and set it to ‘Generate CSV from resultset’ and then use an Assign JavaScript to replace the comma to your specific special character.

Hello David! Thank you for your response. Could you please give me a sample on how to use an Assign JavaScript to replace the comma with the pipe.  I am trying to do something like this (~~Field1|field2|field3~~) in the File then execute it with SysCom but it is not spooling the output correctly. Can you please advise? Thank you.

Once you have your SQL results, you will have data that is line separated records and comma delimited fields.
In JavaScript, you would use the replace option to switch your commas to pipe var.replace(/,/g,"|")

I am getting there with the sql results and trying to replace the comma to the pipe '|'. I have done in an Assign JavaScript for a line but not for a file of 2000+ line of records. Can you please advise more on it? Thanks.

If you assign your SQL results to a variable, you can then treat it as an array (you don't have to declare it as an array)
s=SqlResults.split("\n")
for (i=0;i {
x=s
x=x.replace(/,/g,"|")
output=output+"~~"+x+"~~\n"
}

Email me and I'll send you the script - I can't figure out how to get it to display correctly.

david@consultdavidw.com