Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have a txt file that is a dump from a database, that contains one entry per line. The structure is like this:

1500
29/03/2010 
18
02
09
47
17
45
28.248
0
0.01
130
12.721
7908
298,809
YES
3.046.550,39
6.500.000,00
17,444,222


1501
30/03/2010
27
54
28
50
22
03
37.223
0
0.00
97
22,466
7379
421.90
NO
20,262,429
25,000,000.01
17,995,281.33


... the third record starts here

The database contains 21 fields. The previous lines shows the dump of two records of that database. The blank lines represent blank fields on the database.

The first field (F0) is the number you see 1500, 1501...

The second field (F1) is a date in the format day, month, year.

Fields F2, F3, F4, F5, F6, F7 are six integer numbers.

What I need is to extract F0, F2, F3, F4, F5, F6, F7 from this file creating one line for each one.

Given the two records above, the final file would be

1500,18,02,09,47,17,45
1501,27,54,28,50,22,03

I know how to do that using a bash script that will be miles long and interact over each line, etc. But I also know that unix is a bag of tricks, specially the sed command and that this probably can be done with a simple line. I love to learn new stuff, so I ask you guys that are gods in Unix, how do I do that.

I am on OSX Mavericks. Thanks.

share|improve this question
    
Will the last field always be empty, i.e. will there always be an empty line between records? If not, then @terdon's code will not work correctly. It depends on the empty line (paragraph mode reading). –  Dubu Apr 15 at 10:39

3 Answers 3

up vote 3 down vote accepted

Here's one way:

$ perl -000ne '@f=split(/\n/); print join(",",@f[0,2..7]) , "\n"' file.txt  
1500,18,02,09,47,17,45
1501,27,54,28,50,22,03

Explanation:

  • -000 : activates "paragraph mode", it sets perl's field delimiter to \n\n, consecutive newlines. This means that it will treat each of your records as a single line.

  • @f=split(/\n/); : split the current line (the record) on newlines and save as the array @f. This array now contains each field from your record. This means that the array slice @f[0,2..8] will contain fields 0 and 2 through 8.

  • print join ",",@f[0,2..8] , "\n"' : this will join the array slice with commas, and print the resulting string followed by a newline.

share|improve this answer
    
nice solution :) –  Ramesh Apr 14 at 20:40
    
almost there. I have changed your code to include just the fields I have asked: I have changed the line @f[0..8] to @f[0,2,3,4,5,6,7] but my problem is that a comma is included at the end of each line... –  Digital Robot Apr 14 at 20:58
    
@DigitalRobot ouch. Thanks for the edit, I had indeed misread the question. I also had a silly mistake which was adding the commas. Try the updated version. –  terdon Apr 14 at 21:04
    
SIMPLY PERFECT!!!!!!!!!!!!!!!!!! You are a genius and awesome!!!! Can't up vote you enough! Care to explain why adding a parenthesis there solved the problem? –  Digital Robot Apr 14 at 21:06
    
@Gnouc thanks for the edit, that's what I'd used initially and forgot to edit the OP's suggestion. –  terdon Apr 15 at 1:39

Using awk:

awk '
  BEGIN {
    fields[1]
    fields[3]
    fields[4]
    fields[5]
    fields[6]
    fields[7]
    last_field=8
  }
  ( NR%21 in fields ) { printf($0",") }
  NR%21==last_field' in_file.txt

Or better yet:

awk '
  NR%21 ~ /^(1|3|4|5|6|7)$/ { printf($0",") }
  NR%21==8' in_file.txt

GNU sed has a nice extension to match the nth line after a given one which is useful here. It won't work on OSX, but for the fun of it:

sed -n '
  1~21 { h }
  3~21,7~21 { H }
  8~21 { H; g; s/\n/,/gp }' in_file
share|improve this answer
    
awesome, thanks!!!!!!!!!!!! Nice answer! –  Digital Robot Apr 14 at 21:49
    
@DigitalRobot, thanks. Took me a few tries, but I got there in the end. –  Graeme Apr 14 at 21:51
    
@awk_FTW, yeah I made the same mistake at first, but it is the indices that matter for in not the values. If you can find a way to shorten it though, feel free. –  Graeme Apr 15 at 1:32

If your data always have some number of missing fields (i.e. 2 or more hard returns between records), you could simply:

$ awk -v RS="\\n{2,}" -F"\\n" -v OFS="," '{print $1, $3, $4, $5, $6, $7, $8}' file.txt
1500,18,02,09,47,17,45
1501,27,54,28,50,22,03
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.