cancel
Showing results for 
Search instead for 
Did you mean: 

Flat File To Complicated Hierarchical Structure

laurent_fournier2
Contributor
0 Kudos

Dear Experts,

I'm facing the following problem :

I have a flat file with invoice data,

invoice number - line type - field name - field value

000101 - H - Invoice Date - 01.02.2010

000101 - H - Invoice TYpe - ZZZ

000101 - C - Customer Name - John Doe

000101 - C - Customer Address - XXX avenue

000101 - L1 - Line Item - 60003

000101 - L1 - Material - 98765

000101 - L1 - Net Value - 100,00

000101 - L2 - Line Item - 60004

000101 - L2 - Material - 98766

000101 - L2 - Net Value - 200,00

000102 - H - Invoice Date - 01.03.2010

000102 - H - Invoice TYpe - ZZZ

000102 - C - Customer Name - John Doe 2

000102 - C - Customer Address - yyy avenue

000102 - L1 - Line Item - 60001

000102 - L1 - Material - 98763

000102 - L1 - Net Value - 199,00

000102 - L2 - Line Item - 60002

000102 - L2 - Material - 98764

000102 - L2 - Net Value - 299,00

( H,C,and L* stands for Header,Customer,Line item )

and want somehow to convert it into XI to structured XML like this

output

-->Header ( 1:Unbound )

--> InvoiceDate

--> InvoiceType

-


> Customer ( 1:1)

-


> CustName

-


>CustAddr

-


> Item ( 1:unbound)

-


> ItemNo

-


>Matnr

-


>Value

Can somebody quide me through the mapping process ?

Tried with predefined functions ( splitpervalue,createif etc ) but with no luck.

Can somebody give me a hint !!

Edited by: Laurent Fournier on Dec 23, 2010 2:50 PM

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

if u want the structure as

Header

Customer

LineItem

Header

Customer

LineItem

Header

Customer

LineItem

Please give the recordStructure as Header,,Customer,,LineItem,*

This will take care the whole structre.

Specify the keyfields as H,C and L instead of the invoice number

Babu

laurent_fournier2
Contributor
0 Kudos

I'm not talking about file content conversion. I'm talking about message mapping.

The problem is that many nodes of my inbound file must fill in one node in the target file .

Hope it's clear now.

Former Member
0 Kudos

Hi Laurent,

If I am not missing any understanding then it appears that you are not using content conversion for your sender file adapter. Your subjuect of the thread says that you are having a Flat file at the sender side. If this is the case then you can not map your source structure to target structure without content conversion or getting the flat file in a xml format. PI works only on xml so you need to go with content conversion and get a xml and then only you can do any transformations inside the PI.

Regards,

Rahul

Former Member
0 Kudos

Hi,

Sorry i think i misunderstood the question!!!

Let us first understand exact requirement in the mapping, then some1 here can give u the solution..

For the i/p u have given in the post...

Please check is it the output expected......

Invoice Date - 01.02.2010

Invoice TYpe - ZZZ

Invoice Date - 01.03.2010

Invoice TYpe - ZZZ

Customer Name - John Doe

Customer Address - XXX avenue

Line Item - 60003

Material - 98765

Net Value - 100,00

Line Item - 60004

Material - 98766

Net Value - 200,00

Line Item - 60001

Material - 98763

Net Value - 199,00

Line Item - 60002

Material - 98764

Net Value - 299,00

This may be wrong because in above o/p the only 1 customer details given with all line items.

I mapped so because the occurence of customer is 1:1.

or is this o/p is correct?

Invoice Date - 01.02.2010

Invoice TYpe - ZZZ

Customer Name - John Doe

Customer Address - XXX avenue

Line Item - 60003

Material - 98765

Net Value - 100,00

Line Item - 60004

Material - 98766

Net Value - 200,00

Invoice Date - 01.03.2010

Invoice TYpe - ZZZ

Customer Name - John Doe 2

Customer Address - yyy avenue

Line Item - 60001

Material - 98763

Net Value - 199,00

Line Item - 60002

Material - 98764

Net Value - 299,00

Babu

laurent_fournier2
Contributor
0 Kudos

Hi Rahul,

Flat file is being converted to simple xml structure in XI using content conversion.

So example file (txt)

000101 - H - Invoice Date - 01.02.2010
000101 - H - Invoice TYpe - ZZZ
000101 - C - Customer Name - John Doe
000101 - C - Customer Address - XXX avenue

will be converted to xml ( inside XI )

<LINE>
      <Field1>000101 </Field1>
      <Field3>H</Field3>
      <Field4>InvoiceDate</Field4>
      <Field5>01.02.2010 </Field5>
   </LINE>
   <LINE>
      <Field1>000101 </Field1>
      <Field3>H</Field3>
      <Field4>InvoiceType</Field4>
      <Field5>ZZZ </Field5>
   </LINE>
   <LINE>
      <Field1>000101 </Field1>
      <Field3>C</Field3>
      <Field4>CustomerName</Field4>
      <Field5>John Doe</Field5>
   </LINE>
   <LINE>
      <Field1>000101 </Field1>
      <Field3>C</Field3>
      <Field4>CustomerAddress</Field4>
      <Field5>XXX Avenue</Field5>
   </LINE>

