File naming scheme

[Former Member]

Hi,

We have a requirement in this way.

We need to pick up data from the database and put them in a CSV format to be picked up by BI.

Now, the question is:

1. We have some fields in the tables with field lengths 4000. Since BI only accepts 60 characters in one field, how do I achieve this?

2. In the requirements, they need only 10000 records per file and the filename scheme is

<tablename><timestamp>_<segment>_of_<totalsegments>.csv.

How do I achieve this?

Any ideas?

Thanks in advance.

Regards.

[Former Member · Accepted solution]

Hi,

>1. We have some fields in the tables with field lengths 4000. Since BI only accepts 60 characters in one field, how do I achieve this?

You need to ask your functional guys or BI guys the logic for this. We cannot tell this beacuse its the logic your client need to tell. If you get 4000 and if they a want only the first 60 then substring function will work. If they want last 60 then you require udf. So check with the client for the logic.

>2. In the requirements, they need only 10000 records per file and the filename scheme is <tablename><timestamp>_<segment>_of_<totalsegments>.csv.

You can do multimapping and split for each 10000 records and pass this filename with a udf which has this naming conventions. Use the udf in your receiver communication channel for the name.

Regards,

---Satish

[Former Member · Accepted solution]

> >2. In the requirements, they need only 10000 records per file and the filename scheme is <tablename><timestamp>_<segment>_of_<totalsegments>.csv.

>

Hi for getting the filename scheme u shud use the Dynamic File name method. Create a UDF to get the value of

<tablename>

<segment>

<totalsegments>

in your payload and then Use ASMA to get the file name

Use the following link for basic Dynamic UDF..also there are many other threads on the same.. search sdn.

/people/shabarish.vijayakumar/blog/2009/03/26/dynamic-configuration-vs-variable-substitution--the-ultimate-battle-for-the-file-name

[Former Member · Accepted solution]

Hi,

I checked with the BI guys and the client..there are some records in the table with all 4000 chararacters. The data type for the field is varchar(4000). Is there a way to split the field for every 60 characters?

Regards.

[prateek · Accepted solution]

Yes you can Split the fields in PI using say Substring function or UDF. But after 1st split, where do you want to pass the data? Do you want that this field occurs multiple times in payload? If yes, then you may handle this in mapping.

Regards,

Prateek

[Former Member · Accepted solution]

Hi,

I want to pass the data into the csv file with each split separated by delimiter. It is being done only because of the constraint from the BI side. Can we use the substring function so many times in mapping? Is there any example of an UDF for achieving this?

Regards.

[Former Member · Accepted solution]

Hi guys,

I am back with the question about the filename.

As seen from the requirement above, the sender data type is:

<row>

<FIELDS />

</row>.

My idea is:

1. Count the total number of occurrences of <row> -

---

> how to do that?

2. Use a UDF to get the total segments using mod function by dividing the total number of occurences of row--->how to pass the above value to this UDF?

3. Then write another UDF to get the filename with the timestamp and combine all the UDFs to get the filename.

Please help me how to achieve these three UDFs.

Regards.

[Former Member · Accepted solution]

HI,

1. Count the total number of occurrences of <row> -

---

> how to do that?

--> You can use basic Java APIs in UDF to find the number of rows. Pass the input field row to UDF and make the UDF parameter as queue. row.length will provide you the number of occurances.

2. Use a UDF to get the total segments using mod function by dividing the total number of occurences of row--->how to pass the above value to this UDF?

--> Either you can include the above mention line in new udf and use it to count the segments or maintain the no of Rows in a global variable and use it across multiple UDFs. You may find couple of blogs for maintaing global variable in Mapping on SDN.

3. Then write another UDF to get the filename with the timestamp and combine all the UDFs to get the filename.

--> Usage of Global variable in the newly created udf for file name will solve your purpose. Also use system date time APIs to get those details as well.

Thanks

Swarup

Edited by: Swarup Sawant on Sep 21, 2009 5:49 AM

