How to Define Multi Record Format Files in ODI

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Datastore, Model and Logical Schema are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.

It is not unusual to have to load files containing various record formats. For example a company might store orders and order lines using distinct record formats in the same flat file or you might have one single file containing a header, some records and a footer.
In this post, we'll use the following source file as an example:


1,101,2009/09/01,Computer Parts
2,234,101,Motherboard, Asus P6T,239.99
2,235,101,CPU,Intel Celeron 430,40
1,102,2009/09/02,Computer Parts
2,301,102,CPU,AMD Phenom II X4,170
1,103,2009/09/05,Printers
2,401,103,Inkjet Printer,Canon iP4600,69.99
2,402,103,Inkjet Printer,Epson WF30,39.99
2,403,103,Inkjet Printer,HP Deskjet D2660,49.99

As we can see the Order and Order Lines records have different formats (one has 4 fields, the other has 6 fields), they could also have a different field separator.

Identifying the Record Codes

The first step in order to handle such a file in ODI is to identify a record code, this record code should be unique for a particular record type. In our example the record code will be used by ODI to identify if the record is an Order or an Order Line. All the Order records should have the same record code, this also applies to the Order Lines records.
In our example the first field indicates the record code:
- 1 for Orders records.
- 2 for Order Lines records.

Define the Datastores

We assume that you have already created a Model using a Logical Schema that points to the directory containing your source file.

We will start by defining a datastore for the Order records.

Right-click on the File model and select Insert Datastore.
In the Definition tab, enter a name and specify the flat file resource name in the Resource Name field. 

Order_Datastore.jpg

 

 

In the Files tab, specify your flat file settings (delimiter, field separator etc.)

Refer to the ODI documentation for additional information regarding how to define a flat file datastore.

 

 

In our example the Order records have 4 fields:
- RECORD_TYPE
- ORDER_ID
- ORDER_DATE
- ORDER_TYPE

Go to the columns tab and add those 4 columns to your datastore.

Now specify the Record Code in the 'Rec. Code' field of the RECORD_CODE column.

 

 

Order_Datastore_Columns.jpg

Click OK.

 

 

In the Models view, right-click on the datastore and select View Data to display the file content and make sure it is defined correctly. 

 

Order_Datastore_Data.jpg

The data is filtered based on the record code value, we only see the Order records.

 

We will now apply the same approach to the Order Lines record.

Right-click on the File model and select Insert Datastore to add a second datastore for the Order Lines record.

In the Definition tab, enter a name and specify the flat file resource name in the Resource Name field. We are pointing this datastore to the same file we used for the Order records. 

 

Order_Line_Datastore.jpg

In the Files tab, specify your flat file settings (delimiter, field separator etc.).
Refer to the ODI documentation for additional information regarding how to define a flat file datastore.

 

 

In our example the Order Lines records have 6 fields:
- RECORD_TYPE
- LORDER_ID
- ORDER_ID
- LINE_ORDER_TYPE
- ITEM
- PRICE

Go to the columns tab and add those 6 columns to your datastore.

Now specify the Record Code in the 'Rec. Code' field of the RECORD_CODE column.

 

Order_Line_Datastore_Columns.jpg

Click OK.

 

Right-click on the datastore and select View Data to display the file content and make sure it is defined correctly.

 

Order_Line_Datastore_Data.jpg

The data is filtered based on the record code value, we only see the Order Lines records.

You can now use those 2 datastores in your interfaces.

 

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

Comments:

Thanks for the example. I have a similar issue with a hierarchical file however apart from their position within the file there is no other way to identify what detail record belongs to which header record. For example: 01 Header Record 02 Detail Record 02 Detail Record 02 Detail Record 03 Footer Record 01 Header Record 02 Detail Record 02 Detail Record 03 Footer Record 99 Tail Record What is the correct approach to link the detail records to the correct header records? Thanks Andrew.

Posted by Andrew Cunningham on October 27, 2009 at 10:26 AM PDT #

Hi Andrew, Thanks for your comment. You might have to pre-process this file before using it in ODI so ODI can parse it correctly. This could be automated in a package, you'd start with an ODI procedure doing the pre-processing (using a scripting language) and then have an interface load the file. Another option could be to load the file into the DB potentially adding a generated sequence and then to link the detail records with their header records using hierarchical functions (on Oracle). Thanks, Julien

Posted by Julien Testut on November 02, 2009 at 07:16 AM PST #

Thank you this is a wonderful post, I bookmarked your blog.

Posted by Planning and scheduling on December 07, 2009 at 04:40 AM PST #

Hi Thanks a lot for your post, was very usefull for me. How can I load a Multi Record flat file? I need to load the records in the correct order, header record then detailed records. Thanks Edwin

Posted by Edwin Guerrero on January 04, 2010 at 05:23 AM PST #

