Visit Our UNIX and Linux User Community


Multiple records based on ';' in the record


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Multiple records based on ';' in the record
# 1  
Old 04-18-2011
Multiple records based on ';' in the record

Hi All,

I have a *.csv files in a die /pro/lif/dow, (pipe delimiter file), these files are having 8 columns and 6 column(CDR_LOGIC) records are populated as below, I need to incorporate the below logic in all the *.csv files.

11||:ColumnA||:ColumnB
123||:ColumnA
IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF)
Decode(:ColumnA, When :columnA = '123' then :columnF,
when :Column A = '1234' then :Column T END)

I have to create a new records based on the semi colon value i.e if the cdr_logic is having more than one semi colon the entire records should be copied in the next line as below.

1Column|2Column|3Column|4Column|6Column|7Column|8Column

my input
A|B|C|D|E|11||:ColumnA||:ColumnB|G|H --- 3rd line in the file

my output should be
A|B|C|D|E|11||:ColumnA||:ColumnB|G|H -- 3rd line in the file
A|B|C|D|E|11||:ColumnA||:ColumnB|G|H -- 4th line in the file


My input example 2
A|B|C|D|E|IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF)|G|H -- 10th line in the file

my output

IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF) -- 11th line
IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF) -- 12th line
IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF) -- 13 th line
IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF) -- 14th line

For each semi colon one line should be inserted below the record.

Can any please give me an idea on how to do this.

Thx,
Shrithi
# 2  
Old 04-18-2011
Hey,
This may not be possible in unix shell scripting , can't you handle this at source level.

As the columns are dynamic in the logic field you have to see other options in java or c.

--Mora
# 3  
Old 04-18-2011
Hi ,

It can be possible in unix..,gurus can you please give me some basic idea on how to do this.

Thx,
Shruthi
# 4  
Old 04-18-2011
No way we can do this in unix,you can capture the occurrences of semicolon but if you need to use as variables it can be difficult may be you can load the data in a database and write some procedural language to do this task.

--Mora
# 5  
Old 04-19-2011
Hey try this sed script

Code:
x
s/.*//
x
s/:/;/
/:/{
: label
H
s/:/;/
t label
g
}

the output will be ; separated instead of : separated, you can replace that using another sed... or in same, but I guess this will solve the core issue.

Last edited by 116@434; 04-19-2011 at 09:01 AM.. Reason: few more stuff to add
# 6  
Old 04-19-2011
Code:
awk -F"|" '{s=gsub(":",":",$6); for(i=1;i<=s+1;i++) print $0}' OFS="|"  input_file

# 7  
Old 04-19-2011
Code:
# x=$(sed 's/Col/&\n/g' infile|sed -n '/Col$/=' | sed -n '$p')
for ((i = 1 ; i <= $x ; i++ )); do  pr=(${pr[@]} ";p"  ) ; done ; sed -n "$(echo ${pr[@]})" infile
A|B|C|D|E|11||:ColumnA||:ColumnB|G|H
A|B|C|D|E|11||:ColumnA||:ColumnB|G|H

run same cod after results (pr array must zero every run)
Code:
A|B|C|D|E|IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF)|G|H
A|B|C|D|E|IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF)|G|H
A|B|C|D|E|IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF)|G|H
A|B|C|D|E|IIF(:ColumnA = :ColumnC then :ColumnD else :ColumnF)|G|H

regards
ygemici

Previous Thread | Next Thread
Test Your Knowledge in Computers #197
Difficulty: Easy
C# ranked higher than C according to the TIOBE Index for October 2019.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Display latest record from file based on multiple columns combination

I have requirement to print latest record from file based on multiple columns combination. EWAPE EW1SLE0000 EW1SOMU01 ABORTED 03/16/2015 100004 03/16/2015 100005 001 EWAPE EW1SLE0000 EW1SOMU01 ABORTED 03/18/2015 140003 03/18/2015 140004 001 EWAPE EW1SLE0000 EW1SOMU01 ABORTED 03/18/2015 220006... (1 Reply)
Discussion started by: tmalik79
1 Replies

