Merge multiple records with same key into one large rec(csv)



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Merge multiple records with same key into one large rec(csv)

Postby Indy » Fri Aug 17, 2018 3:50 pm

Here is a situation and I just can't get my head around on how we could address this and wondered if any of you have some ideas on how to go about this.

I have a file where there are multiple records for the same key value (Key value is 2,33) and what we need is creating a single record out of the many records, ideally a pipe delimited file.

Example Input File Attached,
Each record is prefixed with an identifier
- for ‘O’ record, we need to extract data from 35,50, so should be ‘ORNGE-ACCOUNT_RECENT_TRANSACTIONS_EMAIL ‘
- for ‘S’ record, we need to extract data from 35,50, so should be ‘test01@test.com ‘
- for ‘D’ record, we need to extract data from 45,160, so should be ‘090000XXXXXXXX‘, ‘161886’, ‘GBP’ and so on
Above example is from the first chunk of records from the input file. A new section starts with a 'O' record

First record in Output file from the first n records from the input file (before we encounter the second 'O' as record prefix) should look like,
F111111111|001122343001987654|ORNGE-ACC ... 1@test.com|090000XXXXXXXX|161886|GBP|161886|GBP|2018-08-15|PURCHASE01|-4999|GBP|2018-08-14|PURCHASE02|25000|GBP|2018-08-14|PURCHASE03|-5775|GBP|2018-08-14|PURCHASE04|-13044|GBP|2018-08-14|PURCHASE05|900|GBP|MR INDRANIL DAS|00|

This needs to be done through some utilities (preferably SORT / ICETOOL).

sample data ( inlined and BBcoded )

