Reading CSV file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Reading CSV file
# 1  
Old 12-29-2017
Reading CSV file

Hi experts,

Im having csv file with few columns which should contain data as shown below. Want to check if column 3 contain row with duplicate value(9876,9876) then corresponding to this in col2 should contain text "tax" and should not contain text "non".
Word "non" can come but if in column3 duplicate value are not present in rows, if a row contain only distinct value suppose 7623 then corresponding to this "non" can come in column 2.

Need to print error along with line number, if these condition are not met in file or col2 contain "non" against col3 contain duplicate value

CSV Data:

Code:
col1         col2   col3
inv	       tax	9876
inv	       tax	9876
inv	       non	7623
inv           tax    1234
inv           tax    1234

Bad Data :
will be considered incorrect if last row with col2 value as "non" and col 3 value as 1234
Code:
col1         col2   col3
inv	       tax	9876
inv	       tax	9876
inv	       non	7623
inv           tax    1234
inv           tax    1234
inv           non    1234


Last edited by Scrutinizer; 12-29-2017 at 05:36 AM.. Reason: code tags
# 2  
Old 12-29-2017
Not fully understanding what you're after, how about
Code:
awk '
                {++CNT[$3]
                }

$2 == "non"     {NON[$3] = NR
                }

NON[$3] &&
(CNT[$3] > 1)   {print "error ", NON[$3]
                }
' file
error  7

# 3  
Old 12-30-2017
Hi Rudic,

With reference to above query, below is the objective i want to achieve looking into csv file.

Identify the rows with field(column-1) as INV and then on filtered list identify rows with identical values in the column-2 (document Number). In all such cases, check the value in field supplyType (column-3) and in that if any of the row has value TAX then in none of the other row should have value NON.

For ex-
If column three contain duplicate values then against those values in column2 we can have values as "tax","SEZ", "ISD", etc but not value as "non". If value in column 3 does not contain duplicate value(only single or unqiue value) in rows, then column 2 can contain value "non" against those distinct(single) value of colum3.

sample data :
Code:
col1         col2   col3
inv	       tax	9876
inv	       tax	9876
inv	       non	7623
inv           tax    1234
inv           tax    1234
inv           non    1234

here the last bundle of duplicate values of "1234"(column3) will be considered as wrong because in column 2 we are getting "non" against column 3(last row value 1234).
Instead it has to be "tax" at last row of column2.

For this error we need to print "error code" with line number.

---------- Post updated at 12:38 AM ---------- Previous update was at 12:14 AM ----------

Hi Rudic,

Thanks for the code,
will execute the same and will let you know for issues if any.
thank you once again Smilie

Last edited by Don Cragun; 12-30-2017 at 01:20 AM.. Reason: Add CODE tags again.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

1. This will insert the records into db table by reading from ta csv file

I have this code with me but the condition is If any of the mandatory columns are null then entire file will be rejected. LOAD DATA infile ' ' #specifies the name of a datafile containing data that you want to load BADFILE ' ' #specifies the name of... (1 Reply)
Discussion started by: raka123
1 Replies

2. Shell Programming and Scripting

Reading the value of particular column from csv file

Hi Folks, I have the below csv file which is comma delimited , now from this file i need to read the value of the column der_id and then want to create a separate text file which will contain the value of the column der_id only please advise how to read the value of the column der_id and then... (3 Replies)
Discussion started by: punpun66
3 Replies

3. Shell Programming and Scripting

Reading last line of a CSV file

Hi I have a file which I am reading line by line and processing it. But the last line is not getting read in the file loop until I put an enter in the end. #!/bin/ksh -p v_org_id=${P1} FILE=${P2} NEW_FILE_NAME=$APPLPTMP/b1.txt BAKIFS=$IFS IFS=$'\n' exec 0<"$FILE" echo "File to be... (2 Replies)
Discussion started by: Chinky23
2 Replies

4. Shell Programming and Scripting

Reading a csv file using shell script

Hello All, I have a csv file that looks like below ProdId_A,3.3.3,some text,some/text,sometext_1.2.3 ProdId_B,3.3.3,some text,some/text,sometext_1.2.3 ProdId_C,3.3.3,some text,some/text,sometext_1.2.3 ProdId_A,6.6.6,some text,some/text,sometext_9.9.9 I will get ProdId from... (5 Replies)
Discussion started by: anand.shah
5 Replies

5. Shell Programming and Scripting

Reading from a CSV and writing in same CSV file

Hi, I am tryng to read from a csv file and based on some grep command output I will modify one of the column in the same csv. Example:- Input CSV:- 20120829001415,noneAA,google.com 20120829001415,dfsafds,google.com 20120829001415,noneAA,google.com Intermediate Step:- If 2nd column of... (3 Replies)
Discussion started by: kmajumder
3 Replies

6. Shell Programming and Scripting

reading a csv file and creating a flat file

hi i have written a script for reading a csv file and creating a flat file, suggest if this script can be optimized #---------------- FILENAME="$1" SCRIPT=$(basename $0) #-----------------------------------------// function usage { echo "\nUSAGE: $THIS_SCRIPT file_to_process\n"... (3 Replies)
Discussion started by: mprakasheee
3 Replies

7. Shell Programming and Scripting

Reading variables from CSV file

Hi I am using KSH and trying to read variables from a csv file. I've set the IFS=, and it workds. Problem is where one of the values is text containing a comma. For example the following lines exist in my file. How can I read everything between the quotes into a single variable? APW13812,,1... (2 Replies)
Discussion started by: ventris
2 Replies

8. UNIX for Advanced & Expert Users

Issue reading csv file

HI All I have csv file containing the data like this Electrical Equipment,ElecEquip "Engineering, Machinery & Equipment",Engineerin Entertainment & Broadcasting,Entertain The first and third record are fine,The issue with second records as it has comma enclosed with in inverted... (1 Reply)
Discussion started by: mohdtausifsh
1 Replies

9. Shell Programming and Scripting

reading from a .csv file

Hi , Can anyone please help me to read the value from the .csv file? This is my .csv file: dirnames: first,second i want to get the two names and create directories correspondingly (1 Reply)
Discussion started by: novice_user
1 Replies

10. Shell Programming and Scripting

Script for reading .csv file

Can someone please help me to write script for following scenario : 1> script should read a input .csv file of format : EmpName, PF, Leave, Basic ,HRA 2> another config file ( may be again a .csv file ) has format EmpName and EmpID 3> script should read another config file for each EmpName in... (3 Replies)
Discussion started by: creativeworld
3 Replies
Login or Register to Ask a Question