Splitting a file based on the payload field - multimapping

[Former Member]

HI Everyone,

I have a requirement of splitting a file based on the field .

e.g When I am a file as :

row1 David US

row2 Cindra US

row3 Peeru CA

row4 Jay CA

Then, I have to split the file into two files, one file with the US rows and another file with the CA rows.

There can be many countries in the input file, so number of target files need to be generated is not fixed.

I have gone through the below links :

/people/jin.shin/blog/2006/02/07/multi-mapping-without-bpm--yes-it146s-possible ( in the blog we know that there are two receivers but in my case I dont know that )

https://bond.newellco.com/irj/scn/,DanaInfo=www.sdn.sap.com,SSL+thread?messageID=6449801#6449801

( Everyone is providing JAVA mapping as a solution....)

Is Java mapping the only option to resolve the problem case I have ?

Thx

PEERU IN

[pedro_baroni3 · Accepted solution]

You can use multiple target message.

See thread:

In Mapping, will necessary use context functions for separate target messages.

Regards

[Former Member · Accepted solution]

Hello Pedro,

The blog is having an example with two different receiving structure but in my case the structure of the messages are same and the number of target messages are based on the input file.

I dont think this will be helpful in my case. Anyway, thanks for taking time to answer to my forum question.

I am still awaiting for the answer to my case.

THANKS in advance.

[Former Member · Accepted solution]

HI Peeru

You need to do two things

1. Go to message mapping tag messages and change the target message occurrence to 0.. unbounded. Now you will get Messages Message1.

2. You need to map row and Message1.

This will generate message equivalent to number of rows. Now use the receiver determination to route messages based on US and CA.

Ex - if US and CA both occur twice you get 4 messages generated. You need to check this using Xpath in conditional receiver determination and route it. You will get output as 2 US and 2 CA.

Thanks

Gaurav

[Former Member · Accepted solution]

Thanks for your response Gaurav.

I have tried changing the occurrence from 1 to 0..unbounded of the target message type in the MM which generated the structure as :

- Message ( occurrence 1..1)

- - - - - - Message1 (occurrence 1..1)

- - - - - - - - - - Target_MT ( occurrence 0..unbounded)

- - - - - - - - - - - - field1

- - - - - - - - - - - - and so on......

Since the Message1 has a occurrence 1..1, even if I map it with the Input Country field which contains multiple countries, the Message1 only appears once.

( If I map the same field to the Target_MT ( message types) , it does generate as many number of TArget_MT as the number of country in the Input, which is not going to help me in this case)

Please correct me, if I have misconstrued your solution.

[Former Member · Accepted solution]

HI Peeru

No you are correct. You need to map it to target_MT only to generate the number of messages per country.

You got 4 files. But I think you want to generate 2 files. One for US and One for CA Right?

Then the solution is not going to work for you.

I can think of Java mapping. But let me try with graphical and get back to you.

Thanks

Gaurav

[Former Member · Accepted solution]

Hello Gaurav,

I have only one input file with four records and I have to generate the multiple target files based on the number of countries coming in the input file. In the given example , I have two countries, so, there should be two files generated on the target end.

Also, all the files have to be sent to the same receiving system.

Looking forward for a graphical based solution.

Thx PEERU

[nisarkhan_n · Accepted solution]

Hi Peeru

the target file which you are trying to create does it have any specific filename conventions if not just try this it will work.

in receiver file adapter choose the filename from variable substuition, now define the variable substuition to the field which holds the country name, and set this to the filename,

in the attribute choose the file construction mode as append.

What it does is, for the below strucutre

row1 David US

row2 Cindra US

row3 Peeru CA

row4 Jay CA

it will create two files one with the name US.txt all records which contains the US will be written to this file and second file it will write CA.txt, all records which has the country name CA will be writen to it.

( i am not 100% sure on the FILE CONSTRUCTION MODE is append or create i will check and will get back to you, but if you search the SDN i have replied earlier for same issue)

you dnt have to do anything on the mapping, let adapter split the records and create files...let me know if you more question on this.

the

Edited by: Nisar Khan on Dec 4, 2008 1:32 AM

[Former Member · Accepted solution]

Thanks Nisar. This solution is definitely something interesting but in my case, I have a specific file name format with the time stamp and I have to add the country name at the end.

