How to define the datamapper setting for CSV file with multi-records type

donaldng · February 20, 2019, 9:07am

I am trying to define a datamapper for the file with the following structure, but can’t get it work. Can someone give me a hand on it?

1000|1010739|1000|1010739-1|USD|20180503|1000|1010739|C1000|1010739|109|10||27|MR|Name-1 1000|1010739||Txxxxx|Sxxxxx|Street 1000|1010739|Flat 8B|1 and 1 Apartments|163 Hxxxxxx Road|Wxx Cxxx|||Hong Kong||||||||||5295-2737|s.txxxxx@gxxxx-mxxx.com|MALE|19710607|||||XX|HK|2|MARRIED||||2.M||||YES||NO|JPY|LT_3
2000|1010739|2000|1010739-1|20180503|2000|1010739-1.20180503|USD|0001.22|0200.95|||0.00|0002.17||||0|0002.17
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073901|Australian Dollar||||||AUD|USD|0.7421|||0.00|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073902|Canadian Dollar||||||CAD|USD|1.3289|||0.00|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073903|Euro||||||EUR|USD|1.1643|||0.00|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073904|Pound Sterling||||||GBP|USD|1.3230|||0.00|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073905|Hong Kong Dollar||||||HKD|USD|7.8474|||0.00|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073906|Japanese Yen||||||JPY|USD|109.4859|||0|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073907|New Zealand Dollar||||||NZD|USD|0.6892|||0.00|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073908|Singapore Dollar||||||SGD|USD|1.3635|||0.00|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.1|Cash Accounts|3000|1010739-1.1.1|Cash Accounts|0013000|101073909|US Dollar||||||USD|USD|1.0000|||0.00|||||0.00
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.4|Cash Accounts|3000|1010739-1.4.4|Settlement Account|0013000|101073955|Hong Kong Dollar||||||HKD|USD|7.8474|||0.00|||||0001.22
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.4|Cash Accounts|3000|1010739-1.4.4|Settlement Account|0013000|101073956|Japanese Yen||||||JPY|USD|109.4859|||0|||||0001.22
3000|1010739|3000|1010739-1|20180503|3000|1010739-1.4|Cash Accounts|3000|1010739-1.4.4|Settlement Account|0013000|101073959|US Dollar||||||USD|USD|1.0000|||0001.22|0001.22||||0001.22
4000|1010739|4000|1010739-1|20180503|4000|1010739-1.85|Insurance|4000|1010739-1.850.85|Insurance|MD1730408820|Pxxxxxxxxxx Sxxxx 10|||20171026|||USD|USD|1.0000|0008.00||0|0.00|0.00|||0.00
5000|1010739|5000|1010739-1|20180503|5000|1010739-1.50|Collective Investments|5000|1010739-1.522.50|Fixed Income Fund|0|Descript 000046-000 Descript 000046-000 Descript 000046-000 |IE0032590923|||33.1700|14.540000|USD|USD|1.0000|500.00|500.00|002.29|002.29|-17.71|-17.71||0200.95
5000|1010739|5000|1010739-1|20180503|5000|1010739-1.50|Collective Investments|5000|1010739-1.524.50|Equity Fund|0|Descript 000081-000 Descript 000081-000 |IE00B0JY6M65|||12.8890|55.757800|USD|USD|1.0000|050.00|050.00|008.66|008.66|-31.34|-31.34||0200.95
6000|1010739|6000|1010739-1|20180503|6000|1010739-1|USD|20171031|0|0
6000|1010739|6000|1010739-1|20180503|6000|1010739-1|USD|20171130|0005.82|0060.73
6000|1010739|6000|1010739-1|20180503|6000|1010739-1|USD|20171229|0|0006.77
6000|1010739|6000|1010739-1|20180503|6000|1010739-1|USD|20180131|0|0009.09
6000|1010739|6000|1010739-1|20180503|6000|1010739-1|USD|20180228|0|0030.71
6000|1010739|6000|1010739-1|20180503|6000|1010739-1|USD|20180329|0|0003.28
6000|1010739|6000|1010739-1|20180503|6000|1010739-1|USD|20180430|0|0002.17

Regards,
Donald

Sharne · February 20, 2019, 9:33am

Hi Donald,

That cannot be mapped as data type CSV. (CSV mapping requires each row of data to have the same amount of columns which this data does not have) You need to select the Text option when creating a new data mapper. Looking at the second value, 1010739, I assume that is an account number that is unique per record. In the Text viewer window in the data mapper select 1010739, in settings pane change the boundaries Trigger to On Text. The Operator to On Changes. Then go back up to “Cut on number of lines” and change its value to 1. You will now have each record on their own.

Regards,
S