Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Keeping Null's as it is and separating them by Comma

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Keeping Null's as it is and separating them by Comma
# 1  
Old 05-17-2011
Keeping Null's as it is and separating them by Comma
Hi,

I am having source (Oracle) as given below.
Code:
SourceOBJECT_NAMESUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPECREATEDLAST_DDL_TIMESTAMPSTATUSTGSTEST 336559336559TABLE4/15/2009 10:374/15/2009 10:372009-04-15:10:37:57VALIDNNNUNIX 336559336559TABLE4/15/2009 10:374/15/2009 10:372009-04-15:10:37:57VALIDNNNWINDOWS 336559336559TABLE4/15/2009 10:374/15/2009 10:372009-04-15:10:37:57VALIDNNNINFORM 336559336559TABLE4/15/2009 10:374/15/2009 10:372009-04-15:10:37:57VALIDNNN

While i am using the below functions, as the 2nd column SUBOBJECT_NAME is having NULL values, the 3rd column values are moving into 2nd column and so on.

Commands:

Code:
perl -pne 's/\s+([^\s+])/,\1/g' out.txt
awk '$1=$1' OFS=\, file
awk -F, 'NF==6{$0=gensub ("([0-9]+, [[:alpha:]]+),","\\1",1);$0=gensub (", ([[:alpha:]]+, [0-9]+$)"," \\1",1)}1' file

Target data:
Code:
OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_,TIMESTAMP,STATUS,T,G,S
TEST,336559,336559,TABLE,15-Apr-09,15-Apr-09,2009-04-15:10:37:57,VALID,N,N,N,
UNIX,336559,336559,TABLE,15-Apr-09,15-Apr-09,2009-04-15:10:37:57,VALID,N,N,N,
EBILITE,336559,336559,TABLE,15-Apr-09,15-Apr-09,2009-04-15:10:37:57,VALID,N,N,N,
EBILITE,MIGRATION,336559,336559,TABLE,15-Apr-09,15-Apr-09,2009-04-15:10:37:57,VALID,N,N,N

My Requirement: I want the null values to be present in the file and should be separated by comma.

Thanks in advance for your replies.
# 2  
Old 05-17-2011
You should look for a way to directly output in the desired format from Oracle.
There are lot of formatting option available in sql and other sql related languages.

You can for example have look at http://asktom.oracle.com/pls/apex/f?...ID:88212348059
This User Gave Thanks to ctsgnb For This Post:
arunvasu2
# 3  
Old 05-17-2011
Thanks for your reply. But the thing is after fetching the data from Oracle into UNIX into a flatfile and applying the above mentioned commands are giving wrong results.
# 4  
Old 05-17-2011
The link i referred to, demonstrate that you need to setup a script at oracle level so that when you call it, it generate the *.csv file into the expected format.

the specified command need to be entered into and *.sql file, NOT a *.sh nor a *.bash nor <whatever>*.shell

So the commands should be applied and run at Oracle (sql) level, not OS level ...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to parse comma separated field and removing comma in between number and double quotes

Hi Experts, Please support I have below data in file in comma seperated, but 4th column is containing comma in between numbers, bcz of which when i tried to parse the file the column 6th value(5049641141) is being removed from the file and value(222.82) in column 5 becoming value of column6. ... (3 Replies)
Discussion started by: as7951
3 Replies

2. Shell Programming and Scripting

Replace spaces with underscores up to first comma but not after the comma

I have a comma delimited file of major codes and descriptions. I want to replace all occurrences of spaces with underscores up to the first comma (only in the first field), but not replace spaces following the comma. For instance I have the following snippet of the file: EK ED,Elementary and... (7 Replies)
Discussion started by: tdouty
7 Replies

3. Shell Programming and Scripting

Replace comma and blank with comma and number

I, I have a file and i need to replace comma and blank space with comma and 0. cat file.txt a,5 b,1 c, d, e,4 I need the output as cat file.txt a,5 b,1 c,0 d,0 (4 Replies)
Discussion started by: jaituteja
4 Replies

4. Shell Programming and Scripting

Need Help - comma inside double quote in comma separated csv,

Hello there, I have a comma separated csv , and all the text field is wrapped by double quote. Issue is some text field contain comma as well inside double quote. so it is difficult to process. Input in the csv file is , 1,234,"abc,12,gh","GH234TY",34 I need output like below,... (8 Replies)
Discussion started by: Uttam Maji
8 Replies

5. Shell Programming and Scripting

How to grep after the first comma till the next comma in a line

Hi Can any one pls tell me how to grep this line POPULATION,69691,20120509 I want the number 69691 from the above line. How to grep from the first comma till the next comma. Thank You.:confused: (8 Replies)
Discussion started by: rxg
8 Replies

6. Shell Programming and Scripting

Separating list of input files (*.file) with a comma in bash script

Hi all, I'm trying to get a bash script working for a program (bowtie) which takes a list of input files (*.fastq) and assembles them to an output file (outfile.sam). All the .fastq files are in one folder in my home directory (~/infiles). The problem is that the 'bowtie' requires that... (7 Replies)
Discussion started by: TuAd
7 Replies

7. Shell Programming and Scripting

Insert string 'NULL' where there is a null value

I have an input file having 7 fields delimited by , eg : 1,ABC,hg,1,2,34,3 2,hj,YU,2,3,4, 3,JU,kl,4,5,7, 4,JK,KJ,3,56,4,5 The seventh field here in some lines is empty, whereas the other lines there is a value. How do I insert string NULL at this location (7th loc) for these lines where... (8 Replies)
Discussion started by: zilch
8 Replies

8. Shell Programming and Scripting

Find and replace a column that has '' to NULL in a comma delimited using awk or sed

Hi this is my first time posting ever. I'm relatively new in using AWK/SED, I've been trying many a solution. I'm trying to replace the 59th column in a file where if I encounter '' then I would like to replace it with the word NULL. example 0 , '' , '' , 0 , 195.538462 change it to 0... (5 Replies)
Discussion started by: gumal901
5 Replies

9. Shell Programming and Scripting

Pull Data After Comma if 2 word before comma

Hi, I am trying to truncate word after comma in a file ONLY if there are already 2 words BEFORE comma. If there is one word or 3 or more words BEFORE comma, then I have to leave the data AS IS. See below for example. Input File : John Smith, Manager Smith, John Frank J F K... (2 Replies)
Discussion started by: msalam65
2 Replies

10. Shell Programming and Scripting

separating comma delimited words

Hi, I have file with text ________________________________ GROUP:firstname1.lastname1,first_name2.last_name2,first_name3.last_name3 HEAD:firstname.lastname ________________________________ I need help to pick the names separately ie.. Need out put as var1 =firstname1.lastname1... (4 Replies)
Discussion started by: rider29
4 Replies
Login or Register to Ask a Question