Record count of a csv file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Record count of a csv file
# 1  
Old 07-17-2008
Error Record count of a csv file

Hello Gurus,

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..

Thanks
AJ
# 2  
Old 07-17-2008
~bump~
# 3  
Old 07-17-2008
Hammer & Screwdriver Some random thoughts

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.
# 4  
Old 07-17-2008
Quote:
Originally Posted by joeyg
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?
# 5  
Old 07-17-2008
Hammer & Screwdriver Can you count comma's?

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!
# 6  
Old 07-17-2008
Quote:
Originally Posted by joeyg
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!
Second option worked..Thanks alot Joey!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Total record count of all the file present in a directory

Hi All , We need one help on the below requirement.We have multiple pipe delimited .txt file(around 100 .txt files) present on one directory.We need the total record count of all the files present in that directory without header.File format as below : ... (8 Replies)
Discussion started by: STCET22
8 Replies

2. Shell Programming and Scripting

Shell script for field wise record count for different Files .csv files

Hi, Very good wishes to all! Please help to provide the shell script for generating the record counts in filed wise from the .csv file My question: Source file: Field1 Field2 Field3 abc 12f sLm 1234 hjd 12d Hyd 34 Chn My target file should generate the .csv file with the... (14 Replies)
Discussion started by: Kirands
14 Replies

3. Shell Programming and Scripting

Output first unique record in csv file

Hi, I have to output a new csv file from an input csv file with first unique value in the first column. input csv file color product id status green 102 pass yellow 201 hold yellow 202 keep green 101 ok green 103 hold yellow 203 ... (5 Replies)
Discussion started by: Chris LAU
5 Replies

4. Shell Programming and Scripting

Print record count of a file using shell script

HI, I need to print the record count of a file using shell script. If the record count of a file excluding header and trailer record if greater than zero then print 'Record count of a file is xxxx records'. If the record count is zero print 'zero records' Thanks Mahendra (1 Reply)
Discussion started by: mmeda
1 Replies

5. Shell Programming and Scripting

csv file - adding total to a trailer record

Hi, I have a script which creates and modifies a csv file. I have managed to do everything I need to do apart from 1 thing. I need to append a trailer record to the file. I need this line to hold the total of an entire column of the csv file (skipping the 1st line which is a header). Can... (2 Replies)
Discussion started by: mcclunyboy
2 Replies

6. Shell Programming and Scripting

Split long record into csv file

Hi I receive a mainframe file which has very long records (1100 chars) with no field delimiters. I need to parse each record and output a comma delimited (csv) file. The record layout is fixed. If there weren't so many fields and records I would read the file into Excel, as a "fixed width"... (10 Replies)
Discussion started by: wvdeijk
10 Replies

7. Shell Programming and Scripting

replaying a record count with another record count

i use unix command to take the record count for a file1 awk 'END{print NR}' filename i already have a file2 which conatin the count like ... .. rec_cnt=100 .. .. I want to replace the record in the file2 using the record i take from file1. suggest me some simple ways of doing it... (2 Replies)
Discussion started by: er_zeeshan05
2 Replies

8. UNIX for Dummies Questions & Answers

how to get a file name & record count of csv file

HI , I am new to shell scripting , I have a requirement that I send a file for data quality ( original.csv) & i will be getting 4 files daily into a particular directory in return with cleansed data . the files may be clean.csv, unclean.csv , ... (2 Replies)
Discussion started by: sirik
2 Replies

9. UNIX for Dummies Questions & Answers

How to count the record count in an EBCDIC file.

How do I get the record count in an EBCDIC file on a Linux Box. :confused: (1 Reply)
Discussion started by: oracle8
1 Replies

10. UNIX for Dummies Questions & Answers

How to delete a record from a csv file

Hi Guys I have downloaded a table from oracle database in .csv format. it has many fields as Title, First Name, Last Name etc. I have to download distinct titles from database and now i have to check all those titles from data of First Name one by one. and then i have to delete matched record.... (1 Reply)
Discussion started by: Rajeev Agrawal
1 Replies
Login or Register to Ask a Question