Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Separate lines in csv by column content (newbie)

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Separate lines in csv by column content (newbie)
# 1  
Old 01-17-2012
Separate lines in csv by column content (newbie)
Hello everyone

I have a csv file organized just like in the following example:
Code:
col1,col2,col3,CODE_0, ... , colN
col1,col2,col3,CODE_0, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_2, ... , colN
col1,col2,col3,CODE_2, ... , colN
 ...
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN

I need to introduce one common line of text (say one single line of dashes "------------------") everytime the 4th column changes. It doesn't matter how many ocurrences of that column happen. For example:
Code:
col1,col2,col3,CODE_0, ... , colN
col1,col2,col3,CODE_0, ... , colN
------------------
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
------------------
col1,col2,col3,CODE_2, ... , colN
col1,col2,col3,CODE_2, ... , colN
------------------
(...)
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
------------------

I think 'awk' perfoms very well with these problems, but I'm not an expert. Any help is really well appreciated

Best regards

Moderator's Comments:
Mod Comment Please use next time code tags for your code and data
Last edited by vbe; 01-17-2012 at 10:54 AM..
# 2  
Old 01-17-2012
Code:
 
#!/bin/bash
oldtag=nothing
while read line
do
newtag=(echo $line|cut -d "," -f4)
if [ $oldtag != $newtag ]; then
echo "-------------------------------------" >> newfile
fi
echo $line >> newfile
oldtag=$newtag
done < oldfile

Check the new file whether it is as expected and rename it to old file.
This User Gave Thanks to muralikrishna For This Post:
yomaya
# 3  
Old 01-17-2012
If Perl is an option, then here's one way -

Code:
$
$
$ cat f48
col1,col2,col3,CODE_0, ... , colN
col1,col2,col3,CODE_0, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_2, ... , colN
col1,col2,col3,CODE_2, ... , colN
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
$
$ perl -F, -lane 'print "-"x40 if defined $prev and $F[3] ne $prev; print $_; $prev=$F[3]' f48
col1,col2,col3,CODE_0, ... , colN
col1,col2,col3,CODE_0, ... , colN
----------------------------------------
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
----------------------------------------
col1,col2,col3,CODE_2, ... , colN
col1,col2,col3,CODE_2, ... , colN
----------------------------------------
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
$
$

tyler_durden
This User Gave Thanks to durden_tyler For This Post:
yomaya
# 4  
Old 01-17-2012
Thank you both!
The perl code performs great!

Just one more thing: I need the script to add one last line with dashes after the CODE_N block. How could this be done with your perl code?
# 5  
Old 01-17-2012
try this

Code:
awk 'NR%4==0{print "--------------";next;} {print} END{print "--------------"}' filename

Last edited by tarun_agrawal; 01-18-2012 at 12:19 AM..
This User Gave Thanks to tarun_agrawal For This Post:
yomaya
# 6  
Old 01-18-2012
Append this in tyler_durden's perl one-liner:
Code:
; END{print "-"x40}' f48

This User Gave Thanks to balajesuri For This Post:
yomaya
# 7  
Old 01-18-2012
Code:
$ nawk -F, '{if(a!=$4){print "----------";print $0;a=$4}else{print $0;a=$4}}END{print "-----------"}' input.txt  
----------
col1,col2,col3,CODE_0, ... , colN
col1,col2,col3,CODE_0, ... , colN
----------
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
col1,col2,col3,CODE_1, ... , colN
----------
col1,col2,col3,CODE_2, ... , colN
col1,col2,col3,CODE_2, ... , colN
----------
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
col1,col2,col3,CODE_N, ... , colN
-----------

This User Gave Thanks to itkamaraj For This Post:
yomaya
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to Dump data into CSV file which is Separate by <tab>?

Dear Team, please help me to solve this problem using Linux command. I want to dump this data into an excel sheet, Suppose I have a string like: ABC PQR XYZ ASD then I expect output as a ABC XYZ PQR ASD (3 Replies)
Discussion started by: Shubham1182
3 Replies

2. Shell Programming and Scripting

How to separate data coming in one column of CSV file?

I am running an ISQL command on Sybase DB and getting output of a query in an CSV file. The issue is that all the data comes in to the same column, i want them to be separated in different columns. SQL_COMMAND=command.sql file=file.txt formatFile=formatFile.txt report=report.csv echo... (1 Reply)
Discussion started by: Sharma331
1 Replies

3. UNIX for Dummies Questions & Answers

Fill csv entire column with content from another text file

I have a csv that looks like this: ,yude-to-nap2,0,0,0,0,0 ,2twis-yude-to-nap2,0,0,0,0,0 ,2tiws-yude-to-nap2,0,0,0,0,0 ,2arcos-yude-to-nap2,0,0,0,0,0 and another file named m1 that has a single line of text as content: Feb 1 15:30:20 How can I fill the whole the empty column of the... (1 Reply)
Discussion started by: RobertoRivera
1 Replies

4. Shell Programming and Scripting

Csv file separate using awk

Hi, I have file like below apple,orange,pineapple,pappya,guva,avocado want to store as apple orange pineapple pappya I tried below command to seprate first field command1: (3 Replies)
Discussion started by: stew
3 Replies

5. Shell Programming and Scripting

Need help with awk statement to break nth column in csv file into 3 separate columns

Hello Members, I have a csv file in the format below. Need help with awk statement to break nth column into 3 separate columns and export the changes to new file. input file --> file.csv cat file.csv|less "product/fruit/mango","location/asia/india","type/alphonso" need output in... (2 Replies)
Discussion started by: awk-admirer
2 Replies

6. Shell Programming and Scripting

Print every 5 4th column values as separate row with different first column

Hi, I have the following file, chr1 100 200 20 chr1 201 300 22 chr1 220 345 23 chr1 230 456 33.5 chr1 243 567 90 chr1 345 600 20 chr1 430 619 21.78 chr1 870 910 112.3 chr1 914 920 12 chr1 930 999 13 My output would be peak1 20 22 23 33.5 90 peak2 20 21.78 112.3 12 13 Here the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

7. Shell Programming and Scripting

Merge *.csv files, each in separate sheets

Does anyone know how to Merge *.csv files, each in seperate sheets? (7 Replies)
Discussion started by: frhling
7 Replies

8. UNIX for Advanced & Expert Users

Parse (delimited string) key-value pairs in a column into separate lines

Hi experts, e.g. i/p data looks like 0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747,9700005405717924,9700005405733788|unidentified,unidentified,unidentified|| o/p data should like - row1: 0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747|unidentified ... (1 Reply)
Discussion started by: sumoka
1 Replies

9. Shell Programming and Scripting

Convert xlsx worksheet to separate csv

Hi All, I need a solution to convert my .xlsx file which has lot of worksheet, convert to separate csv file. Is there any shell script to do this? I can’t use excel macro to do this, since it was password locked. I need the csv output as an input to my shell script. (1 Reply)
Discussion started by: ranjancom2000
1 Replies

10. UNIX for Dummies Questions & Answers

How to separate file content

Hi All, I have a file like below which is having 3 fields namely companycode, companylocation & companyaddress and i want to separate all the fields individually. How can i do? File Structure: ---------------- Aberdeen Centre Aberdeen The Ming An Ins Co (HK) Ltd Academic... (30 Replies)
Discussion started by: vinsara
30 Replies
Login or Register to Ask a Question