how to copy data to to excel file

Shell Programming and Scripting
1

Hi,

Can any one tell me how to copy data using shell script to a excel file from text file to other columns of excel file,leaving first column unaffected i.e it should not overwrite data in first column.

Say my text file data is:

15-dec-2008 15-dec-2009
16-dec-2008 16-dec-2009

say my first excel column is:

column1 column2 column3
server1
server2

I want output as below:

column1 | column2 | column3
server1 | 15-dec-2008 | 15-dec-2009
server2 | 16-dec-2008 | 16-dec-2009

Thanks in advance........

2

Hi,

try:

awk 'NR==FNR{a[NR]=$0}\
  NR!=FNR{if(FNR==1){print $0}else{print $0, a[++x]}}' data excel \
  | sed 's/\s\+/ | /g'

output:

column1 | column2 | column3
server1 | 15-dec-2008 | 15-dec-2009
server2 | 16-dec-2008 | 16-dec-2009

HTH Chris

3

A CPAN module is available for that

Spreadsheet::ParseExcel::SaveParser - Expand of Spreadsheet::ParseExcel with Spreadsheet::WriteExcel - search.cpan.org

4

Hi Christoph Spohr,

Can you please explain the above code in detail......and what does data and excel stand for? is it for text file and excel file respectively?

5

data stands for the data file with dates.
excel for the file containing column and server info.

The simplest way to merge the two files would have been
the unix "paste" command. But then one would have to take care
of the first line of the excel file separately. So i chose awk.

NR==FNR                      as long as reading the first file,
{a[NR]=$0}\                  save every line in an array a,
NR!=FNR{if(FNR==1){print $0} when reading the second file print the first line
else{print $0, a[++x]}}'     else print the whole line and add the content
                             of the array.
sed 's/\s\+/ | /g'           finally replace spaces by " | ". I use sed as i 
                             had not luck trying this in awk
6

Hey thanks man for your quick reply...Will try it........

7

Hi Chris,

I tried your code....

My data.txt

dec-15-2008 dec-16-2008
dec-14-2008 dec-17-2008

Documents.xls

server1
server2

excel.sh

awk 'NR==FNR{a[NR]=$0}\
NR!=FNR{if(FNR==1){print $0}else{print $0, a[++x]}}' data.txt Documents.xls \
| sed 's/\s\+/ | /g'

I got the following output:

./excel.sh

"server1"

"server2" |dec-15-2008 | dec-16-2008

the second row is not copied and it copied data to second row instead starting from the first row.

can you please help me on this?

8

Try this one:

(echo "column 1| column 2    | column 3"; \
  paste Document.xls data.txt \
  | sed 's/\s\+/ | /g')

Of course the first line is not printed using the other command
as our sample file started with the "column..." line. Thus this line
is printed and then the rest is printed and pated.
If your file doesn't start with "column..." you'll have to adopt the command.