Shell Programming and Scripting

Hi all it is a real challenge for me to do it in 2 days any help or suggestion will be a great !
Problem : I have 17 - CSV files (Coma separated value)
each file conating around 26 column
the first line of each of the file conatin the field name and from second line
the file contain data for each of column heading.

for each of the file there are different mandatory column
like for BASIC_DATA there is mandatory column 1,2,3,4,5,6,10,11
this column must be having data for each of the rows if any of the columns don't have value then this file should be requested to resend .

Here is sample file structure of one of those file

'MBRASH','RMMG1-MTART','RMMG1-MATNR','MAKT-MAKTX','MARA-MEINS','MARA-MAKTL','MARA-BISMT','MARA-LABOR','MARA-KOSCH','MARA-PRDHA','MARA-MTPOS_MARA','MARA-EAN11','MARA-NUMTP','MARA-PRZUS','MARA-FERTH','MARA-NORMT','MARA-FORMT','MARA-WRKST','MARA-PROFL','MARA_KZUMW','MARA_IHIVI','MARA_ILOOS','MARA-ZEINR','MARA_SATNR','MARA_KZKFG'
Chemical,ZMAT-SFM,B12366497,PVAT66 BULK STRGE,KG,CHEM,,,,G2,NORM,,,,,,,,,,,,,,
Chemical,ZMAT-SFM,B12366404,PVA 7130 BULK RC,KG,CHEM,,,,G2,NORM,,,,,,,,,,,,,,
Chemical,ZMAT-SFM,B12366520,VAC BULK STRGE,KG,CHEM,,,,G2,NORM,,,,,,,,,,,,,,
Chemical,ZMAT-FPM,D12766771,ELVANOL T66 22.7KG / 50 LB BAG,KG,CHEM,,,,G2,NORM,,,,,,,,,,,,,,
Chemical,ZMAT-RM,D10270736,ACETIC ACID GLACIAL PLN,KG,CHEM,,,,G2,NORM,,,,,,,,,,,,,,
Chemical,ZMAT-RM,D10000905,METHYL METHACRYLATE 99.5% TKT,KG,CHEM,,,,G2,NORM,,,,,,,,,,,,,,
Chemical,ZMAT-RM,D10057585,DOW CORNING H-10 DEFOAMER DRM,KG,CHEM,,,,G2,NORM,,,,,,,,,,,,,,



The first row actually contain the column heading and the second row onward it contain data for it.

I want to find out if colume 1 ,column2 ,column 3 ,column 4, column 5,column 6 , column 10 ,column 11 has data for each of the row ??
if any row misses data for the mandatory column then
i should mail the owner that data missing.


Please need the help.
I want to write a function for this where i can pass the file name and the mandatory column for this file

someone will help you soon with shell script,meanwhile, here's an alternative Python solution, am just bored.

Code:

import smtplib,glob
smtpserver = 'localhost' #define smtp server
fromaddr = "yourname@yoursite.org"
toaddr = "someone@somesite.org"
server = smtplib.SMTP(smtpserver) #instantiate
for files in glob.glob("*.csv"): #do all 17 csv files
	for num, lines in enumerate(open(files)):
 		lines = lines.strip() #strip new lines
 		splitted = lines.split(",") 
 		mandatory = splitted[0:6] + splitted[9:11] #get mandatory
  		if '' in mandatory: 
 			message = "Line number %d missing value in file %s" %(num, files)        
	        	server.sendmail(fromaddr, toadds, message)
server.quit() #close server

this might help you

Code:

awk -F"," -v man_col="1,2,3,4,5,6,10,11" '
BEGIN { n = split( man_col,man_col_arr,",") }
{
        for( i = 1 ; i <= n ; ++i )
                if ( $man_col_arr[i] == "" )
                {
                        print NR
                        close(FILENAME)
                }
}' file

Anbu,

Can you please explain your script?

Thanks

if you are in windows platform and u have MS excel then i can suggest u something

open excel file-> open the csv file with excel-> then in excel go to -> data->filter->autofilter

then u can go and select the non-blanks from 1,2,3,4,5,6,10,11....if the count is different then there is something missing....


but this will only work f ur csv is small ....i.e not more than 5 mb....or lese u have to resolve it by script....i am working on it...will post later

Code:

n = split( man_col,man_col_arr,",")

breaks man_col into pieces stored in successive elements of array man_col_arr, where the pieces lie between substrings matched by the regular expression ","

The function return value is the number of elements in the array man_col_arr

i/p : man_col="1,3,5"
o/p :
man_col_arr[1]=1
man_col_arr[2]=3
man_col_arr[3]=5

Code:

 if ( $man_col_arr[i] == "" )

here we are checking the mandatory field is empty.
If yes then print record number and then close the file.

i think i have a very simple soution....

Code:

$ cat file | cut -f 25 -d',' | grep -v '^.' | wc -l

for checking every field just change the no. "25" to whatever col. no. u want to check (1,2,3,4,5,6,10,11) and then note the value. if it is non - zero then something must be missing....simple right???

do u really want to save the labour of changing the file name and the no ("25")....then a basic script can be written to automate the above command....do let us know..if it worked