how to copy data to to excel file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to copy data to to excel file
# 1  
Old 12-28-2008
how to copy data to to excel file

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  
Old 12-28-2008
Hi,

try:

Code:
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:
Code:
column1 | column2 | column3
server1 | 15-dec-2008 | 15-dec-2009
server2 | 16-dec-2008 | 16-dec-2009

HTH Chris
# 3  
Old 12-28-2008
# 4  
Old 12-29-2008
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  
Old 12-29-2008
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.

Code:
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  
Old 12-29-2008
Hey thanks man for your quick reply...Will try it........
# 7  
Old 12-29-2008
Bug

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?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy txt file into excel vbscript

Hi everybody, I am working on Windows 10 and using cygwin. I have a vbscript as follows: Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set wb1 = objExcel.Workbooks.open("\\files\share\path\file1.xlsx") wb1.Worksheets("Sheet1").Range("A1:CR89").Clear Set... (0 Replies)
Discussion started by: supernono06
0 Replies

2. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

3. Shell Programming and Scripting

Data formatting in CSV file to EXCEL

Hello friends I want to convert an csv file on unix (which is generated by a ETL application) to a formatted excel sheet like .I have roughly like 28 columns 1)All numbers need to be stored as numbers with leading zeros-like format as text for this column to preserve leading zeroes e.g... (6 Replies)
Discussion started by: etldev
6 Replies

4. Windows & DOS: Issues & Discussions

How to copy a file with long records ,i.e spanning to 2 or 3 lines from UNIX to excel?

Hi Experts, I have a Unix csv file which has long records ie the record length is more than 80 so it goes to the next line.So when its in unix though it spans to two or three lines it still counts it as one record. But what is happening is for the records that are long when i copy it into excel i... (0 Replies)
Discussion started by: 100bees
0 Replies

5. Shell Programming and Scripting

Copy Data from CSV file to Excel Sheet using Perl

Hi All, Firstly I will like to wish A Happy New Year to all. Now my issue is I have one csv file say(data.csv) and one excel file say(result.xls) The result.xls contains two sheet name Sheet1 and Sheet2, Now What I am trying to do is to First I want to delete that data of Sheet2 if present any,... (6 Replies)
Discussion started by: adisky123
6 Replies

6. Shell Programming and Scripting

urgent<parsing data from a excel file>

Hi all, I wud like to get ur assistance in retrieving lines containing l1.My excel dataset contains around 8000 lines.I converted it into a text tab delimiter file and got the lines containing l1,My output is a list of lines containing l1 saved in a outfile.Some of d lines from my outfile s... (5 Replies)
Discussion started by: sayee
5 Replies

7. Shell Programming and Scripting

Sample ksh script for copy the data from excel to database table ?

Hi All, I need to convert the data from excel to database table in sybase. Please provide some sample script.. thanks, Royal. (1 Reply)
Discussion started by: royal9482
1 Replies

8. Shell Programming and Scripting

store the table data in excel file

Hello - I have a below table and i want to extract the data into excel sheet and send to different location. Here is the table structure... SQL> desc t_i1_exportdocs Name Null? Type ----------------------------------------- --------... (11 Replies)
Discussion started by: govindts
11 Replies

9. Shell Programming and Scripting

Copying data from excel file

Hii friends, I am a newbie to unix/shell scripting and got stuck in implementing a functionality.Dear experts,kindly spare some time to bring me out of dark pit :confused:.. My requirement is somewhat wierd,let me explain what i have and what i need to do... 1) there are several excel... (1 Reply)
Discussion started by: 5ahen
1 Replies

10. Shell Programming and Scripting

Help on email data file as excel from unix!!

Hi, I need to email a data in excel sheet from unix using shell scripting.I could able to generate the data file with tab delimiter with extension .xls could able to email it. The problem is when a coulmn with 16 digit number is exported, it is showing in scientific format. Any help in... (1 Reply)
Discussion started by: sparan_peddu
1 Replies
Login or Register to Ask a Question