e.g Countryfile_<timeStamp>_US.xml.

Can your solution work for the above file name too ?

[nisarkhan_n · Accepted solution]

Hi

I dont think it might, but i am not sure about this as i have not tried, but first if you can check this works for you then the time stamp can be added by another field in variable substuition (but i dnt think it will makes sense to your requirement)

Can you try, by changing the file construction mode to (ADD TIME STAMP) see how it behaves..

the other work round is

IF the above method what i have said is working for you exactly then adding the file name for the format which you have can be done, you just have to create an additional field in the target side but the time stamp can create problem but i think there can be workaround for that also.

regards

Nisar

Edited by: Nisar Khan on Dec 4, 2008 1:40 AM

[Former Member · Accepted solution]

Hi Peeru

Just saw the solution given by Nisar.

Its a good one. But to your filename. There are some challenges.

File adapter mode append should collect the file data for you. All records with US can be written to the file.

Now file name. Adding a field will not be good. As output is XML additional field will come.

Adding time stamp will add time stamp at last. not between Country and US.

Its interesting to solve this

Thanks

Gaurav

[nisarkhan_n · Accepted solution]

can u tell whats the timestamp details you have, does it have every sec?

As i told this solution might not make sense to your requirement...... but if we can create an additional field in target structure which hold the filename format and use that for variable substution and it works for you then we can take this field out from stucutre when adpater tries to write the data as TXT from XML by addiing the parameter

(Again i am not sure this will work, if the problem can be solved by variable substution then taking that field out from XML while writing to txt i think can be handled)

xml.fixedLengthTooShortHandling=Cut

[Former Member · Accepted solution]

Hi Nisar,

Well, I dont have the clear requirement for the timestamp, but for now, lets assume that we need the time stamp which XI put when we add the timestamp in the CC.

Also, I tried to check the generation of multiple files based on the payload ( without the timestamp) , but I failed.

I added a Variable parameter as fileName payload: Line1,1,Name,1,Country,1 and in the fileName I have put %fileName%.xml.

The above variable substituation option has created a single file with the name US.xml with all the four records ( it dint create two separate files for US and CA)

[nisarkhan_n · Accepted solution]

Hi

I think you should try with FCC not as XML, let me know if it doesn't work when you do an FCC and follow( i think XML it will not work it will try to put all in single file itself).

please mail me if the screen shots of the receiver file adapter configuration.

regards

Nisar

Edited by: Nisar Khan on Dec 4, 2008 4:22 AM

[Former Member · Accepted solution]

HI Nisar,

I have to create .xml files; so, i have to use File and not FCC. Do you see any option open with all the constraints my case has ?

Thanks in Advance.

~PEERU

[Former Member · Accepted solution]

HI Peeru

I tried this using graphical maps but no success. At max i was able to do was individual message. But even the solution given above by Nisar didn't write the file as US.txt and CA.txt. With the processing mode append.

I feel you should go for Java or XSLT to generate two target structure with record for US and CA separately and use conditional receiver determination with Two CC to same target location. It will allow file naming convention..

One more thing i tried with your case is using the Dynamic Configuration for setting the file name. But We can set only one name using that. So either US or CA. To get file name like you want adapter module can be a way. As you are using XML FCC cannot be implemented So using variable substitution is not an option.

Thanks

Gaurav

[Former Member · Accepted solution]

Yes, I tried too using FCC and variable substitution but no success.

I dint understand your remark :

"As you are using XML FCC cannot be implemented So using variable substitution is not an option."

Even though I dont have to use FCC ( as I want an xml file) but then also I could use Variable substitution for the file name.

In my case, I dont know how many CC I will need, so, somehow I have to tweak the interface to use one CC and generate the file names based on the data.... dont know how to achieve that.

[nisarkhan_n · Accepted solution]

Peeru,

I should have checked with you initially about your target file,i assumed it as text file, in case of XML as target file this solution might not work, about the target as XML, let me get back to you how can we deal about this.

The solution what i had mentioned works fine in case your target file is text because thats what i had done for one of requirement i had 2 years back, i saw the attachment which you have sent, can u change the file const mode from APPEND to CREATE, i will also try to recreate the sample tomorrow if you change the option to CREATE.

in the input file do you have the number of country value which can occur or it is dynamic? send me your sample input XML file.

