Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to write text file data to excel using UNIX shell script?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to write text file data to excel using UNIX shell script?
# 1  
Old 06-22-2013
How to write text file data to excel using UNIX shell script?
Hi All,
I have the requirement in unix shell script.
I want to write the "ls -ltr" command out put to excel file as below.

Input :text file data :

Code:
drwxr-xr-x 5 root  root  4096 Oct  2 12:26
drwxr-xr-x 2 apx   aim   4096 Nov 29 18:40 
drwxr-xr-x 5 root  root  4096 Oct  2 12:26
drwxr-xr-x 2 apx   aim   4096 Nov 29 18:40
drwxr-xr-x 5 root  root  4096 Oct  2 12:26
drwxr-xr-x 2 apx   aim   4096 Nov 29 18:40

Output ; Excel file :

Code:
drwxr-xr-x|5|root|root|4096|Oct|2|12:26
drwxr-xr-x|2|apx|aim|4096|Nov|29|18:40
drwxr-xr-x|5|root|root|4096|Oct|2|12:26
drwxr-xr-x|2|apx|aim|4096|Nov|29|18:40
drwxr-xr-x|5|root|root|4096|Oct|2|12:26
drwxr-xr-x|2|apx|aim|4096|Nov|29|18:40

PS: I could not go for the perl script as I don't have perl in my server.
If any one can help me on this that would be great thanks :-)
Last edited by Scrutinizer; 06-22-2013 at 04:46 AM.. Reason: Removed image that did not work, added code tags
# 2  
Old 06-22-2013
Has been asked a zillion times in these fora. Did you use the search function? Anyhow - try
Code:
awk '{$1=$1}1' OFS="|" file

# 3  
Old 06-22-2013
I don't think you would even need an intermediate step for importing into excel.
# 4  
Old 06-23-2013
Thanks Rudic and scrutinizer for quick response.
Actually i want to export the each file creation date and time to a static files.i.e the existed excel file will have the data already and the date and time will add to the excel sheet.

Exceldata.xls

column1 column2 column3 column4 column5
EMEA root Oct 2 12:26
APAC Aim Nov 29 18:40

Column1 is static the rest of the columns are dynamic.

Please help me on this.

Thanks in advance!!!
# 5  
Old 06-23-2013
I don't understand. Please
- use code tags as required by forum rules
- show input (e.g. result of ls -l) and desired output
- describe the logics that connect the two
- show what you have tried so far
# 6  
Old 06-24-2013
Dear Rudic

I have tried below code :

Code:
ls -ltr > Inputdata.txt
awk '{print $6,$7,$8,$9}' OFS="|" Inputdata.txt > OutputObatained.xls

Input data : Inputdata.txt

Code:
total 0
drwxrwxrwx 1 pvs pvs 0 May 13 12:27 TestMurex
drwxrwxrwx 1 pvs pvs 0 May 13 12:29 20130614
drwxrwxrwx 1 pvs pvs 0 Jun 13 08:43 Markt3
drwxrwxrwx 1 pvs pvs 0 Jun 13 09:22 Markt2
drwxrwxrwx 1 pvs pvs 0 Jun 13 09:22 Markt1
drwxrwxrwx 1 pvs pvs 0 Jun 14 15:01 Markt
drwxrwxrwx 1 pvs pvs 0 Jun 24 10:59 ..
-rwxrwSrwx 1 pvs pvs 0 Jun 24 11:07 Inputdata.txt
drwxrwxrwx 1 pvs pvs 0 Jun 24 11:07 .

Output Obtained: OutputObtained.xls

Code:
Column1 
 
drwxrwxrwx 1 pvs pvs 0 May 13 12:27 TestMurex
drwxrwxrwx 1 pvs pvs 0 May 13 12:29 20130614
drwxrwxrwx 1 pvs pvs 0 Jun 13 08:43 Markt3
drwxrwxrwx 1 pvs pvs 0 Jun 13 09:22 Markt2
drwxrwxrwx 1 pvs pvs 0 Jun 13 09:22 Markt1
drwxrwxrwx 1 pvs pvs 0 Jun 14 15:01 Markt
drwxrwxrwx 1 pvs pvs 0 Jun 24 10:59 ..
-rwxrwSrwx 1 pvs pvs 0 Jun 24 11:07 Inputdata.txt
drwxrwxrwx 1 pvs pvs 0 Jun 24 11:07 .


