Strip some text and format with new delimited


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Strip some text and format with new delimited
# 1  
Old 12-24-2014
Strip some text and format with new delimited

Sample input
Code:
19:08:12.172; Cat1 74598; Cat2 1366; Cat3 227; Cat4 389; Cat5 572; Cat6 2228; Cat7 1039; Cat8 25;
19:08:22.173; Cat1 75589; Cat2 1388; Cat3 233; Cat4 393; Cat5 582; Cat6 2253; Cat7 1055; Cat8 25;
19:08:32.173; Cat1 76518; Cat2 1404; Cat3 238; Cat4 397; Cat5 592; Cat6 2270; Cat7 1069; Cat8 26;
19:08:42.173; Cat1 77471; Cat2 1430; Cat3 239; Cat4 403; Cat5 602; Cat6 2312; Cat7 1083; Cat8 28;

Output
Code:
19:08:00,74598, 1366, 227, 389, 572, 2228, 1039, 25
19:08:00,75589, 1388, 233, 393, 582, 2253, 1055, 25
19:08:00,76518, 1404, 238, 397, 592, 2270, 1069, 26
19:08:00,77471, 1430, 239, 403, 602, 2312, 1083, 28


Last edited by before4; 12-24-2014 at 08:27 AM..
# 2  
Old 12-24-2014
Hello before4,

Following may help you in same.
Code:
awk -F"; " '{gsub(/....$/,X,$1);for(i=2;i<=NF;i++){gsub(/.* /,X,$i);gsub(/\;$/,Y,$i)}}1'  OFS=", " Input_file

Output will be as follows.
Code:
19:08:12, 74598, 1366, 227, 389, 572, 2228, 1039, 25
19:08:22, 75589, 1388, 233, 393, 582, 2253, 1055, 25
19:08:32, 76518, 1404, 238, 397, 592, 2270, 1069, 26
19:08:42, 77471, 1430, 239, 403, 602, 2312, 1083, 28

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 3  
Old 12-24-2014
Quote:
Originally Posted by RavinderSingh13
Hello before4,

Following may help you in same.
Code:
awk -F"; " '{gsub(/....$/,X,$1);for(i=2;i<=NF;i++){gsub(/.* /,X,$i);gsub(/\;$/,Y,$i)}}1'  OFS=", " Input_file

Output will be as follows.
Code:
19:08:12, 74598, 1366, 227, 389, 572, 2228, 1039, 25
19:08:22, 75589, 1388, 233, 393, 582, 2253, 1055, 25
19:08:32, 76518, 1404, 238, 397, 592, 2270, 1069, 26
19:08:42, 77471, 1430, 239, 403, 602, 2312, 1083, 28

Thanks,
R. Singh
Working perfect .. Many thanks
# 4  
Old 12-24-2014
Wasn't the request to set the seconds to zero? Try
Code:
awk '{gsub(/; [^ ]* /,", "); sub(/:[^:,]*,/,":00,"); sub (/;$/,"")}1' file
19:08:00, 74598, 1366, 227, 389, 572, 2228, 1039, 25
19:08:00, 75589, 1388, 233, 393, 582, 2253, 1055, 25
19:08:00, 76518, 1404, 238, 397, 592, 2270, 1069, 26
19:08:00, 77471, 1430, 239, 403, 602, 2312, 1083, 28

These 2 Users Gave Thanks to RudiC For This Post:
# 5  
Old 12-24-2014
Thank you RudiC for pointing out same(Fan of you). Just edited a little to code to get the same output.
Code:
awk -F"; " '{gsub(/......$/,"00",$1);for(i=2;i<=NF;i++){gsub(/.* /,X,$i);gsub(/\;$/,Y,$i)}}1'  OFS=", " Input_file

Output will be as follows.
Code:
19:08:00, 74598, 1366, 227, 389, 572, 2228, 1039, 25
19:08:00, 75589, 1388, 233, 393, 582, 2253, 1055, 25
19:08:00, 76518, 1404, 238, 397, 592, 2270, 1069, 26
19:08:00, 77471, 1430, 239, 403, 602, 2312, 1083, 28

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 6  
Old 12-24-2014
You could also try:
Code:
awk -F'; [^ ]*' '{sub(/[^:]*$/,"00",$1);sub(/;$/,x)}1' OFS=, file

