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

Shell Programming and Scripting
1

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 :

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 :

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 :slight_smile:

2

Has been asked a zillion times in these forums. Did you use the search function? Anyhow - try

awk '{$1=$1}1' OFS="|" file
3

I don't think you would even need an intermediate step for importing into excel.

4

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

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

Dear Rudic

I have tried below code :

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

Input data : Inputdata.txt

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

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

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!!!

7 
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!

8

What be so difficult in using code tags as required?

If I interpret correctly what you say, those country/currency entries already exist in output.xls. Be aware that correlating file entries based on just line numbers may be dangerous as a missing line will corrupt your results. Anyhow, try sth like (untested):

awk 'NR==FNR {TMP[NR]=$6"|"$7"|"$8"|"$9; next} FNR==1 {print "Col1|Col2|Col3|Col4|Col5|Col6";next} {print $1"|"$2"|"TMP[NR]} output.xls Inputdata.txt

Edit: Improved above untested version:

awk     'FNR==NR        {TMP[FNR]=$6"|"$7"|"$8"|"$9; next}
         FNR==1         {print "Col1|Col2|Col3|Col4|Col5|Col6";next}
                        {print $1"|"$2"|"TMP[FNR]}
        ' InputData.txt output.xls
Col1|Col2|Col3|Col4|Col5|Col6
IND|INR|May|13|12:27|TestMurex
IND|INR|May|13|12:29|20130614
IND|INR|Jun|13|08:43|Markt3
US|USD|Jun|13|09:22|Markt2
EUR|EUR|Jun|13|09: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|.
9

Thanks for your quick response Pikk

But her the out put is going in to single column in excel,i need each word should place in each cell of the excel say in A1 cell Column1 and B1 column2 .....etc and in A2 cell IND,B2 cell INR ...etc.

Please help me !!!

10

Try the delimiter as TAB. that should help you!

11

One option is to just copy-paste the ls -ltr output into excel, it gets pasted into a single column (with a selection band around it). Without doing any else, just go straight to the menu and choose data -> text to columns -> delimited -> and select space and tab as separator and select treat consecutive delimiters as one and then choose next and finish. If there are no spaces in your file names then the ls output should land nicely in seperate columns.