Today (Saturday) We will make some minor tuning adjustments to MySQL.

You may experience 2 up to 10 seconds "glitch time" when we restart MySQL. We expect to make these adjustments around 1AM Eastern Daylight Saving Time (EDT) US.


How to validate a CSV file?


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
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  
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  
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  
Perhaps
Code:
set iscsv = `awk ' BEGIN{FS=","}!n{n=NF}n!=NF{failed=1;exit}END{print !failed}' file.csv`

# 5  
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 Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Script to validate header in a csv file
heye18
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...... Shell Programming and Scripting
6
Shell Programming and Scripting
Validate csv file
harry123
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...... UNIX for Beginners Questions & Answers
10
UNIX for Beginners Questions & Answers
Script to ingest a csv, validate data and insert into Oracle
yogz888
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... Shell Programming and Scripting
1
Shell Programming and Scripting
How to: Validate a CSV file using an XSD?
luinzi
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...... Shell Programming and Scripting
6
Shell Programming and Scripting
validate csv file load
rahulrathod
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 \ ...... Shell Programming and Scripting
0
Shell Programming and Scripting

Featured Tech Videos