which also produces the same output.
Note that we're all assuming that the OP doesn't mind having a space in the output after the 1st comma in the output (which was not present in the sample desired output in the 1st post in this thread).
This User Gave Thanks to Don Cragun For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Get the output of w command in pipe delimited format

Since output of w command have variable number of columns I want to get the output in pipe delimited format. I tried export OFS="|"; w but that does not work. Any ideas? (4 Replies)
Discussion started by: Soham
4 Replies

2. UNIX for Dummies Questions & Answers

Need to convert a pipe delimited text file to tab delimited

Hi, I have a rquirement in unix as below . I have a text file with me seperated by | symbol and i need to generate a excel file through unix commands/script so that each value will go to each column. ex: Input Text file: 1|A|apple 2|B|bottle excel file to be generated as output as... (9 Replies)
Discussion started by: raja kakitapall
9 Replies

3. Shell Programming and Scripting

Append output in tab delimited format

hello.. i m scripting in Perl and having issue writing the output in specific format..i read two files and run some commands and write output to one file. i want this to be a 2d table, File one has 48 rows and file two has 48 rows, first i take one id from file one, and go to second file, loop... (2 Replies)
Discussion started by: empyrean
2 Replies

4. UNIX for Dummies Questions & Answers

How to convert a text file into tab delimited format?

I have a text file that made using text editor in Ubuntu. However the text file is not being recognized as space or tab delimited, the formatting seems to be messed up. How can I convert the text file into tab delimited format? (3 Replies)
Discussion started by: evelibertine
3 Replies

5. UNIX for Dummies Questions & Answers

How to convert text to columns in tab delimited text file

Hello Gurus, I have a text file containing nearly 12,000 tab delimited characters with 4000 rows. If the file size is small, excel can convert the text into coloumns. However, the file that I have is very big. Can some body help me in solving this problem? The input file example, ... (6 Replies)
Discussion started by: Unilearn
6 Replies

6. Shell Programming and Scripting

how to convert this file into comma delimited format

Hi experts, I need urget help! I have the a text file with this format: Types of fruits Name of fruits 1,1 Farm_no,1 apple,1 pineapple,1 grapes,1 orange,1 banana,1 2,2--->this is the record seperator Farm_no,2 apple,1 pineapple,1 grapes,3 orange,2 banana,1 3,3--->this is the... (1 Reply)
Discussion started by: natalie23
1 Replies

7. Shell Programming and Scripting

Help needed to convert delimited to CSV format

I have a file with the data as follows.. FILE 1|#START|Jan 22 15:03 FILE 1|#END|Jan 22 16:06 FILE 2|#START|Jan 22 5:15 FILE 2|#END|Jan 22 5:25 FILE 3|#START|Jan 22 07:03 FILE 3|#END|Jan 22 08:15 FILE 4|#START|Jan 22 16:09 FILE 4|#END|Jan 22 16:55 FILE 1|#START|Jan 22 18:15 FILE... (5 Replies)
Discussion started by: karthikd214
5 Replies

8. Shell Programming and Scripting

how to strip rows from a text file?

Can an expert kindly write an efficient Linux ksh script that will strip rows with no numbers from a text file? Supposing there are three rows that text file called text.txt : "field1","field2","field3",11,22,33,44 "field1","field2","field3",1,2,3,4 "field1","field2","field3",,,, The... (5 Replies)
Discussion started by: ihot
5 Replies

9. Shell Programming and Scripting

How do I strip and add tabbed fields to a long text file?

I was wondering if there was a way to modify a tab delimited text file, up to 185,000 lines long by adding a repeated field to a block of 20 data , then repeat the process until the file is processed. The current file looks like: ... (3 Replies)
Discussion started by: saint65
3 Replies
Login or Register to Ask a Question