We have a requirement to count the valid number of records in a comma delimited file with double quotes.
The catch here is..few records have a new line carriage within the double quotes,,say for ex:we have a file called accounts the record count is 4827..but the actual valid count is 4823..1 header row and 1 field in a record has 3 lines of data.
So i'm having a tough time to count the exact count..we need to compare this count to the table count where we are loading this file to.
Please suggest..
1) cat file | grep "^[0-9][0-9]" | wc -l
Or similar, if the normal file has numbers in the first couple character positions as user id or other identifier
2) cat file | cut -c1 | grep '"'
Assuming that the first field begins with a "
3) cat sample | tr "\n" " " | tr '"' "\n" | wc -l
Will produce a number. If normally three fields with " for each valid record, then take the resulting number from the command and divide by (twice the normal # fields).
The command above showed me 12 for my sample file, and I knew my sample has three fields with ". So 12/(3*2) = 2 records
p.s.
Be cautious of the "bump" to move a question. I always scan questions for those with zero responses - to work on first. Then, time permitting, go back to others that I previously skipped over. Therefore, your "bump" can have the reverse effect.
Thanks Joey..
cat sample | tr "\n" " " | tr '"' "\n" | wc -l..seems to be working..
when I tried..found an issue..for one of the record the data has multiple double quotes ..("xyz","abc","toys ""R"" us", "def", "hij","....)..
coz of this I'm not getting a round value count..how do I handle this?
Quotes inside quoted field - yech.
Can you count comma's then, using similar logic to my 3rd option? Or, are there comma's inside the quotes also? That is quite possible.
Perhaps try the first or second options?
The 2nd will show how many lines begin with a " character - in theory, your continued lines would not begin with a quote character.
Beyond the counting... good luck trying to decipher the file!