OF111111111001122343001987654EMAILORNGE-ACCOUNT_RECENT_TRANSACTIONS_EMAIL
SF111111111001122343001987654EMAILtest01@test.com
DF111111111001122343001987654EMAILCUENTALOCA090000XXXXXXXX
DF111111111001122343001987654EMAILSALDOCUENT161886
DF111111111001122343001987654EMAILDIVSALDO  GBP
DF111111111001122343001987654EMAILSALDOLIMDE161886
DF111111111001122343001987654EMAILDIVLIMDES GBP
DF111111111001122343001987654EMAILFECHA     2018-08-15
DF111111111001122343001987654EMAILDESCRIPCIOPURCHASE01
DF111111111001122343001987654EMAILIMPORTE   -4999
DF111111111001122343001987654EMAILDIVMVTO   GBP
DF111111111001122343001987654EMAILFECHA     2018-08-14
DF111111111001122343001987654EMAILDESCRIPCIOPURCHASE02
DF111111111001122343001987654EMAILIMPORTE   25000
DF111111111001122343001987654EMAILDIVMVTO   GBP
DF111111111001122343001987654EMAILFECHA     2018-08-14
DF111111111001122343001987654EMAILDESCRIPCIOPURCHASE03
DF111111111001122343001987654EMAILIMPORTE   -5775
DF111111111001122343001987654EMAILDIVMVTO   GBP
DF111111111001122343001987654EMAILFECHA     2018-08-14
DF111111111001122343001987654EMAILDESCRIPCIOPURCHASE04
DF111111111001122343001987654EMAILIMPORTE   -13044
DF111111111001122343001987654EMAILDIVMVTO   GBP
DF111111111001122343001987654EMAILFECHA     2018-08-14
DF111111111001122343001987654EMAILDESCRIPCIOPURCHASE05
DF111111111001122343001987654EMAILIMPORTE   900
DF111111111001122343001987654EMAILDIVMVTO   GBP
DF111111111001122343001987654EMAILNOMBRECOMPMR INDRANIL DAS
DF111111111001122343001987654EMAILBRAND_TYPE00
OF222222222003322133011896754EMAILORNGE-ACCOUNT_RECENT_TRANSACTIONS_EMAIL
SF222222222003322133011896754EMAILtest02@test.com
DF222222222003322133011896754EMAILCUENTALOCA090000YYYYYYYY
DF222222222003322133011896754EMAILSALDOCUENT24871
DF222222222003322133011896754EMAILDIVSALDO  GBP
DF222222222003322133011896754EMAILSALDOLIMDE16856
DF222222222003322133011896754EMAILDIVLIMDES GBP
DF222222222003322133011896754EMAILFECHA     2018-08-16
DF222222222003322133011896754EMAILDESCRIPCIOPURCHASE0A
DF222222222003322133011896754EMAILIMPORTE   -915
DF222222222003322133011896754EMAILDIVMVTO   GBP
DF222222222003322133011896754EMAILFECHA     2018-08-16
DF222222222003322133011896754EMAILDESCRIPCIOPURCHASE0B
DF222222222003322133011896754EMAILIMPORTE   -294
DF222222222003322133011896754EMAILDIVMVTO   GBP
DF222222222003322133011896754EMAILFECHA     2018-08-16
DF222222222003322133011896754EMAILDESCRIPCIOPURCHASE0C
DF222222222003322133011896754EMAILIMPORTE   -898
DF222222222003322133011896754EMAILDIVMVTO   GBP
DF222222222003322133011896754EMAILFECHA     2018-08-15
DF222222222003322133011896754EMAILDESCRIPCIOPURCHASE0D
DF222222222003322133011896754EMAILIMPORTE   -1400
DF222222222003322133011896754EMAILDIVMVTO   GBP
DF222222222003322133011896754EMAILFECHA     2018-08-15
DF222222222003322133011896754EMAILDESCRIPCIOPURCHASE0E
DF222222222003322133011896754EMAILIMPORTE   -800
DF222222222003322133011896754EMAILDIVMVTO   GBP
DF222222222003322133011896754EMAILNOMBRECOMPMR ASHOKE KUMAR DAS
DF222222222003322133011896754EMAILBRAND_TYPE00
OF333333333009921323021677421EMAILORNGE-ACCOUNT_RECENT_TRANSACTIONS_EMAIL
SF333333333009921323021677421EMAILtest03@test.com
DF333333333009921323021677421EMAILCUENTALOCA090000ZZZZZZZZ
DF333333333009921323021677421EMAILSALDOCUENT2220262
DF333333333009921323021677421EMAILDIVSALDO  GBP
DF333333333009921323021677421EMAILSALDOLIMDE2220262
DF333333333009921323021677421EMAILDIVLIMDES GBP
DF333333333009921323021677421EMAILFECHA     2018-08-15
DF333333333009921323021677421EMAILDESCRIPCIOPURCHASE001
DF333333333009921323021677421EMAILIMPORTE   -3298
DF333333333009921323021677421EMAILDIVMVTO   GBP
DF333333333009921323021677421EMAILFECHA     2018-08-14
DF333333333009921323021677421EMAILDESCRIPCIOPURCHASE002
DF333333333009921323021677421EMAILIMPORTE   2500
DF333333333009921323021677421EMAILDIVMVTO   GBP
DF333333333009921323021677421EMAILFECHA     2018-08-10
DF333333333009921323021677421EMAILDESCRIPCIOPURCHASE003
DF333333333009921323021677421EMAILIMPORTE   -1000
DF333333333009921323021677421EMAILDIVMVTO   GBP
DF333333333009921323021677421EMAILFECHA     2018-08-10
DF333333333009921323021677421EMAILDESCRIPCIOPURCHASE004
DF333333333009921323021677421EMAILIMPORTE   -1000
DF333333333009921323021677421EMAILDIVMVTO   GBP
DF333333333009921323021677421EMAILFECHA     2018-08-07
DF333333333009921323021677421EMAILDESCRIPCIOPURCHASE005
DF333333333009921323021677421EMAILIMPORTE   2500
DF333333333009921323021677421EMAILDIVMVTO   GBP
DF333333333009921323021677421EMAILNOMBRECOMPMRS SOHINI DAS
DF333333333009921323021677421EMAILBRAND_TYPE00

 
Indy
 
Posts: 1
Joined: Fri Aug 17, 2018 3:39 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Merge multiple records with same key into one large rec(csv)

 

Re: Merge multiple records with same key into one large rec(

Postby enrico-sorichetti » Fri Aug 17, 2018 6:14 pm

do not post attachments ...
for sample data it is enough to put it in the post text using the code tags
DONE IT FOR YOU
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
Indy (Fri Aug 17, 2018 6:46 pm)
enrico-sorichetti
Global moderator
 
Posts: 2790
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 148 times


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post