Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Validate csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Validate csv file
# 1  
Old 04-21-2016
Power Validate csv file
Hi guys,
i want to validate the no.of colums in a csv file ,but if there is a comma(,) in any of the data values it should skip and count only valid (,) commas.
e.g
Code:
1.abc,pqrs,1234,567,hhh

result :4
Code:
2.abc,pqrs,1234,567,hhh,"in,valid",end12

result:6
here script should skip the comma inside the{"in,valid"}.
please advice how to get this count?

thanks

Moderator's Comments:
Mod Comment Please use code tags as required by forum rules!
Last edited by RudiC; 04-21-2016 at 03:52 AM.. Reason: Added code tags.
# 2  
Old 04-21-2016
You said you wanted to count columns, but your sample output seems to be counting commas instead of columns. To count columns, you might try:
Code:
awk -F'"' '
{	nf = gsub(/,/, ",") + 1
	for(i = 2; i <= NF; i += 2)
		nf -= gsub(/,/, ",", $i)
	printf("%s\nresult:%d\n", $0, nf)
}' file

which, if file contains your two sample input lines, produces the output:
Code:
bc,pqrs,1234,567,hhh
result:5
2.abc,pqrs,1234,567,hhh,"in,valid",end12
result:7

This User Gave Thanks to Don Cragun For This Post:
harry123
# 3  
Old 04-22-2016
Thanks ! Don..it will also work..

---------- Post updated 04-22-16 at 01:25 AM ---------- Previous update was 04-21-16 at 07:38 AM ----------

Hi Don,
can i get total count of columns in out put result .
if a file has 2 rows and 19 colums.
then total colums is 19*2.

thanks
# 4  
Old 04-22-2016
Do you mean: The file consists of records which spread over two lines, and you want the element count per record?
# 5  
Old 04-22-2016
Hi RudiC,
Below code gives the out put as below
Code:
bc,pqrs,1234,567,hhh result:5 2.abc,pqrs,1234,567,hhh,"in,valid",end12 result:7

i want the total_count as 12(5+7) and so on ..if more records as there.



Moderator's Comments:
Mod Comment Please use code tags as required by forum rules!

To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.)



Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums
Last edited by RudiC; 04-22-2016 at 04:50 AM.. Reason: Added code tags (again)
# 6  
Old 04-22-2016
This is not the concept of "columns" (as to my understanding). Columns are repeating structures of records (or rows) in a (DB or spread sheet or ...) table or file. E.g. Col1 has "Last Name", Col2 has "First Name", Col3 "CustomerID", etc.

Do you want just the count of data fields/elements in a file? Then try an adaption of Don Cragun's proposal:
Code:
awk -F'"' '
        {nf += gsub(/,/, ",") + 1
         for(i = 2; i <= NF; i += 2)
         nf -= gsub(/,/, ",", $i)
        }
END     {printf("result:%d\n", nf)
        }
' file
result:12

This User Gave Thanks to RudiC For This Post:
harry123
# 7  
Old 04-22-2016
Your desired output format isn't clear, but maybe something like:
Code:
awk -F'"' '
{	nf = gsub(/,/, ",") + 1
	for(i = 2; i <= NF; i += 2)
		nf -= gsub(/,/, ",", $i)
	printf("%s\tresult:%d\n", $0, nf)
	tf += nf
}
END {	printf("Summary: fields:%d rows:%d average fields/row:%.2f\n",
		tf, NR, tf / NR)
}' OFS='"' file

producing the output:
Code:
bc,pqrs,1234,567,hhh	result:5
2.abc,pqrs,1234,567,hhh,"in,valid",end12	result:7
Summary: fields:12 rows:2 average fields/row:6.00

will give you what you want from your two sample input lines???
This User Gave Thanks to Don Cragun For This Post:
harry123
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script to validate header in a csv file

Hi All; I am struggling to write a script that validates file header. Header file would be like below with TAB separated TRX # TYPE REF # Source Piece Code Destination Piece Code every time I need to check the txt file if the header was same as above fields if validation success... (6 Replies)
Discussion started by: heye18
6 Replies

2. Shell Programming and Scripting

Script to ingest a csv, validate data and insert into Oracle

Hi all i would appreciate your help... I am looking for a set of unix commands which i can use to 1) ingest a csv file with a known format 2) validate the filename 3) validate the data/datatypes 4) Insert into an oracle db Can you help get me started? yogz888 (1 Reply)
Discussion started by: yogz888
1 Replies

3. Shell Programming and Scripting

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 Replies

4. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

5. Shell Programming and Scripting

How to: Validate a CSV file using an XSD?

Hi All, I was wondering if there is a utility/perl library/way of validating the contents of a CSV file using an XSD. i.e. Customer csv (including Header & Trailer) Cust_num, Cust_nme, Cust_typ, Cust_act_dte, Cust_loc, 101,Joe's Pizza,Retail,10121979,Detroit, 102,Sony... (6 Replies)
Discussion started by: luinzi
6 Replies

6. Shell Programming and Scripting

Validate the file

How do we validate the header file. The file number should increament by 1 (position 17 to 19) if not abend the process. first week ABC0001 20100101123 second week ABC0001 20100108124 Third week ABC0001 20100115125 (7 Replies)
Discussion started by: zooby
7 Replies

7. Shell Programming and Scripting

validate against a file

Hello all, I am having problem in writing a if condition for the following: I have a file Instance.dat which has: #Server Environment server1 dev server2 dev server3 sit #!/bin/ksh ENV=dev for i in $( cat Instances.dat | grep -v '#' |awk {'print $2'} ) do if ]... (7 Replies)
Discussion started by: chiru_h
7 Replies

8. Shell Programming and Scripting

How to validate a CSV file?

Hi. I think some people have already asked this, but the answers/questions seem to be about validating the contents inside a CSV file. I am simply after a simple variable solution (ie 0 = false, 1 = true) that I can use in my script to say that file so-and-so is actually a CSV file, or in some... (4 Replies)
Discussion started by: ElCaito
4 Replies

9. Shell Programming and Scripting

validate csv file load

Hi All, I am using sqlldr to load my csv files into the database. The code in the sh script is as follows. sqlldr ${DBUSER}/${DBPASS}@${ORACLE_SID} \ data=myCSV.data \ bad=myCSV.bad \ control=myCSV.ctl \ ... (0 Replies)
Discussion started by: rahulrathod
0 Replies
Login or Register to Ask a Question