Wanted best way to validate delimited file records Post: 302717285

Sponsored Content

Top Forums UNIX for Advanced & Expert Users Wanted best way to validate delimited file records Post 302717285 by Seshendranath on Thursday 18th of October 2012 12:27:52 AM

10-18-2012

Registered User

Wanted best way to validate delimited file records

actually i post about this issue before but many folkz miss-understood with my quesion,
We are checking for the delimited file records validation
Delimited file will have data like this:

Code:

Aaaa|sdfhxfgh|sdgjhxfgjh|sdgjsdg|sgdjsg|
Aaaa|sdfhxfgh|sdgjhxfgjh|sdgjsdg|sgdjsg|
Aaaa|sdfhxfgh|sdgjhxfgjh|sdgjsdg|sgdjsg|

So we are checking for where the files of records we got is having validating length or not
NOTE:the structer of the file will be present in the teradata db,we will fetch the structer of the file then we will validate
in the teradata configured table we wil get details about
column name,oder number will be order of column in tht table….it will be 1 2 3….like tht,length of the column
for ex. if we are checking for file of 3 columns then in the table we wil hav 3 columns of size varchar(5),so now every feild in the file should have lenght <=5,

script which i wrote:

Code:

#------------------------------------------
#  Reading through the file and checking for the column length
#----------------------------------------------------
                logNote "Reading through the temp file and and checking for the column length"
 
                while read col_nm col_order_num col_len
                do
                                typeset -i col_len
                                typeset -i col_len_good
 
                                col_len_good=`expr $col_len + 1`
 
                                logNote "col_nm : $col_nm"
                                logNote "col_order_num : $col_order_num"
                                logNote "col_len : $col_len"
                                logNote "col_len_good : $col_len_good"
 
                                awk 'BEGIN{col_ord='$col_order_num';col_l='$col_len'}{FS="|"}{if (length($col_ord) > col_l) print $0;}'  $Src_File >> $Src_File.bad
 
                                awk 'BEGIN{col_ord='$col_order_num';col_l='$col_len_good'}{FS="|"}{if (length($col_ord) < col_l) print $0;}'  $Src_File > $Src_File.temp
 
                                rm -f $Src_File
                                mv $Src_File.temp $Src_File
 
                done <$RPT_FILE
==============================================================

In the script col_nm, col_order_num ,col_len we will fetch from table
col_nm =column name
col_order_num =oder number will be order of column in tht table….it will be 1 2 3….like tht
col_len=length of the column

its working fine bi=ut we had performance issue.
can any come up with some better solution
mostly using with awk,awk array might be easy i guess
thankz in advance

Last edited by Franklin52; 10-23-2012 at 06:33 AM.. Reason: Please use code tags for data and code samples

Seshendranath

View Public Profile for Seshendranath

Find all posts by Seshendranath

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Converting Tab delimited file to Comma delimited file in Unix

Hi, Can anyone let me know on how to convert a Tab delimited file to Comma delimited file in Unix Thanks!!

2. Shell Programming and Scripting

Delete Duplicate records from a tilde delimited file

Hi All, I want to delete duplicate records from a tilde delimited file. Criteria is considering the first 2 fields, the combination of which has to be unique, below is a sample of records in the input file 1620000010338~2446694087~0~20061130220000~A00BCC1CT...

3. UNIX for Dummies Questions & Answers

Extract records by column value - file non-delimited

the data in my file is has no delimiters. it looks like this: H52082320024740010PH333200612290000930 0.0020080131 D5208232002474000120070306200703060580T1502 TT 1.00 H52082320029180003PH333200702150001 30 100.0020080205 D5208232002918000120070726200707260580T1502 ...

4. Shell Programming and Scripting

How to convert a space delimited file into a pipe delimited file using shellscript?

Hi All, I have space delimited file similar to the one as shown below.. I need to convert it as a pipe delimited, the values inside the pipe delimited file should be as highlighted... AA ATIU2345098809 009697 005374 BB ATIU2345097809 005445 006518 CC ATIU9685098809 003215 003571 DD...

5. Shell Programming and Scripting

how to Insert values in multiple lines(records) within a pipe delimited text file in specific cols

this is Korn shell unix. The scenario is I have a pipe delimited text file which needs to be customized. say for example,I have a pipe delimited text file with 15 columns(| delimited) and 200 rows. currently the 11th and 12th column has null values for all the records(there are other null columns...

6. Shell Programming and Scripting

Print records which do not have expected number of fields in a comma delimited file

Hi, I have a comma (,) delimited file, in which few fields are enclosed with in double quotes " ". I have to print the records in the file which donot have expected number of field with the line number. File1 ==== name,desgnation,doj,project #header#...

7. Shell Programming and Scripting

what is the better way to validate records in a file.

8. UNIX for Dummies Questions & Answers

How to convert a comma delimited string to records or lines of text?

Hi, I am not sure if I've posted this question before. Anyway, I previously asked about converting lines of text into a comma delimited string. Now I am needing to do the other way around ... :( :o Can anyone advise how is this possible? Example as below: Converting records/lines to...

9. Shell Programming and Scripting

Delimited records splitted into different lines

Hi I am using delimited sequence file. Delimter we are using is pipe .But for some of the records for one of the column the values are getting split into different lines as shown below "113"|"0155"|"2016-04-27 07:59:04"|"1930"|"TEST@TEST"|"2016-04-27 11:04:04.357000000"|"BO"|"Hard...

10. UNIX for Beginners Questions & Answers

Need help on an old post - How to convert a comma delimited string to records or lines of text?

Hi, Apologies in advance to the moderator if I am posting this the wrong way. I've searched and found the solution to an old post but as it is a very old post, I don't see an option to update it with additional question. The question I have is in relation to the following post: How to...