[Former Member · Accepted solution]

Hi,

1. For counting the number of rows use the function COUNT of Node Functions

2. you can use the result of 1 as input parameter to you UDF.

3. You can go through this code for Dynamic configuration :

Code:

try {

String filename = "";

String timestamp = "";

DynamicConfiguration conf1 = (DynamicConfiguration) container

.getTransformationParameters()

.get(StreamTransformationConstants.DYNAMIC_CONFIGURATION);

DynamicConfigurationKey key1 = DynamicConfigurationKey.create( "http:/"+"/sap.com/xi/XI/System/File","FileName");

DynamicConfigurationKey key2 = DynamicConfigurationKey.create( "http:/"+"/sap.com/xi/XI/System/File","SourceFileTimestamp");

filename = conf1.get(key1);

timestamp = conf1.get(key2);

filename = filename.replaceAll( ".txt" ,"_" );

filename = filenametimestamp".xml";

conf1.put(key1,filename);

return filename;

}

catch(Exception e)

{

String exception = e.toString();

return exception;

}

Edited by: rahul grover on Sep 21, 2009 9:41 AM

[Former Member · Accepted solution]

Thank you very much for the awesome help guys.

Now I am getting the filename.

But I want to create the file for every "n" number of rows.

In the screen shot below,

[Mapping test|http://img43.imageshack.us/img43/8596/testmapping.jpg]

for example, I want to create a file for every 3 rows.

As you see in the target message, you can see the filename...since 5 rows are present in the source message,

total segments is 002 and the currentsegment is 001.

But after the first 3 rows, I need to get the filename again with the currentsegment as 002 of totalsegments 002.

How to achieve that?

Regards.

[Former Member · Accepted solution]

Hi,

You need to do multimapping for this.In mapping you need to split based upon the count. So you can use the basic standard funcitons or an udf to do the split.

Regards,

---Satish

[Former Member · Accepted solution]

Hey Satish,

I have never done multimapping.

I went through this blog..

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3417300)ID1286950950DB11547653157842873061End...

and performed the mapping with the count and equalsS functions...but of no avail..still gives me the same result..

Can you please explain?

Regards.

[Former Member · Accepted solution]

Hey guys,

This is what I am getting after multimapping.

These are the different mappings I performed.

[UDF for splitting for every 3 rows|http://img197.imageshack.us/img197/8853/udf.jpg]

[Message split|http://img9.imageshack.us/img9/9384/messagesplit.jpg]

[Filename mapping|http://img197.imageshack.us/img197/5241/filenamemapping.jpg]

[UDF for filename|http://img9.imageshack.us/img9/2940/filenameudf.jpg]

[MultiMapping Test|http://img225.imageshack.us/img225/8596/testmapping.jpg]

What I am expecting is in the filename, instead of 001 of 001, in the above example, is

001 of 002 and 002 of 002.

Please help me out.

Regards.

Edited by: DNSPK on Sep 22, 2009 11:19 PM

[Former Member · Accepted solution]

Hey guys,

I got it solved. Had to tweak the UDF for file name a little and it worked like charm.

Whew...

Thank you very much for all the help guys...PI rocks..!!!

[Former Member]

DNSPK,

This depends on the requirement. XI can actually process the data to the target system (4000 characters), however if the receiver doesn't accept then you will have to split 60 characters and then send next 60 in the next iteration. this will require an UDF.

As previously suggested above if not you can always send first 60 characters using substring standard function.

[Former Member]

Hey Pavan,

Thank you for replying. But you gave the answer for the first question which is solved right now.

The I got the partial solution for question 2. But my problem right now is how to achieve all the filenames for the segments created using multimapping.

Regards.

[Former Member]

Hi DNSPK,

As Satish suggested, you might like to check with your Business team for the requirement.

Also, you should look into the Production messages to verify what is the length of value coming in that particular field. There can be a case where the field length has been defined as 1000, but the value never exceeds the length of 15 !!

Regards,

Neetesh