CODEWITHSUNDEEP
bashshellmergepaste
MR.IT
MR.IT

Reputation: 51

How to combine some files with different sizes into one excel file using shell?

I have these files, and I want to merge them in an excel file each file have two columns

file1

title1
 1 1 
 2 2 
 3 3

file2

title2
 5 5
 6 6 
 7 7
 8 8 
 9 9
 10 10

file 3

title3
21 21
22 22
23 23
24 24

i use command below to merge them in a excel file

paste file* > out.csv

the normal output is ok,but when i put it in an excel file(out.csv), the output become like this

title1 title2 title3
1  1   5  5   21  21
2  2   6  6   22  22
3  3   7  7   23  23
   8  8   24  24
   9  9
  10 10

but I want this output

title1 title2 title3
1  1   5  5   21  21
2  2   6  6   22  22
3  3   7  7   23  23
       8  8   24  24
       9  9 
       10 10

each number should be in separate cells, separate columns and exactly below each other but when the size of a file is longer than the previous file, the additional numbers go back to last column and below the previous file numbers, for example numbers 8,9,10 and 24

Upvotes: 2

Views: 996

Answers (2)

dogbane
dogbane

Reputation: 274542

You can try the following script:

#!/bin/bash
maxLines=$(wc -l file1 file2 file3 | grep -v total | sort -nr | head -1 | awk '{print $1}')
for f in file1 file2 file3
do
    sed 's/ /,/g' "$f" > "$f".tmp
    lineCount=$(wc -l < $f.tmp)
    linesToAdd=$((maxLines-lineCount))
    for ((i=0;i<linesToAdd;i++))
    do
        echo "," >> "$f".tmp
    done
done
paste -d, file{1,2,3}.tmp | sed '1 s/,/,,/g'
rm file*.tmp

Output:

$ myscript.sh
title1,,title2,,title3
1,1,5,5,21,21
2,2,6,6,22,22
3,3,7,7,23,23
,,8,8,24,24
,,9,9,,
,,10,10,,

The script works by calculating the max number of lines in the files and then appending extra rows to the other files. For example, in this case, file2 has 7 rows and file1 has 4, so the script will temporarily add an extra 3 comma-delimited rows to file1. Finally, paste is used to merge the files.

Upvotes: 2

anishsane
anishsane

Reputation: 20980

Try this:

#/bin/bash
maxcount=`read wc -l file1  file2  file3 | sort -h -r | sed -rn '2s/^ *([0-9]+).*/\1/'`
exec 4<file1
exec 5<file2
exec 6<file3
while [ $maxcount -gt 0 ]; do
    read -a f1<&4
    read -a f2<&5
    read -a f3<&6
    echo ${f1[0]},${f1[1]},${f2[0]},${f2[1]},${f3[0]},${f3[1]}
    ((maxcount--))
done
exec 4<&-
exec 5<&-
exec 6<&-

I have hard-coded max columns for each file as 2. However, if that varies, you can get max column for each file & run a loop with echo -n ${f1[$i]}

Upvotes: 1

Related Questions