Saturday, May 2, 2015

Shell script example for dissecting a column in .csv file

#!/bin/bash
#echo "Please enter input  file in  csv file format: "
echo "File you entered is: $1"
inputfile=$1;
echo $inputfile
awk -F "," '{print $4}' $inputfile >> list.unsorted
sort list.unsorted | uniq -c | column -ts $'\t'| grep -R 'XXX*' > sortedlist.sed
awk 'BEGIN{printf("%-8s%-30s%-10s\n","#","Column1 Name","Column 2 Name")}{printf("%-8s%-30s%-10s\n",NR,$1,$2)}' sortedlist.sed > $inputfile "Final_Report.txt"
rm -r list.unsorted  sortedlist.sed

  • My string is present in 4th column so i am printing 4th column using awk
  • Printf() is very nice function to look into if you want to customize the spaces between the different columns.
consider the following example,in this example i am looking for the following pattern to check how many times this pattern has occurred.

since i was only looking for XXX* only it showed all the matching strings.this string input to grep can be any string of your choice.

Example Pattern for the above script.
XXX-YYYY-ZZ-AABBCCDD.1234


Output of the above script

#       No.of Times                   My Pattern
1       6                             XXX-YYYY-ZZ-AABBCCDD.1234

How to convert xlsx files to csv using libreoffice headless command.

First if you want to convert the xlsx file to csv file,it is possible using libreoffice.
First check if libreoffice is installed on your machine.
To check that type "libreoffice" on console.

If a prompt comes up,then it is evident that libre office is installed.





we don't need to have to open libreoffice calc for converting the xlsx to csv.
we can do this using the headless mode,using the following command.
format :--
libreoffice --headless --convert-to output_file_extension output_filter_name --outdir output_dir
command example
libreoffice --headless --convert-to csv filename.xlsx --outdir .
Here the filename.xlsx is your xlsx to be converted to csv.

Note:- If the above command does not work in the normal mode use sudo,
or add following line to your sudors file.

users ALL=(ALL) NOPASSWD: libreoffice
Now see the .xlsx is converted to csv and is present in the current directory.
Now xlsx is converted to csv,dissecting a column in csv is one usecase.
and search for a matching string is one more usecase.
and find the unique occurances of that string is another usecase.