Hi All,

Hope you are doing good. I'm new to shell scripting. So don’t know much.
I need to learn that how unix script to validate csv files and print which file and row having issues in 10000 records.

Scenario:
Suppose many *.csv files are in a directory.
Each CSV file having multiple data types and has 10000 rows with comma separated semi structured data.
Script has to go through all the csv files and check if all the field with comma separated or not. Example – “, ,“(NULL) OR “” (Without comma) OR “|” etc..
then script print the corresponding names on screen which csv files and row having invalid format.

Can anyone help me out this with script. it’s a bit difficult task for a newbie like me.

looking forward from you guys..

Thanks
Sivaraj
Email: sivaraj111raajjz@gmail.com

I'd look at perl with Text::CSV from CPAN.

Are you trying to normalize the data format or just flag lines with incorrect syntax?

Hi,

Thanks for responding. The agenda is to avoid processing incorrect syntax file and reason for incorrect syntax so that we can give justification to business why we are unable to process specific files.

Thanks
Sivaraj

Can you post a few sanitized lines of typical errors you are looking to spot?

Please wrap them in [code] [/code] tags so that they are easier to read.

Hi Turbocapitalist,

Thanks. Main agenda is to find incorrect file having incorrect syntax.

Possbile spots are below

1) Space/null ex: [], ,[/]
2) Pipe between string ex: []|[/]
3) New line ex: []
[/]

Thanks
Sivaraj Khamithkar

Right. Please show two or three examples, with any sensitive content replaced with xxxxxx or something.

sorry for not providing proper information. please find the examples.

1) "EXCEPTION","XXXXX","08/18/2016 09:10:25", ,-1,"SYSTEM"|"Internal software event.","Get parameter PID contror for 'sdfds_GROUP_10' is set to open_loop:0 is set to closed_loop:140",0,"-","-","-","-","-","-","-","-","-","-","-","-","-","-",XXXXX,"XXXXX","VDN [937637728]","?.?",116," ","00000000;00000000;00000000;00000000;00000000;00000000;00000000;00000000;00000000;00000000","Autom atic","00000-00000000-m0000-00","END"

Null/Space --> "08/18/2016 09:10:25", ,-1,
Pipe symbol between --> "SYSTEM"|"Internal software event.",

2) "EXCEPTION","XXXX","08/18/2016 09:10:35",490588,-1,"USER","XXXX","Test run finished.","Test Run Results for sfadsfdsf (DGSS Droplet Stability Test)
Test Status: Finished
Measurement Quality: OK
Result Validation: In Limits
Machine Constants: N.A.",0,"-",END

New line between comma's.
,"Test Run Results for sfadsfdsf (DGSS Droplet Stability Test)
Test Status: Finished
Measurement Quality: OK
Result Validation: In Limits
Machine Constants: N.A.",


Thanks

Null/Space --> "08/18/2016 09:10:25", ,-1,
Pipe symbol between --> "SYSTEM"|"Internal software event.",

The "null space" is simply an empty field in the CSV.
The pipe is inside a field, too. The field contains: ["SYSTEM"|"Internal software event."]
Field by field processing shouldn't have a problem with either data.
What are you doing with the file that an empty field or an embedded pipe causes a problem? I'd think "fixing" those things would cause more problems, since the number of fields wouldn't match whatever your process is expecting.

Ah yes, fields with embedded newlines...I remember those. If you were to open the file in your favorite spreadsheet program, that field would show up as a multi-line entry. The challenge (as you probably know) is that when reading the csv file, most processes would assume that the first newline is the end of the line of data.
The file needs to be pre-processed to replace line feeds with spaces only when found between commas (or quotes, since your alpha fields are all quoted). I'll need to search for the regular expression to do that (I've got it somewhere). Maybe someone else has one handy. I'm going to be out of pocket for awhile. Sorry.

Hi,

Ok thanks. Actually, I'm working in testing area. Our client ask is to validate csv file and provide incorrect syntax csv list to them so that they can avoid injecting data to database.

Please help with script. Thanks!

Thanks
Sivaraj

For more information on awk and CSV data see https://www.gnu.org/software/gawk/ma...ing-By-Content