Home » Infrastructure » Unix » Create a Pipe Delimited Extract File
Create a Pipe Delimited Extract File [message #98551] Wed, 29 December 2004 09:08 Go to next message
Anindya Banerjee
Messages: 5
Registered: December 2004
Junior Member
Hi,

I need to call a SQL script that goes against couple of Oracle tables and create an extract file. By default this file is created as a fixed-width file.

Is there a way I can create this file in pipe-delimited format?

Thanks in Advance,
- Anindya
Re: Create a Pipe Delimited Extract File [message #98554 is a reply to message #98551] Thu, 30 December 2004 01:09 Go to previous messageGo to next message
velu
Messages: 10
Registered: February 2002
Junior Member
hi,
change the SQL script like the following and spool it into the output file.

select field1||'|'||field2||'|'field3
from table_name
where condition

I think it will help.

Thanks
Velu.
Re: Create a Pipe Delimited Extract File [message #98557 is a reply to message #98554] Thu, 30 December 2004 09:31 Go to previous messageGo to next message
Anindya Banerjee
Messages: 5
Registered: December 2004
Junior Member
Thanks Velu.

Yes... this works, only extra thing I would do is to add a LTRIM(RTRIM(Field)) in the SQL script.

Also this is possible using a 'awk' command.
Re: Create a Pipe Delimited Extract File [message #98563 is a reply to message #98557] Tue, 04 January 2005 17:00 Go to previous messageGo to next message
velu
Messages: 10
Registered: February 2002
Junior Member
yes, u can use awk ...

awk '{print $1"|"$2"|"$3}' filename

if any spl delimited (, comma) file then

awk -F, '{print $1"|"$2"|"$3}' filename
Re: Create a Pipe Delimited Extract File [message #98564 is a reply to message #98557] Tue, 04 January 2005 17:15 Go to previous messageGo to next message
velu
Messages: 10
Registered: February 2002
Junior Member
yes u can use awk if the output spooled into a file.

awk '{print $1"|"$2"|"$3}' filename > outfilename

if the file is delimited (, comma) then

awk -F, '{print $1"|"$2"|"$3}' filename > outfilename
Re: Create a Pipe Delimited Extract File [message #98568 is a reply to message #98563] Wed, 05 January 2005 11:53 Go to previous message
Anindya Banerjee
Messages: 5
Registered: December 2004
Junior Member
"....awk '{print $1"|"$2"|"$3}' filename...."
This does not work if you have spaces in the extracted fields. Suppose the field is name and constituted by 'firstname lastname'. This command of awk will assume them to be separate fields.

In such scenario this piece of code works (got this snippet from another website):
awk 'BEGIN{j=split("[[provide the starting columns for the fields e.g. 1 11 39]]",s)}
{for(i=1;i<j;++i){
a=substr($0,s[[i]],s[[i+1]]-s[[i]]);sub(/ *$/,"",a)
printf "%s"(i<j-1 ? "|" : "n"),a
}}' filename;
Previous Topic: unix installation
Next Topic: Oracle 9 installer freeze at Welcome screen
Goto Forum:
  


Current Time: Fri Mar 29 10:55:01 CDT 2024