Shell Programming and Scripting

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:

Code:

Field1 Field2 Field3
abc 12f sLm
1234 hjd 12d
Hyd 34
Chn

My target file should generate the .csv file with the record count on field wise as below:


Target File:

Code:

Field1 Field2 Field3
4 3 2

Field1 has 4 values and field2 has 3 values and field3 has two values so the target file should be as below.

My source file can changes with more columns or less, so the target file should also have the same number of columns with the record count. Please help to resolve this..

Regards,
Kiran.

Hello Kirands,

Please use code tags for code/Input/commands which you are using into you posts as per forum rules. Following may help you in same.

Code:

awk 'NR==1{print;next} {for(i=1;i<=NF;i++){array[i]++}} END{for(j in array){A=A?A "\t" array[j]:array[j]};print A}'   Input_file

Output will be as follows.

Code:

Field1 Field2 Field3
4       3       2

Thanks,
R. Singh

Hi Ravinder,

Thank you so much for your response.
The data is looking good when i run the code given by you.. can you please help to put that data in the .csv file with the comma separator.

Regards,
Kiran.

Hello Kirands,

Sorry forgot to mention that previously, following may help you in same.

Code:

awk 'NR==1{OFS=",";for(i=1;i<=NF;i++){Q=Q?Q OFS $i:$i}print Q;next} {for(i=1;i<=NF;i++){array[i]++}} END{for(j in array){A=A?A OFS array[j]:array[j]};print A}' OFS=,  Input_file > Output.csv

Thanks,
R. Singh

Hi Ravinder,

Thank you for your prompt response.

I have tested the code given by you, but its still have some issues in giving the correct record count.

Please find the attached source file that has the data and the two headers., the record count should be from the 3rd row.
Please help to resolve this issue.

Thanks for your support.

Regards,
Kiran.

Hello Kirands,

I am sorry, I couldn't open the .rar file. According to your post you need to leave first two lines then let's say we have following Input_file:

Code:

Field1 Field2 Field3
XYZ ABC DEF
abc 12f sLm
1234 hjd 12d
Hyd 34
Chn

Then a little change in my previous post code as follows.

Code:

awk 'NR==1 || NR==2{OFS=",";for(i=1;i<=NF;i++){Q=Q?Q OFS $i:$i}print Q;Q="";next} {for(i=1;i<=NF;i++){array[i]++}} END{for(j in array){A=A?A OFS array[j]:array[j]};print A}' OFS=,  Input_file > output.csv

Let me know if you have any queries here.

Thanks,
R. Singh

Hi Ravindra,

Thank you for your response.

I have executed the latest code send by you :

Code:

awk 'NR==1 || NR==2{OFS=",";for(i=1;i<=NF;i++){Q=Q?Q OFS $i:$i}print Q;Q="";next} {for(i=1;i<=NF;i++){array[i]++}} END{for(j in array){A=A?A OFS array[j]:array[j]};print A}' OFS=,  RecountCountTest.csv> example_4.csv

The output data is giving correct for first 4 columns and the last 5 columns are not giving the correct record count, below are the source file and the target file(after i executed the above code) for your reference:

Source file:
First two are the headers and should not be in the count working good:




Target file after i executed the code provided by you:



for the columns NICKNAME count should be 0

Please help to resolve this issue, thanks for all your support.

Regards,
Kiran.

---------- Post updated at 07:52 AM ---------- Previous update was at 07:49 AM ----------

Hi Ravinder,

Here is the source data as per my previous reply if you did not able to see the data:

Source data:

STATUS USERID USERNAME FIRSTNAME NICKNAME MI LASTNAME SUFFIX TITLE
STATUS USERID Username FirstName Nickname MiddleName LastName Suffix Title
ACTIVE 80001001 itria1 Carlos Guillermo Itria
ACTIVE 80001002 lafournere1 Jean-Paul
ACTIVE 80001003 Wade Peter
ACTIVE 80001004 Olivier

Target Data: (after i executed the code provided by you

STATUS USERID USERNAME FIRSTNAME NICKNAME MI LASTNAME SUFFIX TITLE
STATUS USERID Username FirstName Nickname MiddleName LastName Suffix Title
4 4 2 4 4 4


Regards,
Kiran.