3

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.

  • By what logic do you know that select varieties<NL>up to should join with a space, whereas 06<NL>-JAN-10 should join without a space? – Kaz Jun 24 '15 at 23:38
  • Does it contain newline characters? – mikeserv Jun 25 '15 at 6:07
1
tr -d \\n <infile | tr \~ \\n | paste -d~ - - - - - - - - - - - - - -

That will work.

  • Elegant solution! – user1404316 Mar 7 '18 at 18:42
0

You could try using sed :

sed -rn ':a;/^([^~]*~){13}[^~]*$/!{N;s/\n//;ba};p' yourfile.tsv

What it does

The script has three parts separated by a ;:

  • :a defines a label we can branch to
  • /^([^~]*~){13}[^~]*$/!{N;s/\n//;ba} searches for a complete column
    • /^([^~]*~){13}[^~]*$/ looks for a line with exactly 14 fields (0 or more occurrences of anything that is not a ~), separated by 13 occurrences of ~.
    • ! Inverts the search result (if not found, then...)
    • {N;s/\n//;ba} the block that is executed if the column is not complete
      • N reads in another line
      • s/\n// removes the newline between the two lines
      • ba branches (b) to our previously defined label (a)
  • p prints the complete column
0

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.

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.