1

The description field value might be present in multiple lines. I would like to put it into single line like below. The file will have fixed number of columns.

Input File:

Number|Level|Description|Unit|Rate|Special Rate|Notes  
101|0|Apple, Orange, Banana||6.80%|8.56|Free                
   |1|Fruits:||||   
102|2|Banana,      
Orange, Grapes  
Kiwi||||

Any line that does not contain "|" or the lines that contain "|" but the count of "|" in the line is equal to 4, then the line should be appended to the previous line which contains "|"

Output File:

Number|Level|Description|Unit|Rate|Special Rate|Notes      
101|0|Apple,Orange, Banana||6.80%|8.56|Free                 
   |1|Fruits:||||       
102|2|Banana,Orange, Grapes Kiwi||||
  • your output is unclear. For ex. the 2nd line doesn't fit any of conditions but it was appended to the 1st line. Elaborate your conditions OR update your output – RomanPerekhrest Dec 19 '17 at 7:06
  • 2nd Line should not be appended to the first line.. Updated the required output – Harish Dec 19 '17 at 8:13
  • It seems to me that what you might want is to rewrap the CSV file to a given width so that all of the rows have the same number of columns. Is that what you want or do you really just literally want to do what's stated in the question? – igal Dec 19 '17 at 8:34
  • yes.. I want to rewrap the text file so that all of the rows have same number of columns – Harish Dec 19 '17 at 9:43
  • @Harish Did any of the answers below help? If so please see stackoverflow.com/help/someone-answers . If not please specify what is missing. – B Layer Dec 30 '17 at 3:26
0

Here is a simple Python script that does what you want:

#!/usr/bin/env python2
# -*- ascii -*-
"""rewrapcsv.py"""

import sys

datafile = sys.argv[1]
columns = int(sys.argv[2])

with open(datafile) as filehandle:
    buffer = []
    for line in filehandle:
        buffer.extend(line.strip().split('|'))

    while len(buffer) >= columns:
        print('|'.join(buffer[0:columns]))
        buffer = buffer[columns:]

    print('|'.join(buffer[0:columns]))

Here is an example data file (data.csv) taken from your question:

Number|Level|Description|Unit|Rate|Special Rate|Notes
101|0|Apple, Orange, Banana||6.80%|8.56|Free
|1|Fruits:||||
102|2|Banana,
Orange, Grapes Kiwi||||

Here is how you could run the script:

python rewrapcsv.py data.csv 6

And here is the output:

Number|Level|Description|Unit|Rate|Special Rate
Notes|101|0|Apple, Orange, Banana||6.80%
8.56|Free||1|Fruits:|
|||102|2|Banana,
Orange, Grapes Kiwi||||
0

Awk solution:

awk -F'|' 'r{ if (NF!=1 && NF!=5) print r; else { print r,$0; next } }{ r=$0 }' file
  • -F'|' - field separator
  • r - contains previous record

The output:

Number|Level|Description|Unit|Rate|Special Rate|Notes
101|0|Apple, Orange, Banana||6.80%|8.56|Free
   |1|Fruits:||||
102|2|Banana, Orange, Grapes Kiwi||||
0

sed solution:

sed -E -e :a -e '/(\|.*){6,}/!N;s/\n/ /;ta' file

The extended regular expression (\|.*){6,} matches a line with at least 6 bars in it. If the current line doesn't match that, append the next line (!N).

s/\n/ / replaces a newline with a space. If this replacement was made, it means that a line got appended, so we need to test again, so ta jumps to mark :a if the replacement was made. Done.

0

Using Ex...

ex +'g/^\([^|]\+\(|[^|]*\)\{4}\|[^|]\+\)$/norm! kgJ' +wq file

Or, same thing but with "very magic" mode (the \v at the start of the pattern)...a bit easier on the eyes, IMO, as only the literal | need to be escaped...

ex +'g/\v^([^\|]+(\|[^\|]*){4}|[^\|]+)$/norm! kgJ' +wq file

Vim implements Ex in modern systems so if you know Vim these ex commands may look familiar. The first command (after the first +) is a "global" command. If one of the patterns (no | or four | in a line) is found then the Normal mode commands for moving up one line (k) and joining the line that follows (gJ) are executed.

This is one of the few portable solutions that will update the file in-place. (GNU's sed -i, for example, uses temp files).

Here's your input plus a test for the second pattern ("line doesn't contain |"):

Number|Level|Description|Unit|Rate|Special Rate|Notes  
101|0|Apple, Orange, Banana||6.80%|8.56|Free                
   |1|Fruits:||||   
102|2|Banana,      
Orange, Grapes    Kiwi||||
103|2|Watermelon,
Pear, Raspberry, Lime

Result:

Number|Level|Description|Unit|Rate|Special Rate|Notes
101|0|Apple, Orange, Banana||6.80%|8.56|Free
   |1|Fruits:||||
102|2|Banana,Orange, Grapes    Kiwi||||
103|2|Watermelon,Pear, Raspberry, Lime

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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