Hi Edwin, You could have 3 interfaces: 1. Insert Header Information into Flat File (using Datastore defined with Header record structure). 2. Insert Detailed Records into Flat File (using Datastore defined with Detailed Records structure). 3. Insert Footer into Flat File (using Datastore defined with Footer record structure). Even so those 3 interfaces would use a different target datastore they would ultimately write into the same flat file. Once you have your interfaces you can put them in the same workflow. Thanks, Julien

Posted by Julien Testut on January 04, 2010 at 09:46 AM PST #

Hi Julien: Thanks for your response. But I need to put the records in order, in the same form of your example in this post: 1,101,2009/09/01,Computer Parts 2,234,101,Motherboard, Asus P6T,239.99 2,235,101,CPU,Intel Celeron 430,40 1,102,2009/09/02,Computer Parts 2,301,102,CPU,AMD Phenom II X4,170 1,103,2009/09/05,Printers 2,401,103,Inkjet Printer,Canon iP4600,69.99 2,402,103,Inkjet Printer,Epson WF30,39.99 2,403,103,Inkjet Printer,HP Deskjet D2660,49.99 In the workflow I only can put ALL headers first and then all DETAILS. How Can I do this in the correct order? Thanks Edwin

Posted by Edwin on January 06, 2010 at 12:17 AM PST #

Edwin, I would use an approach similar to the one I described in my previous comment: - first interface would create a header file containing all the headers. - second interface would create the detailed records file. - then I would have a shell script or Jython/Java process that combines the 2 files together: - open both files - read the ID from the first header line (101 in my example). To extract the ID value, I would split the header line into an array based on the field separator and then read the second array value. - write the header record into output file. - write all the detailed records with the same ID into the output file. To identify the line record ID, I would use the same technique ie splitting the detailed record line based on the field separator and read only the ID field. - once the first header line is processed, read the second header line and so on. Using this approach you can still leverage the set based processing of ODI to create the 2 staging files (header and detailed record) and then use a procedural script to build the final output linking the detailed records and the header lines with the header ID. Thanks, Julien

Posted by Julien Testut on January 07, 2010 at 01:28 AM PST #

Hi Julien!! Great job! Excellent post! But, if you don't mind, I would like to ask you about Flat Files in ODI... I have the ODI installed in my local machine, but I need to use flat files in server directory (Linux). That's my problem: I only see the default directory in the local machine, in order to use a file in the server, I need to copy it from server to my local directory... Is there a way to redirect the default directory from my local machine to my server Linux machine? Thanks a lot!! Lito Brazil - São Paulo

Posted by Lito on April 15, 2010 at 01:21 AM PDT #

Hi Lito, Sure you can create a physical schema underneath the File technology which would refer to a remote directory. I guess you're using FILE_DEMO_SRC right now which points to the ../demo/file folder. Note that you need to have an ODI agent running on your Linux box otherwise ODI won't be able to see the file. Thanks, Julien

Posted by Julien Testut on April 16, 2010 at 07:43 AM PDT #

Hi Julien, I have a scenario like this: The data is coming from a file and it has Header and Details fields. The whole details will be in single line with one Header and multiple lines. It will be like: HeaderDetail1Detail2Detail3..... The file is of fixed length format. I have to poll the data from this file and insert the Header details to a Header table and the Line details to a Details table. Could you please help me to find out a solution for the same. Thanks, Anju

Posted by guest on September 28, 2010 at 07:02 PM PDT #

Hi Anju, I would use a script to pre-process the file (OS or Jython) or maybe have a first interface that does some substring to separate the different elements (header and lines) and then another interface that loads the data once it is in a proper format. Thanks, Julien

Posted by Julien Testut on October 04, 2010 at 10:07 AM PDT #

Hi We have a stiuation where there is no Record identfier for header trailer or detail. However we now the position of the record in file : Let's say 1 line is header and last line is trailer .all 3 have different format. Now even if we use the sequence generator to log the rows in C$ , the C$ will not get loaded Row by Row , so the option of picking max and min seq is lost. Could you please suggest a way for the same

Posted by tej on May 18, 2011 at 11:47 PM PDT #

Hi Tej, If you know the first line in your file is always the header then you can specify it in the Datastore definition regardless of the record format (Files panel, Heading field). If the format of the footer is different than the data format (records that are not the header or footer) then applying a Rec. Code will automatically discard that record from the Datastore. If you don't need the footer at all, I'd run a quick OS command prior to using the file in ODI to remove the footer. You can orchestrate the whole process in a package. I hope this helps. Thanks, Julien

Posted by Julien on May 20, 2011 at 08:34 AM PDT #

Hi julien, Than you for the response.However the problem is i can't discard header neither footer. It's like one i load the file i need to store it in 3 different tables and then integrate the information into a single table. for eg: let's say contract no exist in header. and details for the contract exists in details but they do not contain header. so for each header i get , i need to join the information from header to details. and als o the summary information present in footer needs to be stored in different table

