Question: We are using flat text files to store in teradata table after transformations from Informatica. The file contains 14 fields (~ separated). But as the records are getting scattered over multiple lines informatica is not able to pick it up.

Is there's any way that we join the record by counting the delimiter using sed/awk or any other command?

Sample record---

```
48602040112~4100010080701242015~2010-01-21 10:23:44~Foods~7~Poultry ~Perdue Smart Chicken~Circular~06
-JAN-10~24-JAN-10~$5.99~24 oz., select varieties
up to 4 at this price, additional
Save up to $4.00 
load up on savings~~1598

```

Ideally it should be like --

```
48602040112~4100010080701242015~2010-01-21 10:23:44~Foods~7~Poultry ~ Perdue Smart Chicken~Circular~06-JAN-10~24-JAN-10~$5.99~24 oz., select varieties up to 4 at this price, additional Save up to $4.00 load up on savings~~1598

```

If it's not obvious, it contains new line characters.

Answer: 
```
tr -d \\n <infile | tr \~ \\n | paste -d~ - - - - - - - - - - - - - -

```

That will work.
It's not clear how to decide whether to join a split field with a space or without a space. `06<NL>-JAN-10` needs to have the newline removed, whereas `varieties<NL>up to` needs to have it replaced by a space.

Ignoring the above concern, we can arrive at this prototype Awk command:

```
$ awk 'BEGIN { RS = "~" }
       { gsub(/\n/,"",$0);
         printf("%s", $0);
         if (++i < 14) { printf("~"); }
         else { i = 0; printf("\n"); } }' < in.txt > out.txt

```

We get awk to split records on `~` so that each field is actually a record. Then just trim blanks from each record with `gsub`, and print it followed by the separator if it is field 1 through 13, or else followed by a newline if it is 14 (and reset the count).

If the number of fields is not divisible by 14, you will get an incomplete last line in the output, terminated by a `~` instead of newline.

A possible way to deal with the join issue is to leave the newlines in place, or possibly replace them with some character which does not occur in the data. Do the joining as a post-processing step. Suppose that `@` doesn't occur in the data. If we replace newlines by `@`, then we can apply some simple pattern replacements with the usual text processing tools. For instance replacement along the lines of `s/([0-9])@-/\1-/` handles broken dates like `06@-JAN`.

It may be necessary for this logic to understand the data type of the specific field that requires newline removal, so that it can, for instance, treat dates specially.