2

I have two files as such:

fileA:

Name,Site Name,Product UPEI,Product Name,NMS Flag,Product Model, SW

FileB:

NE_Name       SW   
ABC           4.4
ASD           4.3
...           ...

Note that fileA just consists of a single line and it has several headers (eg, Name, Site Name etc.). Whereas, fileB has 2 fields with huge number of lines.

I want to combine these 2 files as such:

Output file:

Name,Site Name,Product UPEI,Product Name,NMS Flag,Product Model, SW
ABC,,,,,,4.4
ASD,,,,,,4.3

Thus, output file would have all the headers present in fileA and would expand the fields: 'Name' (with the 'NE_Name' values fetched from fileB) and 'SW' (with the 'SW' values fetched from fileB).

How can I get this output file from the 2 input files mentioned above?

1
  • Could you care for a little better explanation. cat fileA and paste the output; cat fileB and paste the output & the expected output
    – neuron
    Jul 23 '15 at 14:09
2

Here is one way with awk:

$ awk -F, 'NR==1{nf=NF-1; print $0; FS=" "} \
         NR>2{printf("%s%.*s%s\n",$1,nf,",,,,,,,,,,,,,,,,,",$2)}' fileA fileB

Name,Site Name,Product UPEI,Product Name,NMS Flag,Product Model, SW
ABC,,,,,,4.4
ASD,,,,,,4.3

Crucial part is printf where we print first and third fields from fileB separated by , which number is taken from first file as nf=NF-1.

7
  • Thanks a lot for the response. However, I would like to ask some questions to understand your command better: 1- Why you set 'nf=NF-1'? Why not to NF directly? 2- I guess, 'NR==1' phrase cause the firs line operation to be handled in fileA (this file has just 1 record) and 'NR>2' phrase cause the second line to be handled in fileB since it has 2 records. Am I correct? 3- Can you describe what this phrase do exactly: 'printf("%s%.*s%s\n",$1,nf,",,,,,,,,,,,,,,,,,",$2'? Thanks...
    – Murat
    Jul 24 '15 at 8:22
  • @Murat 1) The point is to take number of delimiters , from fileA, and use it in fileB, so that if you add another header (field) to fileA it will be automatically transformed to fileB. To achieve that I use NF parameter which returns number of fields in the row, however number of delimiters is NF-1. 2) Yes NR==1 corresponds to fileA, NR==2 would be first line in fileB, but we don't need that line, so we skip to NR>2 - line third and rest (line 2 in fileB and rest). 3) printf prints $1 then very long string of , cut to NF-1 characters, then $2.
    – jimmij
    Jul 24 '15 at 10:07
  • Thanks for your help. The last questions: 1- Could you please describe the logic of the phrase ' FS=" " ' located in the first line of your command? 2- What is the meaning of the phrase: ' %.*s '? Thanks...
    – Murat
    Jul 24 '15 at 10:43
  • @Murat FS=" " sets field separator to space and %.*s prints string cut to defined number of characters. Try printf "%.*s\n" 3 "abcdef" in bash.
    – jimmij
    Jul 24 '15 at 10:49
  • I think you set the field separator to space for fileB because it has columns seperated by tabs? I was thinking that file separator for fileA is comma so I was wondering why you set the file separator to space. So, you just printed the whole line of fileA and then set the file separator to space to be able to separate the columns of fileB. Right?
    – Murat
    Jul 24 '15 at 10:58
2
tail -n+2 fileB | sed -r 's/[[:space:]]+/,,,,,,/' | cat fileA - > Output

Explanations

tail -n +2 fileB writes the end of fileB, starting at the second line, since we want to ignore the first line.

sed, using -r, --regexp-extended (for the + meta-character meaning 'at least one') replaces the first group of spacing characters by the expected amount of commas as of your example.

cat finally concatenates fileA and the data received from sed through the pipe, referred to as -, and writes it to stdout, which is redirected to the Output file as of your request.

Limitations

Will not work if there are space characters in NE_Name or SW columns.

2
  • 1
    Your solution without tail and the GNU -r option sed -e '1d' -e 's/[[:space:]]*[[:space:]]/,,,,,,/' fileB | cat fileA - > Output
    – fd0
    Jul 23 '15 at 15:23
  • Clever use of 1d, didn't cross my mind. is there any reason to use [[:space:]]*[[:space:]] instead of [[:space:]][[:space:]]* ? I would have assumed that the second option fails faster for non-space characters.
    – Emeric
    Jul 23 '15 at 15:29
1

Another sed approach:

$ sed 's/   */,,,,,,,/;/NE_/d' fileA fileB
Name,Site Name,Product UPEI,Product Name,NMS Flag,Product Model, SW
ABC,,,,,,,4.4
ASD,,,,,,,4.3

The 1st sed command replaces 3 or more spaces with 7 commas and the 2nd removes any line matching NE_.

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.