Posted by tej on May 20, 2011 at 06:16 PM PDT #

Thanks for update Tej. Then I would define 3 different datastores using a Rec. Code for each of them which would leave me with: - one datastore with only the header data - a second one with only the details - a 3rd one with only the footer You can then join those datastores together as you want and store it in a 4th datastore. Thanks, Julien

Posted by Julien on May 24, 2011 at 06:54 AM PDT #

Hi julen, Let me put the problem in detail once again.I guess in the chain some points in previous comment were lost scenario1: 1) file received with header ,detail footer information. 2) no record type (record identifier) exists to seperate and store information. 3) the only information available is 1st line is header, and last line is trailer. 4) the data length of header,footer and detail is same. 5) need to store information of header,footer and detail in seperate tables,so that these can be integrated. issue: 1) while loading the data into c$, the data is loaded in random fashion , so cannot define the 1 st record as header. 2) tried LKM row by row but it seems not to be compatible with files . Scenario2: file format AAAA 9999999999999999999 BBBB 999999999999999999 CCC 999999999999999999 CCC 99999999999999999 DDD 9999999999999999999 BBB 99999999999999999999 CCC 99999999999999999999 CCC 9999999999999999999 DDD 99999999999999999999999 ZZZ 999999999999999999999 where AAA :is main header BBB : IS SUB HEADER CCC: DETAIL OF SUBHEAADER DDD: SUB TRAILER ZZZ: MAIN TRAILER Need to tie each individual subheaer to its corresponding sub detail only . howver no join is present , the data needs to be normalized (or flatened out). These are the 2 scenario's in discussion. Thanking for your kind help Regards Tej

Posted by tej on May 24, 2011 at 02:42 PM PDT #

Hi Tej, We do have a new technology in ODI 11.1.1.5 called Complex Files, I'd recommend to have a look at it (http://download.oracle.com/docs/cd/E21764_01/integrate.1111/e12644/complex_files.htm#sthref53) and see if it can solve your problem. Thanks, Julien

Posted by Julien on June 03, 2011 at 09:14 AM PDT #

Hi Julien,
Thanks for this wonderful post , I need to load a multi record fixed file where header footer and line identifiers are non-numeric values and header, footer and line records are of different length . (For example, Header – FileHeaderRec1Rec22011 Line Record – FileLineRec1Rec200001000011). So, I have assigned Record Code = ‘FileLine’ in the line datastore to load line records. But it is not working. But the interface works if the Record Code has a numeric value. Is there any way the record code will work with non-numeric values..otherwise I will have to pre-process the file.
Thanks Saby.

Posted by saby on June 14, 2011 at 09:38 PM PDT #

Hi Saby,

The Record Code field should accept both numeric and non-numeric values. I'd suggest to open up a support request to have our support team look into it.

Thanks,
Julien

Posted by Julien on June 22, 2011 at 08:16 AM PDT #

Hi Julien! Thanks for this post, it was very useful for me. I'd like to ask you how can I process a flat file which appears in my server directory, but its name is variable, eg: HHRR_20110705 (if the file was generated last week) or HHRR_20110712 (if the file was sent today)
Thanks for your time
Facundo, Buenos Aires - Argentina
PD: I'm working with ODI 11.1.3 and I can´t upgrade it

Posted by flopezsanz on July 12, 2011 at 02:42 AM PDT #

Which KM are you using? If you are

One technique is illustrated below...
http://blogs.oracle.com/dataintegration/entry/using_parameters_in_odi_the_dy_1

There are other options though, for example if using external table to stage files for Oracle, a small surgical change can be made to the LKM in order to list the files in the directory and have all files staged (dynamic files, compressed files etc.).

Cheers
David

Posted by David on July 12, 2011 at 04:20 AM PDT #

Hi Julien, congratulations for the post!

I'm developping a project and I've a problem. I need to each register in a table, create 2 lines in the file. Is it possible using interface? How? Because I just could from a single row from a table, create one row in the file.

Thanks

Posted by Ronaldo on January 06, 2012 at 11:32 PM PST #

Hi Ronaldo,

It is doable but you need to come up with the right SQL statement when ODI reads the file. Try to drag and drop your source twice and do a cross join between the 2 datastores. From there you should be able to split one record into multiple records.

Thanks,
Julien

Posted by Julien on January 10, 2012 at 05:42 AM PST #

I have the exact same scenario and I need to load the different Record types to different tables.I have created different datastores for those and used in the interfaces. But I am getting ODI-26022 Unrecoverable error during object saving. Please suggest how can I get rid of the error.I can save other objects so there is no question of repository connectivity.

Posted by guest on February 29, 2012 at 10:34 PM PST #

Hello,

The ODI-26022 error should not be related to the use case you're trying to implement but most likely to a problem in the Interface. I'd recommend to work with our Support team which can help you with ODI issues.

Thanks,
Julien

Posted by Julien on March 01, 2012 at 11:48 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today