missing comma delimeter in columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting missing comma delimeter in columns
# 1  
Old 02-27-2012
missing comma delimeter in columns

hi

if comma delimeter missing in columns treat them as bad file and if it is not then gudfiles. only checking columns not data.

id,name,sal,deptno =======> gudfile
1,awa,200,10
2,aba,100,20
3,cdc,300,30

idname,sal,deptno ========> badfile since its missing (.)
1,awa,200,10
2,aba,100,20
3,cdc,300,30

thanks.
# 2  
Old 02-27-2012
Do you know in advance how many fields to expect or do you need to read the first line of the data to get that detail?
if not
Code:
if [ "X$(head -1 file.dat | awk -F\, '{print $4}')" eq "X" ] ; then
   echo "Bad file"
else 
   echo "Good file"
fi

# 3  
Old 02-27-2012
This will check each record in inputfile and finally determine if good or bad based on whether number of columns are equal to 4 or not.
Code:
awk -F',' '{(NF==4)?(x=0):(x=1)} END{if(x==0){print "good"}else{print "bad"}}' inputfile

# 4  
Old 02-27-2012
thanks a lot for all your replies. i dont know how many columns will be in each file. just for example i mention 4 columns. i got the logic i will try using the above logic.

here is the code which i was trying for the data. now i have to check only for columns. how can i modified the same script to just for columns. here its checking each line and i have to check the line which contain columns of the file.
Code:
#!/bin/ksh
BASE_DIR=/data/SrcFilescd 
$BASE_DIR
## finding the files from work directory which are changed in 3 day
Find . -type f -name "*.csv" -ctime 3 > /home/mydir/flist.txt
## Loop thru all the file nameswhile read linedo
## get only the base name for the file
FN=`basename $line`
## the variable DC counts the number delimiters on each line and sort them and get the unique
## for good file without any delimiter missing the count should be one
DC=`awk -F "," '{print NF}' $FN | sort | uniq -c | wc -l `
## From the above we know that the good file always have DC equal one..
if [ $DC -ne 1 ]; then
echo $DC
echo $FN >> /home/mydir/badfile.txt
## also remove the bad files that are been corrupted here by removing comments ## rm $FN
else
echo $DC
echo $FN >> /home/mydir/gfile.txt
fi done < /home/mydir/flist.txt


Last edited by Scott; 02-27-2012 at 03:41 PM.. Reason: Please use code tags and less formatting
# 5  
Old 02-27-2012
Please use code tags. Codes/data samples would be easier to read.
# 6  
Old 02-27-2012
thanks a lot gurus

yeh its working
# 7  
Old 02-27-2012
This should work for any number of columns:
Code:
awk -F, 'NR==1{n=NF} n!=NF{b=1;exit} END{print b?"Bad":"Good"}' infile

Version with return code:
Code:
awk -F, 'NR==1{n=NF} n!=NF{b=1;exit} END{exit b}' infile

---------- Post updated at 20:31 ---------- Previous update was at 18:40 ----------

Multiple files:
Code:
awk -F, 'function pr(){print f ": " (b?"Bad":"Good")} FNR==1{if(NR>1)pr();n=NF;f=FILENAME;b=0} !b && n!=NF{b=1} END{pr()}' infile*


Last edited by Scrutinizer; 02-27-2012 at 01:49 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to change comma delimeter in the file to number?

I have a file H,20180624200732,VPAS,TRANS_HDR,20180724, VPAS.TRANS_HDR.20180724.01.txt, ,93, T,1, I have to change and instead first comma put ",1" like below H,20180624200732,VPAS,TRANS_HDR,20180724, VPAS.TRANS_HDR.20180724.01.txt,1,93, T,1, I made sed "2s/, /,1/"... (8 Replies)
Discussion started by: digioleg54
8 Replies

2. Shell Programming and Scripting

Awkscript to reduce words delimited with comma on right hand to columns

