Unload delimiter

Hi, Everytime I unload data to a file, it also puts a delimiter at the end of the line. For example, I would like a row with three fields to look like this: a|b|c instead of a|b|c| Is there a way to do this inside the unload utility, without formatting the data with the operating system's command line? Thanks, Denis

No. Get Jonathan Leffer's sqlcmd package it has various unload file format included CSV and generic delimited files which do not append the delimiter (IDS LOAD and dbload expect the trailing delimiter) as well as XML formats. You can download sqlcmd from the IIUG Software Repository. Art On Wed, Jan 14, 2009 at 1:31 PM, DENIS DIDEROT <nyakizu2000@yahoo.com>wrote: > Hi, > Everytime I unload data to a file, it also puts a delimiter at the end of > the > line. For example, I would like a row with three fields to look like this: > a|b|c instead of a|b|c| > Is there a way to do this inside the unload utility, without formatting the > data with the operating system's command line? > Thanks, > Denis > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Hi Denis, Try following : unload to 'test.unl' delimiter " " select col1,"|", col2, "|", col3 fromtable_name Regards, Dharmendra > To: ids@iiug.org> From: nyakizu2000@yahoo.com> Subject: Unload delimiter [14535]> Date: Wed, 14 Jan 2009 13:31:07 -0500> > Hi, > Everytime I unload data to a file, it also puts a delimiter at the end of the > line. For example, I would like a row with three fields to look like this: > a|b|c instead of a|b|c| > Is there a way to do this inside the unload utility, without formatting the > data with the operating system's command line? > Thanks, > Denis > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Windows Live™ Hotmail®: Chat. Store. Share. Do more with mail. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_howitwor ks_012009

Hi all, I try: unload to /tmp/ln10 delimiter "" select person, "|", rpt_type, "|",rpt_link from wpe where person = 121643 or ( propositus = 26970 and rpt_type = "F"); Result in /tmp/ln10: 200539"|"F"|"152238" 200540"|"F"|"152238" Still ugly!. So try this script: : dbaccess <<+ database ttlabs; unload to A select person, rpt_type,rpt_link from wpe where person = 121643 or ( propositus = 26970 and rpt_type = "F"); close database; + sed -e 's/|$//g' A > B; #remove all "|" at end of each line. ====================== run this cript and see result in B: 200539|F|152238 200540|F|152238 Cheers Long N. ________________________________ From: dharmendra sharma <dharmendrasharma@hotmail.com> To: ids@iiug.org Sent: Thursday, 15 January, 2009 3:34:51 PM Subject: RE: Unload delimiter [14540] Hi Denis, Try following : unload to 'test.unl' delimiter " " select col1,"|", col2, "|", col3 fromtable_name Regards, Dharmendra > To: ids@iiug.org> From: nyakizu2000@yahoo.com> Subject: Unload delimiter [14535]> Date: Wed, 14 Jan 2009 13:31:07 -0500> > Hi, > Everytime I unload data to a file, it also puts a delimiter at the end of the > line. For example, I would like a row with three fields to look like this: > a|b|c instead of a|b|c| > Is there a way to do this inside the unload utility, without formatting the > data with the operating system's command line? > Thanks, > Denis > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Windows Live Hotmail®: Chat. Store. Share. Do more with mail. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_howitwor ks_012009 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. Stay connected to the people that matter most with a smarter inbox. Take a look http://au.docs.yahoo.com/mail/smarterinbox

Hi Long, You do not need to do additional step to remove the last pipe sign from the unload file. If you copy and paste the unload statement I have provided, it must work. I think the reason you are getting "200539"|"F"|"152238" result in your /tmp/In10 is, you are not giving space between " " after delimiter... Try cut and paste following unload (I have used your statement...and added space between " " after delimiter....) unload to /tmp/ln10 delimiter " " select person, "|", rpt_type, "|",rpt_link from wpe where person = 121643 or ( propositus = 26970 and rpt_type = "F");Regards, Dharmendra > To: ids@iiug.org> From: longhuynguyen51@yahoo.com.au> Subject: Re: Unload delimiter [14555]> Date: Thu, 15 Jan 2009 23:01:27 -0500> > Hi all, > I try: > unload to /tmp/ln10 delimiter "" > select person, "|", rpt_type, "|",rpt_link > from wpe where person = 121643 or > > ( propositus = 26970 and > > rpt_type= "F"); > Result in /tmp/ln10: > 200539"|"F"|"152238" > 200540"|"F"|"152238" > Still ugly!. > So try this script: > : > dbaccess <<+ > database ttlabs; > unload to A > select person, rpt_type,rpt_link > from wpe where person =121643 or > > ( propositus = 26970 and > > rpt_type = "F"); > close database; > + > sed -e 's/|$//g' A > B; #remove all "|" at end of each line. > ====================== > run this cript and see result in B: > 200539|F|152238 > 200540|F|152238 > > Cheers > Long N. > > ________________________________ > From: dharmendra sharma <dharmendrasharma@hotmail.com> > To: ids@iiug.org > Sent: Thursday, 15 January, 2009 3:34:51 PM > Subject: RE: Unload delimiter [14540] > > Hi Denis, > > Try following : > > unload to 'test.unl' delimiter " " select col1,"|", col2, "|", col3 from > table_name > > Regards, > > Dharmendra > > To: ids@iiug.org> From: nyakizu2000@yahoo.com> Subject: Unload delimiter > [14535]> Date: Wed, 14 Jan 2009 13:31:07 -0500> > Hi, > Everytime I unload > data to a file, it also puts a delimiter at the end of the > line. For > example, I would like a row with three fields to look like this: > a|b|c > instead of a|b|c| > Is there a way to do this inside the unload utility, > without formatting the > data with the operating system's command line? > > Thanks, > Denis > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > _________________________________________________________________ > Windows Live Hotmail®: Chat. Store. Share. Do more with mail. > > > http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_howitwor ks_012009 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Stay connected to the people that matter most with a smarter inbox. Take a > look http://au.docs.yahoo.com/mail/smarterinbox > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Windows Live™ Hotmail®: Chat. Store. Share. Do more with mail. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_explore_012 009