Convert text file data into XL file format

UNIX for Beginners Q & A
1

Hi
i have a file containing below info and want it to put in xl format

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.

 
 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

Hello scriptor,

could you please try following and let me know if this helps you.

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.

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.

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

3

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

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

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

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

Hello scriptor,

Could you please go through the following explanation and let me know if this helps you.

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

1 Like
7

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.

 
 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

8

Try also

awk -vDT=$(date +"%d-%b-%Y") 'BEGIN {print "Date         stat1 stat2 stat3 stat4 stat5"} {print DT, $0; DT="           "}' file
Date         stat1 stat2 stat3 stat4 stat5
30-Jun-2017 2878042 455134 3333176 24.231979  23.81
            2880246 453022 3333268 24.141338  23.81
            2879677 453495 3333172 24.310986  23.81
9

Hello scriptor,

That is one of the way to change digits 01 to 12 to Jan,FEB...Dec , we could do with Rudi's code too.

Now coming on the troubleshooting part for code is not working, it is working fine for me, I am getting output when I am running it without mailx and when I am using mailx getting email too successfully. Could you please try following points.

A- Do following steps.
i- Check if any carriage characters present into your Input_file by following command.

cat -v Input_file

If you see ^M characters then you should go further.
ii- Remove carriage characters by running following commands.

awk '{gsub('\r/,"");print} Input_file > temp_file && mv temp_file Input_file

B- If option is A is NOT valid for you and your Input_file doesn't have carriage characters then you may check things like:

1- If your Input_file have any other delimiter rather than space?
2- Check if your Input_file is same as what you have posted in very first post or not?
3- Last but not the least, you haven't mentioned your O.S name so commands which me and Rudi have given will nicely work on *NIX but on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk .

Let me know how it goes then.

Thanks,
R. Singh