Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Convert text file data into XL file format

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Convert text file data into XL file format
# 1  
Old 06-29-2017
Convert text file data into XL file format
Hi
i have a file containing below info and want it to put in xl format
Code:
2878042 455134 3333176 24.231979  23.81
2880246 453022 3333268 24.141338  23.81
2879677 453495 3333172 24.310986  23.81

i want this data in XL file format and want that my linux system should send me that file on my mail.
expected output in xl file i need.

Code:
 
 Date         stat1 stat2 stat3 stat4 stat5
 28-jun-2017 2878042 455134 3333176 24.231979  23.81
              2880246 453022 3333268 24.141338  23.81
              2879677 453495 3333172 24.310986  23.81

Pease help me if this is possible.

regards,
scriptor
# 2  
Old 06-29-2017
Hello scriptor,

could you please try following and let me know if this helps you.
Code:
awk -v Date=$(date +%d) -v Month=$(date +%m) -v Year=$(date +%Y) 'BEGIN{print "Date         stat1 stat2 stat3 stat4 stat5";split("Jan,Feb,Mar,Apr,may,Jun,Jul,Aug,Sept,Oct,Nov,Dec", array,",");MON=array[sprintf("%d",Month)];VAL=Date"-"MON"-"Year} NR==1{print VAL,$0;next} {printf("%"length(VAL)+1"s%s\n","",$0)}'  Input_file  | mailx -s"test_email"  chumma@chumma.com

EDIT: Adding a non-one liner form of solution too here.
Code:
awk -v Date=$(date +%d) -v Month=$(date +%m) -v Year=$(date +%Y) 'BEGIN{
                                                                        print "Date         stat1 stat2 stat3 stat4 stat5";
                                                                        split("Jan,Feb,Mar,Apr,may,Jun,Jul,Aug,Sept,Oct,Nov,Dec", array,",");
                                                                        MON=array[sprintf("%d",Month)];
                                                                        VAL=Date"-"MON"-"Year
                                                                       }
                                                                  NR==1{
                                                                        print VAL,$0;
                                                                        next
                                                                       }
                                                                       {
                                                                        printf("%"length(VAL)+1"s%s\n","",$0)
                                                                       }
                                                                 '  Input_file | mailx -s"test_email" chumma@chumma.com

EDIT2: Adding a little more modified solution where no hard coded first heading.
Code:
awk -v Date=$(date +%d) -v Month=$(date +%m) -v Year=$(date +%Y) 'BEGIN{
                                                                        split("Jan,Feb,Mar,Apr,may,Jun,Jul,Aug,Sept,Oct,Nov,Dec", array,",");
                                                                        MON=array[sprintf("%d",Month)];
                                                                        VAL=Date"-"MON"-"Year
                                                                        printf("%s%"length(VAL)+1"s %s %s %s %s\n","Date","stat1","stat2","stat3","stat4","stat5");
                                                                       }
                                                                  NR==1{
                                                                        print VAL,$0;
                                                                        next
                                                                       }
                                                                       {
                                                                        printf("%"length(VAL)+1"s%s\n","",$0)
                                                                       }
                                                                 '  Input_file  | mailx -s"test_email"  chumma@chumma.com

Thanks,
R. Singh
Last edited by RavinderSingh13; 06-29-2017 at 09:38 AM.. Reason: Adding a non-one liner form of solution too successfully here.
# 3  
Old 06-29-2017
HI Ravi

I tried all 3 option which you have mentioned. but I didn't get any o/p.
also it did not give any error .

regards.
scriptor
# 4  
Old 06-29-2017
Quote:
Originally Posted by scriptor
HI Ravi
I tried all 3 option which you have mentioned. but I didn't get any o/p.
also it did not give any error .
regards.
scriptor
Hello scriptor,

You shouldn't see any output on screen, you should see an email on your outlook client email box. If you want to see output on standard screen then please remove the code from | mailx.... till end of the command and it will show you output on screen. Also check your email's junk box in case it is going to junk email too.


Thanks,
R. Singh
# 5  
Old 06-30-2017
Hi Ravi,

I have not even received any mail.

---------- Post updated at 04:27 PM ---------- Previous update was at 10:52 AM ----------

Hi Ravi

also if possible can you please explain me working of this command

Code:
awk -v Date=$(date +%d) -v Month=$(date +%m) -v Year=$(date +%Y) 'BEGIN{
                                                                        split("Jan,Feb,Mar,Apr,may,Jun,Jul,Aug,Sept,Oct,Nov,Dec", array,",");
                                                                        MON=array[sprintf("%d",Month)];
                                                                        VAL=Date"-"MON"-"Year

scriptor
# 6  
Old 06-30-2017
Hello scriptor,

Could you please go through the following explanation and let me know if this helps you.
Code:
awk -v Date=$(date +%d) -v Month=$(date +%m) -v Year=$(date +%Y) 'BEGIN{
#####creating variables named Date with current date, Month for current month and Year for current year value.
                                                                        split("Jan,Feb,Mar,Apr,may,Jun,Jul,Aug,Sept,Oct,Nov,Dec", array,",");
