I have a data file which has a certain number of columns say 101. It has one description column which contains foreign characters and due to this some times, those special characters are translated to new line character and resulting in failing the process.
I am using the following awk command to indentify the number of columns
cat File name | awk -F "|" '{print NF}' | sort -u
If the file has foreign characters then it will display some thing like this if I run the above command.
101
102
I have written a small code to compare the counts of previous line with the next line and displays the line no. I have not done extensive unix scripting but the performance is not good as it takes for ever.
#!/usr/bin/ksh
filename=$1
count=0
prev_count=0
lineno=1
while IFS= read line
do
count=`echo $line| awk -F "|" '{print NF}'`
if [ $count -ne $prev_count ]
then
echo $prev_count
echo $count
echo $line_no
echo "previous count is not matching"
fi
prev_count=$count
line_no=`expr $line_no + 1`
done < $filename
Is there a better way to identify the issue record and replace the foreign character with space or remove it all together.
When the above bad record is read by abinitio etl tool, it gets new line character and the record gets scrambled and will shifted to next column and process fails.
Any help or quick command will be helpful. Thanks.
Thanks for your suggestion. After searching for other threads in this forum, I have modified the code as follows. It identifies the problem records which have greater than 101 columns.
awk -F "|" 'NF > 101 {print NR,$0}' <filename>
But to fix it is still a manual step. I am getting the output as shown below.
I don't understand your request. The red pipe char is the normal ASCII 0X7C used as a field separator in your file. Do you imply that one is an artefact? Did you try the gsub from post#1?