I have a large database with the following structure: Indicword,Indicword,Indicword=English on a line. Not all lines will have this structure. Some might have a single word mapping to a single word in Indic. An example will make this clear ... (4 Replies)
Discussion started by: gimley
4 Replies

3. Shell Programming and Scripting

Modify comma delimited file columns.

Please help me to update a file which contains date values as below:- From:- "1912108",20161130,"2016-12-01-00.00.00.000000","2016-12-01-08.37.12.000000" "1912108",20161201,"2016-12-02-00.00.00.000000","2016-12-02-08.28.22.000000" To:- "1912108",2016-11-30,"2016-12-01... (7 Replies)
Discussion started by: KrishnaVM
7 Replies

4. Shell Programming and Scripting

Transpose comma delimited data in rows to columns

Hello, I have a bilingual database with the following structure a,b,c=d,e,f The right half is in a Left to right script and the second is in a Right to left script as the examples below show What I need is to separate out the database such that the first word on the left hand matches the first... (4 Replies)
Discussion started by: gimley
4 Replies

5. Shell Programming and Scripting

Replacing the delimeter with other delimeter

Hi Friends, I have a file1.txt as below 29123973Ç2012-0529Ç35310124Ç000000000004762Ç00010Ç20Ç390ÇÇÇÇF 29123974Ç20120529Ç35310125Ç0000000000046770Ç00010Ç20Ç390ÇÇÇÇF 29123975Ç20120529Ç35310126Ç0000000000046804Ç00010Ç20Ç390ÇÇÇÇF 29123976Ç20120529Ç35310127Ç0000000000044820Ç00010Ç20Ç390ÇÇÇÇF i have a file2.txt... (4 Replies)
Discussion started by: i150371485
4 Replies

6. Shell Programming and Scripting

Needed value after the last delimeter in a file with varying number of delimited columns

Hi All, My file has the records as below: aaa\bbb\c\dd\ee\ff\gg zz\vv\ww pp\oo\ii\uu How can I get the value after the last delimeter. My o/p: gg ww uu Thanks in Advance, (5 Replies)
Discussion started by: HemaV
5 Replies

7. UNIX for Dummies Questions & Answers

sort comma separated lines by specific columns

Hello, I have a file which lines' words are comma separated: aa, bb, cc, uu b, ee, ff bb, cc, zz, ee, ss, kk oo, bb, hh, uu a, xx, ww tt, aa, dd, yy aa, gg I want to sort first by second column and in case of tie by fourth column with sort command. So the output would be: ... (4 Replies)
Discussion started by: asanchez
4 Replies

8. Shell Programming and Scripting

Count the delimeter from a file and delete the row if delimeter count doesnt match.

I have a file containing about 5 million rows, in the file there are some records which has extra delimiter at random position. (we dont know the positions), now we have to Count the delimeter from each row and if the count of delimeter is not matching then I want to delete those rows from the... (5 Replies)
Discussion started by: Akumar1
5 Replies

9. Shell Programming and Scripting

Filling in missing columns

Hi all, I have a file that contains about 1000 rows and 800 columns. Nearly every row has 800 columns but some DONT. I want to extend the rows that dont have values with NA's. Here is an example: my file bob 2 4 5 6 8 9 4 5 tar 2 4 5 4 3 2 9 1 bro 3 5 3 4 yar 2 ... (7 Replies)
Discussion started by: gisele_l
7 Replies

10. Shell Programming and Scripting

fill in missing columns

It can't be that hard, but I just can't figure it out: I have file like: File Sub-brick M_1 S_1 M_2 S_2 M_4 S_4 ... xxx 1 214 731 228 621 132 578 ... and would like to get 0 0 where M_3 S_3 is missing xxx 1 214 731 228 621 0 0 132 578 ... I wrote following script, but can't figure out... (3 Replies)
Discussion started by: avvk
3 Replies
Login or Register to Ask a Question