LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 12-07-2011, 01:56 AM   #1
sukhdip
Member
 
Registered: Sep 2011
Posts: 54

Rep: Reputation: Disabled
Exclamation validating mandatory column value in csv file using script


Hi as m new to shell scripting..so dn't know much.
i need to learn that how unix script will find a empty filed in csv files and print the name of csv file on screen which is having empty field.
Scenario:
Suppose many *.csv files are in a directory.
Script has to go through all the csv files n check if all the field contains some value. i.e., they are not NULL. like two commas are not coming together. i.e., ",," or ", ,".
then script print the corresponding names on screen which csv files having empty field.

can anyone help me out this with script. its a bit difficult task for a newbie like me.

looking forward from you guys..
thanks,
 
Old 12-07-2011, 04:55 AM   #2
devUnix
Member
 
Registered: Oct 2010
Location: Bengaluru, India
Distribution: RHEL 5.1 on My PC, & SunOS / Sun Solaris, RHEL, SuSe, Debian, FreeBSD and other Linux flavors @ Work
Posts: 584

Rep: Reputation: 59
grep and common regex

Sukhdip,


How are you doing? Here are two example CSV files based upon your requirement:

cat my.csv
Code:
Mr. X,USA,IT,+1######
Mr. Y,UK,,09090##
Mrs. Chatter-Box,,,99090909
Miss Hotty,India,,90909
Miss Rubbish,,HR,
Miss Sweet,China,HR,
cat my_1.csv

Code:
Mr. Me,India,IT,+911111111111
Mr. X,USA,IT,+1######
Mr. Y,UK,,09090##
Mrs. Chatter-Box,,,99090909
Miss Hotty,India,,90909
Miss Rubbish,,HR,
You see above that there are 4 Fields in total:

Code:
Name,Country,Department,Contact
Some have last field's values missing hence "," in the end of them.

Some have some other fields' values missing hence ",," and ",,," embedded in those records.

Now, let's list out those invalid records:

Code:
grep -E '(,,|,$)' *.csv 
my_1.csv:Mr. Y,UK,,09090##
my_1.csv:Mrs. Chatter-Box,,,99090909
my_1.csv:Miss Hotty,India,,90909
my_1.csv:Miss Rubbish,,HR,
my.csv:Mr. Y,UK,,09090##
my.csv:Mrs. Chatter-Box,,,99090909
my.csv:Miss Hotty,India,,90909
my.csv:Miss Rubbish,,HR,
my.csv:Miss Sweet,China,HR,
To suppress the Invalid Records and display only the file names that have them, do this:

Code:
grep -lE '(,,|,$)' *.csv 
my_1.csv
my.csv
It is that much easy!

You can use "awk" utility also. But the "grep" above works fine and efficiently.


This extra piece of information is for your curiosity only:

Code:
grep -vhE '(,,|,$)' *.csv 
Mr. Me,India,IT,+911111111111
Mr. X,USA,IT,+1######
Mr. X,USA,IT,+1######
Can you figure out what the above output shows? Yes, it displays those records that are VALID (have all the values required to process them) and the file names have been suppressed this time.

Cheers!

Last edited by devUnix; 12-07-2011 at 04:59 AM.
 
1 members found this post helpful.
Old 01-31-2012, 02:45 AM   #3
sukhdip
Member
 
Registered: Sep 2011
Posts: 54

Original Poster
Rep: Reputation: Disabled
thanks a lot @ devUnix
learnt a lot from this ...
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to redirect the output to a different column in .csv file Sayan Acharjee Linux - General 2 04-25-2011 11:43 AM
Rearrange the column in CSV file.. govi1234 Linux - Newbie 9 08-27-2010 07:15 PM
Shell Script for CSV file comparision aravind_balan Programming 1 02-17-2009 03:33 AM
[SOLVED] Need help create a bash script to edit CSV File imkornhulio Programming 13 02-05-2009 10:23 AM
Shell script to read from csv file hendemeg Programming 1 05-11-2004 08:23 PM


All times are GMT -5. The time now is 05:53 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration