shell programming - merge 2 files and give desired output

shell programming- sample file is below

I have 2 data files

data1

1,tom,tom@a.com
2,mike,mike@a.com

data2

1|05/05/2018/20000
2|06/05/2018/25000

Need help to merge the files and give me the desired output

1,05/05/05/2018,20000,tom, tom@a.com
2,06/05/05/2018,25000,mike, mike@a.com
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dfkeCommented:
Hi,

You can write a script but with sed and paste you can achieve this fairly easy:

Remove the first character in data1 and write output to data1-new
sed 's/^.//' data1 > data1-new

Open in new window

Replace the '|' with a ',' in data2 and write output to data2-new
sed 's/|/,/g' data2 > data2-new

Open in new window

Now merge data1 with data2, add a 'space' after the third ',' and output to file data3
paste -d' ' data2-new data1-new | sed 's/ //' | sed 's/ /|/3' > data3

Open in new window

All this won't alter the original files.

Cheers
0
anumosesAuthor Commented:
$ cat first.sh
Hello World!
Knowledge is power.


sed 's/^.//' data1 > data1-new

sed 's/|/,/g' data2 > data2-new

paste -d' ' data2-new data1-new | sed 's/ //' | sed 's/ /|/3' > data3

when I try to run

$ ./first.sh

errors

$ ./first.sh
./first.sh: Hello:  not found
./first.sh[2]: Knowledge:  not found
./first.sh[3]: ^M:  not found
./first.sh[4]: ^M:  not found
sed: Cannot find or open file data1.
./first.sh[6]: ^M:  not found
sed: Cannot find or open file data2.
./first.sh[8]: ^M:  not found
paste: data2-new : Cannot open the file.
0
dfkeCommented:
Hi,

If you insist it to be scripted then it can be more like:

#!/bin/bash

echo Hello World!
echo Knowledge is power.

cd /path/to/your/data/files

sed 's/^.//' data1 > data1-new
sed 's/|/,/g' data2 > data2-new
paste -d' ' data2-new data1-new | sed 's/ //' | sed 's/,/, /3' > data3

Open in new window

Just change /path/to/your/data/files to the absolute path of your datafiles.

This will generate a merged data3 file.

The ^M (CTRL+V+M) that you get is because you edited the file in Windows and it needs to be in unix format before being executed.

Cheers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

tel2Commented:
Hi dfke,
Have you compared your data3 output file with the desired output in the original post?  If you think they're the same, please post your data3 contents here.

Hi anumoses,
Are you wanting records to be merged based on some key (e.g. the 1st field of both input files), or will you always want the 1st record of data1 to be merged with the 1st record of data2, then the 2nd record of data1 with the 2nd record of data2, etc?  I can't tell which you want based on the examples and description you've provided.
0
dfkeCommented:
Hi ,

You can see for yourself and run the last script with the given input data1 and data2 files.  
The output in data3 is exactly as requested by anumoses

Cheers
0
tel2Commented:
Hi dfke,
I have seen for myself, and there is a slight difference when I run it on my machine, but I'm interested to see the output you get, which is why said:
"If you think they're the same, please post your data3 contents here."
If you can't spot the differences then run a diff on them.
0
tel2Commented:
Hi anumoses,

3 questions for you:
Looking at your sample data2 file, where you've written this:
    1|05/05/2018/20000
Q1. Do you really mean "/20000", or should it be "|20000"?

And looking at your desired output, where you've written this:
    1,05/05/05/2018,20000,tom, tom@a.com
Q2. Do you really mean: "05/05/05/2018" or should it be "05/05/2018"?

Q3. And do you really mean "tom, tom@a.com" or should it be "tom,tom@a.com" (no space)?
0
anumosesAuthor Commented:
Did not really help a lot. But I need to close this
0
tel2Commented:
Hi anumoses,

One reason why it probably did not really help alot is, I asked you 4 questions to clarify your requirements (the last 3 were clearly labelled Q1, Q2 & Q3) and you responded to none of them.  It's hard to help someone who will not clarify questions raised about what's required.
You didn't even tell dfke what you didn't like about his solution.

Hi dfke,
> "The output in data3 is exactly as requested by anumoses"
This is what I get when I compare anumoses's with expected results with the output of your script:

$ diff anumoses.out dfke.out
1,2c1,2
< 1,05/05/05/2018,20000,tom, tom@a.com
< 2,06/05/05/2018,25000,mike, mike@a.com
---
> 1,05/05/2018/20000,tom, tom@a.com
> 2,06/05/2018/25000,mike, mike@a.com
Yours omits a 2nd "/05" and also has a "/" before the 5 digit number instead of a ",".
Yours might be what is wanted (we don't know since anumoses has not said), but it's not the same as his.
What do you get when you compare the two outputs?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Bash

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.