2. Shell Programming and Scripting

How to split one record to multiple records?

Hi, I have one tab delimited file which is having multiple store_ids in first column seprated by pipe.I want to split the file on the basis of store_id(separating 1st record in to 2 records ). I tried some more options like below with using split,awk etc ,But not able to get proper output. can... (1 Reply)
Discussion started by: jaggy
1 Replies

3. Shell Programming and Scripting

Merge records based on multiple columns

Hi, I have a file with 16 columns and out of these 16 columns 14 are key columns, 15 th is order column and 16th column is having information. I need to concate the 16th column based on value of 1-14th column as key in order of 15th column. Here are the example file Input File (multiple... (3 Replies)
Discussion started by: Ravi Agrawal
3 Replies

4. Shell Programming and Scripting

Multiple Records from 1 Record

I need to make one record to multiple records based on occurence column in the record and change the date.For example below first record has 5 ,so need to create 5 records from one and change the date to 5 months.Occurence can be any number. I am unable to come with a script.Can some one help ... (5 Replies)
Discussion started by: traininfa
5 Replies

5. Shell Programming and Scripting

Splitting record into multiple records by appending values from an input field (AWK)

Hello, For the input file, I am trying to split those records which have multiple values seperated by '|' in the last input field, into multiple records and each record corresponds to the common input fields + one of the value from the last field. I was trying with an example on this forum... (4 Replies)
Discussion started by: imtiaz99
4 Replies

6. UNIX for Dummies Questions & Answers

Split single record to multiple records

Hi Friends, source .... col1,col2,col3 a,b,1;2;3 here colom delimeter is comma(,). here we dont know what is the max length of col3 means now we have 1;2;3 next time i will receive 1;2;3;4;5;etc... required output .............. col1,col2,col3 a,b,1 a,b,2 a,b,3 please give me... (5 Replies)
Discussion started by: bab.galary
5 Replies

7. Shell Programming and Scripting

Split a single record to multiple records & add folder name to each line

Hi Gurus, I need to cut single record in the file(asdf) to multile records based on the number of bytes..(44 characters). So every record will have 44 characters. All the records should be in the same file..to each of these lines I need to add the folder(<date>) name. I have a dir. in which... (20 Replies)
Discussion started by: ram2581
20 Replies

8. Shell Programming and Scripting

Multiple records based on :

Hi , I have the below source source data 1|2|3|:123:abc|4 1|2|a| | 5 1|2|3|4|:a:s:D.....:n|t Target data should be 1|2|3|:123:abc|4 1|2|3|:123:abc|4 1|2|a| | 5 1|2|3|4|:a:s:D.....:n|t 1|2|3|4|:a:s:D.....:n|t 1|2|3|4|:a:s:D.....:n|t 1|2|3|4|:a:s:D.....:n|t (3 Replies)
Discussion started by: mora
3 Replies

9. Shell Programming and Scripting

Parsing record into multiple records in Shell Script

Hi, I am trying to parse a very long record in a text file into multiple records by checking ADD, DELETE, or MODIFY field value in a shell script. Input # File name xyz.txt ADD|N000|8015662|DELETE|N001|9915662|MODIFY|N999|85678 Output ADD|N000|8015662| DELETE|N001|9915662|... (8 Replies)
Discussion started by: naveed
8 Replies

10. Shell Programming and Scripting

Record count based on a keyword in the records

Hi, Am having files with many records, i need to count and display the number of records based on the keyword in one of the column of the records. for e.g THE FILE CONTAINS TWO RECORDS LIKE. 200903031143150 0 1236060795054357lessrv1 BSNLSERVICE1 BSNLSERVICE1 ... (4 Replies)
Discussion started by: aemunathan
4 Replies

Featured Tech Videos