Gaurav:

Can you change the file const mode to create? file splitting by adapter works fine, if not i will try to create the interface and update you guys.

I think varibale substuition should work for XML also, let me also check this.

regards

NisarKhan

Edited by: Nisar Khan on Dec 5, 2008 5:30 AM

[Former Member · Accepted solution]

@ Peeru

Sorry for the confusing statement.

We can use Variable substitution for XML,CSV,TEXT files.

If for your case we use variable substitution as a added field then it will add one more field to the file and without FCC we cannot remove that field. Thats the reason i tried out with DynamicConfiguration but it can be set once and not more than that.

@ Nisar

I tried with Create as well but was not able to generate the file. Will check again for some config mistake

I am also working on this issue its quite intresting

Thanks

Gaurav

[nisarkhan_n · Accepted solution]

MultiMapping can work for this i think, for the below record strucutre

row1 David US

row2 Cindra US

row3 Peeru CA

row4 Jay CA

if i genereate the target strucutre inthe below way and inthe receiver file adapter if we point the country field as variable substuition it might work, just a guess as if not, tmrw will try to check if it works.

<Message>

<Message1>

<data1>

<row>row1</row>

<name>David</name>

<country>US</country>

</data1>

<data1>

<row>row2</row>

<name>Cindra</name>

<country>US</country>

</data1>

</Message1>

<Message2>

<data1>

<row>row3</row>

<name>Peeru</name>

<country>CA</country>

</data1>

<data1>

<row>row4</row>

<name>Jay</name>

<country>CA</country>

</data1>

</Message2>

</Message>

[nisarkhan_n · Accepted solution]

Hi Peeru,

I don't have access to the any FTP or file server of XI to check the file adapter as i am travelling right now(I am 100% sure that the file adapter splitting will work as i have done this for one of my requirement).

Coming to your requirement:

I wrote an java map which will read the XML file and create the multiple recordset based on number of different country exists in the file, then i am printing the final structure in an multi mapping layout (please see the attached input file Country.xml and out put file which the java map generated Final.XML) if we do this i think the file adapter will create the different files based on the number of recordset we have as you can see the output file is generated with the 3 recordset (<Country>) tag each country tag has its own records, now if i give the filename in variable substuition pointing to region then i think i should get 3 files with names

us.xml

us01.xml

us02.xml

i think by using the multi mapping i can generate 3 files and by using the variable substution i can give the 3 different names from the payload of each file and also can add the timestamp for each of the file, again i didn't have access to the file adapter in the project i am working on so i couldn't verify in XI server, but if you still looking for solution then let me know i will give the map details.

Input File: Country.xml

<?xml version="1.0"?>

<check>

<order>

<name>Nisar1</name>

<region>US</region>

</order>

<order>

<name>Nisar2</name>

<region>US</region>

</order>

<order>

<name>Nisar3</name>

<region>US</region>

</order>

<order>

<name>Nisar4</name>

<region>US01</region>

</order>

<order>

<name>Nisar5</name>

<region>US01</region>

</order>

<order>

<name>Nisar6</name>

<region>US01</region>

</order>

<order>

<name>Nisar7</name>

<region>US</region>

</order>

<order>

<name>Nisar8</name>

<region>US</region>

</order>

<order>

<name>Nisar8</name>

<region>US02</region>

</order>

</check>

Output File : Final.XML

<ns0:Messages xmlns:ns0="http://sap.com/xi/XI/SplitAndMerge">

<ns0:Message1>

<country>

<Order>

<name>Nisar1</name>

<region>US</region>

</Order>

<Order>

<name>Nisar2</name>

<region>US</region>

</Order>

<Order>

<name>Nisar3</name>

<region>US</region>

</Order>

<Order>

<name>Nisar7</name>

<region>US</region>

</Order>

<Order>

<name>Nisar8</name>

<region>US</region>

</Order>

</country>

<country>

<Order>

<name>Nisar4</name>

<region>US01</region>

</Order>

<Order>

<name>Nisar5</name>

<region>US01</region>

</Order>

<Order>

<name>Nisar6</name>

<region>US01</region>

</Order>

</country>

<country>

<Order>

<name>Nisar8</name>

<region>US02</region>

</Order>

</country>

</ns0:Message1>

</ns0:Messages>

regards

Nisar Khan