Rearange fields within a csv

Shell Programming and Scripting
1

Hello with sed and tr I have converted my "quite big" cronlist into the below csv file
Here is a sample:

05,15,15,25,35,45,55;*;*;*;*;/SCRIPT1;/SCRIPT1.log 
10;6,16;*;*;*;/SCRIPT2;/SCRIPT2.log 
30;*;*;*;*;/SCRIPT3;/SCRIPT3.cfg;/SCRIPT3.log  
50;8;*;*;*;/SCRIPT4;-1;/SCRIPT4.log 
30;5;*;*;*;/SCRIPT5;0;-1;/SCRIPT5.log

Unfortunatelly some scripts run with parameters, and some with configuration files
I need to find a way to add extra ;s so I can properly inport the columns into excel
Like:

05,15,15,25,35,45,55;*;*;*;*;/SCRIPT1;;;;/SCRIPT1.log 
10;6,16;*;*;*;/SCRIPT2;;;;/SCRIPT2.log 
30;*;*;*;*;/SCRIPT3;/SCRIPT3.cfg;;;/SCRIPT3.log  
50;8;*;*;*;/SCRIPT4;-1;;;;/SCRIPT4.log 
30;5;*;*;*;/SCRIPT5;;0;-1;/SCRIPT5.log

What will be the best way to implement this?
I am thinking storing into array with awk.
Any recommendatins ?
Thank you

2

One wonders why, initially, but I'm sure there is a reason. The file would have to be imported into Excel as a delimited text file if you are using ; as the separator.

It looks like you want the format of the time controls first, followed by the first script and then up to four more ; separated fields, kind of right-justified, which seems a little odd. Left justified would be easier:-

crontab -l|while read mins hours day_of_mon month day_of_week f1 f2 f3 f4 f5
do
   echo "${mins};${hours};${day_of_mon month};${day_of_week};${f1};${f2};${f3};${f4};${f5}"
done > crontab.txt

To get right justified, you would need to adjust the values of f2 to f5, so:-

crontab -l|while read mins hours day_of_mon month day_of_week f1 f2 f3 f4 f5
do
   if [ "$f2" != "" ]
   then
      while [ "$f5" = "" ]
      do
         f5="${f4}"
         f4="${f3}"
         f3="${f2}"
         f2=""
      done
   fi
   echo "${mins};${hours};${day_of_mon month};${day_of_week};${f1};${f2};${f3};${f4};${f5}"
done > crontab.txt

That should shuffle items (except the first script) to the right.

Does this acheive the results you are after? If not, please write back and we'll have another look.

Robin
Liverpool/Blackburn
UK

1 Like
3

I want to build

mins;hours;day_of_mon;month;day_of_week;script;conf_file;param1;param2;logfile

but some lines do not use parameters or conf files, so the output will be:

mins;hours;day_of_mon;month;day_of_week;script;;;;logfile
4

So, how do you know which records have them and which do not?
In either case, the second chunk of code should do it. I'm still wondering why you need this, but......

If you are not getting the output you need, can you paste in the output of crontab -l and then the output you would want for a sample of the lines.

Cheers,
Robin

1 Like
5

Maybe I've missed the point here, but if you want to import your crontab into Excel, why not just do it with the raw file using white space as the delimiter?
Each crontab line has 6 entries - minute, hour, day, month, day of week, command, so each will appear in a different column. Commands will be split across columns 6 to last, so you can process those at will in the spreadsheet

1 Like
6

Sample from my cron:

00 05  *  *  * (. ~/.profile ; timex /Data/bin/load_data.sh /Data/bin/load.cfg ) >> /Data/logs/load_data.log 2>&1
00 07 *  *  * (. ~/.profile ; timex /Data/bin/process -1 0 )   >> /Data/logs/processs.log 2>&1
#30 15,00 *  *  3,6     (. ~/.profile ; timex /Data/bin/analyze ) >> /Data/logs/analyze.log 2>&1

As you can see some script use cfg files and some use parameters.
I need to have ;; if no parameter is specified so it will load in the right column

7

A correction required because of a typo in the second script offerrin:-

For the output statement, it should read

   echo "${mins};${hours};${day_of_mon};${month};${day_of_week};${f1};${f2};${f3};${f4};${f5}"

I had missed the };${ out between day_of_month and month and it threw a wobbler. :o

Correcting it and running against your sample data gave me:-

00;05;*;*;*;(.;~/.profile;;;timex;/Data/bin/load_data.sh /Data/bin/load.cfg ) >> /Data/logs/load_data.log 2>&1
00;07;*;*;*;(.;~/.profile;;;timex;/Data/bin/process -1 0 )   >> /Data/logs/processs.log 2>&1
#30;15,00;*;*;3,6;(.;~/.profile;;;timex;/Data/bin/analyze ) >> /Data/logs/analyze.log 2>&1

Is that near to what is required? It looks rather messy to me, probably because the input has lots of extra spaces in it. If this represents all of the input from crontab -l then you will need to recode my suggestion to cater for that. It is picking up the date bit okay, but the next "field" I'm calling f1 is selected as just (. then f2 gets .profile and f3 is ; which makes no sense.

Robin