The target xml structure is ( inside XI )

<HEADER>
  <InvoiceNo>000101</InvoiceNo>
  <InvoiceDT>01.02.2010</InvoiceDT>
  <InvoiceTY>ZZZ</InvoiceTY>
  <CUSTOMER>
    <CustomerName>John Doe</CustomerName>
    <CustomerAddress>xxx Avenue</CustomerAddress>
  </CUSTOMER>
  <ITEM></ITEM>
  <ITEM></ITEM>
  <ITEM></ITEM>
</HEADER>

Target File ( txt )

invoice,invoice type,invoice date,customer name,customer address,line item 1, material number,value
invoice,invoice type,invoice date,customer name,customer address,line item 2, material number,value
invoice,invoice type,invoice date,customer name,customer address,line item 3, material number,value

Problem : How to do message mapping in order to go from source structure to target structure ???

Is it possible ? As i understand we need to implement the opposite of "one node to many".

Hope now is clear to understand the exact requirement.

laurent_fournier2
Contributor
0 Kudos

Dear Babu ,

your second o/p is correct.

Former Member
0 Kudos

Hi,

Before some1 here provide u how to map for this case....

How are u accessing the source file FCC?

Does the contents are coming like ,

000101 - H - Invoice Date - 01.02.2010

000101 - H - Invoice TYpe - ZZZ

000101 - C - Customer Name - John Doe

000101 - C - Customer Address - XXX avenue

000101 - L1 - Line Item - 60003

000101 - L1 - Material - 98765

000101 - L1 - Net Value - 100,00

000101 - L2 - Line Item - 60004

000101 - L2 - Material - 98766

000101 - L2 - Net Value - 200,00

000102 - H - Invoice Date - 01.03.2010

000102 - H - Invoice TYpe - ZZZ

000102 - C - Customer Name - John Doe 2

000102 - C - Customer Address - yyy avenue

000102 - L1 - Line Item - 60001

000102 - L1 - Material - 98763

000102 - L1 - Net Value - 199,00

000102 - L2 - Line Item - 60002

000102 - L2 - Material - 98764

000102 - L2 - Net Value - 299,00

The whole record as a single element or are u doing any other FCC?

Let us know the structure of source so that some1 can provide u the Mapping..

Babu

anupam_ghosh2
Active Contributor
0 Kudos

Hi Laurent,

with your complicated target xml structure, I feel you need java mapping. With java mapping you can cretae any target xml structure you like.

regards

Anupam

laurent_fournier2
Contributor
0 Kudos

Dear Babu,

The contents are coming exactly as you said. I Upload the contents using a simple FCC so each line produce a new node in my source xml.

Regards,

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Two or more deep xml structure conversion is not possible in file content conversion . If you want to convert into the below structure i.e at the same level , then it is possible.

Header(1 to 1)

-->Invoice Date

-->Invoice Type

Customer (1 to 1)

--->customer name

--->customer address

LineItem (1 to unbounded)

--->itemno

--->Matnr

---> Value

Please try this link. This might be helpful to you.

/people/shabarish.vijayakumar/blog/2006/02/27/content-conversion-the-key-field-problem

Note: I heard from Shabrish weblog that Conversion Agent tool will be helpful to do deep nested xml structure.

laurent_fournier2
Contributor
0 Kudos

I 'm not trying to use file content conversion.

I'm trying to convert the data using message mapping.

Source Message :

Line ( 1:unbounded)

--> Field1

--> Field2

--> Field3

--> Field4

Target Message

-->Header ( 1:Unbound )

--> InvoiceDate

--> InvoiceType

-


> Customer ( 1:1)

-


> CustName

-


>CustAddr

-


> Item ( 1:unbound)

-


> ItemNo

-


>Matnr

-


>Value

Field3 in source message describes target field in target message.

laurent_fournier2
Contributor
0 Kudos

Just For Clarification...The example bellow is for the header data only. The Same applies on customer and items data.

Inbound File :

000101 - H - Invoice Date - 01.02.2010

000101 - H - Invoice TYpe - ZZZ

Target Strcucture :

Header (1:unbounded)

--> InvoiceNo - 000101

--> InvoiceDT - 01.02.2010

--> InvoiceTP - ZZZ

-


> Customer (1:1)

-


> Items (1:Unbounded)

So to be clear.. The first line of the inbound file should produce a line in the header and fill the header field Invoice No and invoice date.The Second line of the inbound file must NOT produce a new line in header but Fill in the InvoiceTy field with the corresponding value.

Any Hints to achive this with Message Mapping ?