Title: multiple values in one cell in CSV files
Tags: csv;awk
Question: I have CSV files with data format like following :
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|SOCIAL|||0|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|EMAIL|||0|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|STREAMING|||0|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|WEB|||10180|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|IM_AND_VOIP|||0|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|DEFAULT|||0|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|TUNNELING|||0|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|FREETRAFFIC|||0|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
201212998474|201212998474|99|20140911092720|1410427640|Sy-module-1.PTS-DR1.mobinil.net;1410427618;0;151.154.11.224|2001|||0|||||mobinildpi|ALWAYS_ON|||0|DIAMETER_LOGOUT||0,0,0,5634026,|201212998474NonRoaming,201212998474_2190_MONTHLY_1GB_20LE,201212998474_7702_Unlimited_CAP,201212998474_7702_POST_FUP_USAGE,|10||T|1|||||
i need to extract the 4 values in the 22th column each and get the difference between the first reading and the last reading  for each of them  in the column  .
i am trying this code :
awk -F "|" '{
if (length(msisdn[$1] == 0)) {
msisdn[$1] = $6 ;
counter[$1] = $22 ; }
last[$1] = $22 ;
type[$1] = $23  ;
} END { for ( i in msisdn ) { print i " : " msisdn[i] " : " type[i] " : " (counter [i]-last[i]) ; }}' 201409* &gt; counters.txt
but it gives me the the desired out for the first value in the column only .
The expected output should something like this :
201274971010 : Sy-ptsd-3.module-3.PTS1-DRC.mobinil.net;1410748407;0;151.154.11.224 : 201274971010NonRoaming,201274971010_2190_MONTHLY_1GB_20LE, : 0 , 320
201202999478 : Sy-ptsd-3.module-8.PTS3-DRC.mobinil.net;1410753394;0;151.154.11.224 : 201202999478NonRoaming,201202999478_2190_MONTHLY_1GB_20LE, : 0 , 150
Comment: It is not very clear what you're trying to do here. Can you try to explain more clearly? How are the columns grouped? What is the significance of $6 and $23? Perhaps you could reduce the number of columns in your question, as it seems that many of them are unrelated to the problem.

Here is the accepted answer: The short (shortest!) answer is
awk -F\| '{
# split(source, destination, separator_re)
split($22, values, /,/);
print values[4]-values[1];
}'
I think that you can arrange for the rest of the output file requirements (that I can't understand)
Comment for this answer: ps: see the above comment by @Tom Fenech that explains my problems trying to understand your requirements...
Comment for this answer: Dear Mohamed, the problem in your explanation was that your problem IS a complicated problem, that you've solved on your own for 98%, and you left all that complication in your question.  If you make the effort (I understand that it is an effort) to drop all the complications and present a simpler problem it is much more likely to have useful answers!
Comment for this answer: Thanks for your answer it was helpful enough sorry if i am not clear enough in my explanation


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:10/12/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<csv><awk>', 'Title': 'multiple values in one cell in CSV files', 'ViewCount': 551, 'id': 26324460, 'user_AboutMe': None, 'user_AccountId': 4858356.0, 'user_DisplayName': 'Mohamed Yasser', 'user_Reputation': 45.0, 'user_Views': 15.0, 'user_id': 4046014.0}}