#####creating an array named array by split command which has all 12 months names which are separated by comma in it and their index is 1 to 12 digits.
                                                                        MON=array[sprintf("%d",Month)];
#####creating variable named MON which will have current months value in plain English rather than number in it.
                                                                        VAL=Date"-"MON"-"Year
#####creating variable VAL which will have date-month-year format value of complete date value in it.
                                                                        printf("%s%"length(VAL)+1"s %s %s %s %s\n","Date","stat1","stat2","stat3","stat4","stat5");
#####printing the heading here where after 1st column it will take the variable VAL length and print those many spaces between 2nd column and 1st column here.
                                                                       }
                                                                  NR==1{
#####Checking condition if line number is 1 here.
                                                                        print VAL,$0;
#####printing the variable VAL and current line here.
                                                                        next
#####using next keyword of awk here which will skip all further statements.
                                                                       }
                                                                       {
                                                                        printf("%"length(VAL)+1"s%s\n","",$0)
#####printing the value of VAL variable with spaces and then print the current line.
                                                                       }
                                                                 '  Input_file  | mailx -s"test_email"  chumma@chumma.com
####Mentioning Input_file here and sending this command output to mailx command to send email to desired id.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
scriptor
# 7  
Old 06-30-2017
thx a lot Ravi for explaining the command.
one thing I am not able to understand is
why do we need to take an array of month. why we need to define it.
Code:
 
 split("Jan,Feb,Mar,Apr,may,Jun,Jul,Aug,Sept,Oct,Nov,Dec", array,",");

also one more thing I an not getting mail while running the command.

once again thx a lot for explaining the command

scriptor
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sort data in text file in particular format

I have to sort below output in text file in unix bash 20170308 DA,I,113 20170308 PM,I,123 20170308 DA,U,22 20170308 PM,U,123 20170309 DA,I,11 20170309 PM,I,23 20170309 DA,U,123 20170309 PM,U,233 (8 Replies)
Discussion started by: Adfire
8 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

Convert text file to HTML tabular format.

Please provide script/commands to convert text file to HTML tabular format. No need of styles and colours, just output and a heading in table is required. Output file will be send via email and will be seen from outlook. (script required without using awk). output file content: (sar... (7 Replies)
Discussion started by: Veera_V
7 Replies

4. Shell Programming and Scripting

Need a unix script to convert date into Julian format in a text file

The 6th & 7th column of the text files represents date & time. I need this to be converted in julian format using command "date +%s -d <date>". I know the command, but dont know how to use it on the script 0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup... (4 Replies)
Discussion started by: ajiwww
4 Replies

5. UNIX for Dummies Questions & Answers

How to convert a text file into tab delimited format?

I have a text file that made using text editor in Ubuntu. However the text file is not being recognized as space or tab delimited, the formatting seems to be messed up. How can I convert the text file into tab delimited format? (3 Replies)
Discussion started by: evelibertine
3 Replies

6. Programming

awk script to convert a text file into csv format

hi...... thanks for allowing me to start a discussion i am collecting usb usage details of all users and convert it into csv files so that i can export it into some database.. the input text file is as follows:- USB History Dump by nabiy (c)2008 (1) --- Kingston DataTraveler 130 USB... (2 Replies)
Discussion started by: certteam
2 Replies

7. Shell Programming and Scripting

Convert comma text file to Column in html format

I am trying to generate a report with below file : File1 : EQADM,edrtere9-phys,8122caef0,gpatmon,/bin/ksh,nuten Erick EQADM,edrtere11-phys,8227caef0,gpatmon,/bin/ksh,nuten Erick EQADM,edrtere3-phys,822caef0,gpatmon,/bin/ksh,nuten Erick can you help me convert it to html and add... (9 Replies)
Discussion started by: sriram003
9 Replies

8. UNIX for Dummies Questions & Answers

To convert multi format file to a readable ascii format

Hi I have a file which has ascii , binary, binary decimal coded,decimal & hexadecimal data with lot of special characters (like öƒ.ƒ.„İİ¡Š·œƒ.„İİ¡Š· ) in it. I want to standardize the file into ASCII format & later use that as source . Can any one suggest a way a logic to convert such... (5 Replies)
Discussion started by: gaur.deepti
5 Replies

9. UNIX for Advanced & Expert Users

Convert UTF8 Format file to ANSI format

:) Hi i am trying to convert a file which is in UTF8 format to ANSI format i tried to use the function ICONV but it is throwing error Function i used it as $ iconv -f UTF8 -t ANSI filename Error iam getting is NOT Supported UTF8 to ANSI please some help me out on this.........Let me... (1 Reply)
Discussion started by: rajreddy
1 Replies

10. UNIX for Dummies Questions & Answers

write data into a text file in bold format

Hi, can anyone help to write data into a text file in bold format and rollback to actual format. Thanks, Regards, Milton Y. (1 Reply)
Discussion started by: miltony
1 Replies
Login or Register to Ask a Question