Here i am getting the output in single column(column1)

Output required : OutputRequired.xls

Code:
Column1 Column2 Column3 Column4 Column5 Column6 
IND INR May 13 12:27 TestMurex 
IND INR May 13 12:29 20130614 
IND INR Jun 13 8:43 Markt3 
US USD Jun 13 9:22 Markt2 
EUR EUR Jun 13 9:22 Markt1 
EUR EUR Jun 14 15:01 Markt 
AUS AUD Jun 24 10:59 .. 
GBP GBP Jun 24 11:07 Inputdata.txt 
GBP GBP Jun 24 11:07 .

In my out put file already first two columns will have the data before script triggered,All i need is to place the script out put in 3rd,4th,5th columns in excel sheet.

Thanks in advance!!!
Last edited by Scrutinizer; 06-24-2013 at 01:12 PM..
# 7  
Old 06-24-2013
Code:
awk 'BEGIN{ OFS="|"; print "Column1|Column2|Column3|Column4|Column5|Column6"};
NR > 1{print "IND", "INR", $6, $7, $8, $9;}' Inputdata.txt > Output.xls

I am not sure with what basis the first two columns are changing!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to insert data into black column( Secound Column ) in excel (.XLSX) file using shell script?

Source Code of the original script is down below please run the script and try to solve this problem this is my data and I want it column wise 2019-03-20 13:00:00:000 2019-03-20 15:00:00:000 1 Operating System LAB 0 1 1 1 1 1 1 1 1 1 0 1 (5 Replies)
Discussion started by: Shubham1182
5 Replies

2. Shell Programming and Scripting

Read csv file, convert the data and make one text file in UNIX shell scripting

I have input data looks like this which is a part of a csv file 7,1265,76548,"0102:04" 8,1266,76545,"0112:04" I need to make the output data should look like this and the output data will be part of text file: 7|1265000 |7654899 |A| 8|12660000 |76545999 |B| The logic behind the... (6 Replies)
Discussion started by: RJG
6 Replies

3. Shell Programming and Scripting

Need help to write a shell script to convert text file to excel file.

Hi Everyone, I want your help to write a script which will take text file as input and on the basis of delimiter ":"script will create excel sheet. Example input: IpAdress:InstanceName:Port:ServerName 10.255.255.1:abc:2232:xyz_abc Output should be an excel sheet like below: Column... (8 Replies)
Discussion started by: akabhinav18
8 Replies

4. 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

5. Shell Programming and Scripting

Shell Script for copying text file to Excel Sheet

Hi, I want to write a program to copy a log file to Excel sheet. Excel sheet has four columns MethodName , Code , Description, Details and Time. I want to pick these info from text file and put it in excel sheet. here is how the text file looks - 04.17.2014 08:06:12,697... (1 Reply)
Discussion started by: hershey
1 Replies

6. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required. (1 Reply)
Discussion started by: 24ajay
1 Replies

7. Shell Programming and Scripting

Need help on inserting data from text file to excel using shell script

Hi, Please help me on this. I want to insert data from text file to excel using shell script nawk -v r=4 -v c=4 -v val=$a -F, 'BEGIN{OFS=","}; NR != r; NR == r {$c = val; print}' "file.csv" I used above one to insert $a value in 4th row, 4th column in an excel file.csv and it... (3 Replies)
Discussion started by: suman.frnz
3 Replies

8. Shell Programming and Scripting

a shell script to generate an excel sheet from a text file..

hi, i have a text file that looks like this! i want to generate an excel sheet out of it, removing all the junk data except the addresses that look like . Arrow Electrical Services Rotating Machinery, Electrical Contracting & Mining Specialists Onsite maintenance, breakdown... (8 Replies)
Discussion started by: vemkiran
8 Replies

9. UNIX for Advanced & Expert Users

put data in excel file using shell script

Hi. I wish to add data in a specific excel file on daily basis.However the currect dat's data should always come on top i.e for example should always occupy cell A7,B7,C7 .. and the data of day before which was earlier on 7th row of each coloumn should move to 8th row..data on 8th row should... (1 Reply)
Discussion started by: kanus
1 Replies

10. Shell Programming and Scripting

how to put data using shell script to a excel file

Hi, Can any one tell me how to put 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... (1 Reply)
Discussion started by: siri_886
1 Replies
Login or Register to Ask a Question