How to validate a CSV file?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to validate a CSV file?
# 1  
Old 02-24-2006
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 other format. Is this possible?

I guess the tricky part is defining precisely what a CSV file is. I've seen "CSV" files with commas, but the fields are enclosed in double quotes. I've seen others without double quotes and just commas as the delimiter. I am dealing with the later type.

Thanks in advance.
# 2  
Old 02-24-2006
Assumes that if each line of the file has the same number of comma-delimited fields, then the file is a csv file.

Code:
awk 'BEGIN{FS=","}!n{n=NF}n!=NF{failed=1;exit}END{exit !failed}'

# 3  
Old 02-26-2006
Quote:
Originally Posted by futurelet
Assumes that if each line of the file has the same number of comma-delimited fields, then the file is a csv file.

Code:
awk 'BEGIN{FS=","}!n{n=NF}n!=NF{failed=1;exit}END{exit !failed}'

Yes. You are correct, but please can you explain this code to me, and how it works? I don't see where I can place the filename that I need to check in this bit of code.
What I am after is some sort of variable statement, to store either YES or NO (1 or 0) to say that the CSV file given in the command is a true CSV file.
Your assumption about the same number of commas per line is correct, and that is good enough for me.
Could I use the above code like this ...
Code:
set iscsv = awk 'BEGIN{FS=","}!n{n=NF}n!=NF{failed=1;exit}END{exit !failed}'

??

or?
# 4  
Old 02-27-2006
Perhaps
Code:
set iscsv = `awk ' BEGIN{FS=","}!n{n=NF}n!=NF{failed=1;exit}END{print !failed}' file.csv`

# 5  
Old 02-27-2006
Thanks for your help. It's pointed me in the right direction.

Here is what I found and it works fine for me, where $file is a variable that holds the full file name I want to use. In my case I know each file has to have 84 commas per line.

Code:
  CSVcnt=`awk 'BEGIN{FS=","}END{print NF}' $file`
  if [[ $CSVcnt != 84 ]] ; then
    echo "ERR03 - FPOS file $file is NOT a CSV file!"
  fi

I tried to place a single line solution using your suggested script, but I gave up trying to get it to work.

Cheers
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. UNIX for Beginners Questions & Answers

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 1.abc,pqrs,1234,567,hhh result :4 2.abc,pqrs,1234,567,hhh,"in,valid",end12 result:6 here script should skip the comma inside... (10 Replies)
Discussion started by: harry123
10 Replies

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

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