topic Re: [resolved] Conditional parsing of a positional file into Oracle tables in Talend Studio

[resolved] Conditional parsing of a positional file into Oracle tables

_AnonymousUser — Wed, 30 Jul 2014 16:43:19 GMT

I've recently started using Talend for Data Integration and had a question regarding how to tackle a specific issue.
I'm attempting to load a positional file into a set of Oracle tables. Each row of positional data contains two separate character values that determine the type of the row. The type of the row determines the positional placement for each of the expected columns.
To reiterate, the way you must parse each line of the positional file is dependent on two fields of data contained within the row itself.
In order to compensate, I had to strip out the two fields, and based on their values, copy the entire row of text to the appropriate output file containing rows of that type. Once all the rows of the original file were divided by type into their respective files, I could then parse each file, knowing that within each file, the positional data placement would be the same. This process works, but is extremely slow, resulting in a complex, messy solution that produces a lot of overhead data that would need to be cleaned up after the load.
A few questions come to mind. Does Talend support using .ctl files to parse a file into a table, as would SQLLDR? Is there any support for conditional line by line parsing of a positional file in Talend without needing to divide the original file? Are there any other alternatives that may work?
Any advice is appreciated.

Re: [resolved] Conditional parsing of a positional file into Oracle tables

Anonymous — Thu, 31 Jul 2014 08:14:26 GMT

if i am correct then you have multi-schema positional file and you want to load it into oracle tables based on one column condition. it is possible using. tFileInputMSPositional component.
if you post some sample rows from your input and required output. community can suggest you best answers.

Re: [resolved] Conditional parsing of a positional file into Oracle tables

Anonymous — Thu, 31 Jul 2014 14:36:39 GMT

Unfortunately, I am not at liberty to show a sample of the data we intend to load. Besides, without the associated documentation, the data raw would be exceedingly difficult to read. However, I can give you more details as to the general structure.
The original datafile contains hundreds of thousands of rows of positional data. Each row is assumed to have the same character length. Character position 5 designates the category of the row. The position of the subcategory changes depending on the category value.
For instance, if the category is "A", then the subcategory would always be located at character position 13. If the category is "P", then the subcategory would always be located at position 6.

Much like how the category value determines the subcategory placement, the category and subcategory values together also determine how the rest of the columns are positioned in the row.
In order to determine which table the row belongs to and which character positions refer to which columns, you would have to:
1. Determine the position of the subcategory value based on the category value at position 5
2. Determine the position of the rest of the columns based on both the category and subcategory values.
I hope this helps. Thanks for the tip. I'll see if tFileInputMSPositional has what I need.

Re: [resolved] Conditional parsing of a positional file into Oracle tables

Anonymous — Thu, 31 Jul 2014 14:50:09 GMT

Hi,
Have you tried to create metadata for your input file? If you can create a standard metadata, then i think, this is possible.. Please let me know after creating the metadata...
Can you create a look-up table for category and subcategory, based on this we can try to perform some logical operations...during the data flow.
Thanks
Vaibhav

Re: [resolved] Conditional parsing of a positional file into Oracle tables

Anonymous — Thu, 31 Jul 2014 15:12:00 GMT

I have tried to create a positional file metadata object for my input file. The issue is that the position of the subcategory value changes based on category value. Let me clarify by showing you the following two rows:
XXXXARXXXXXXXXXXXXX
XXXXPXXXXXXXDXXXXXX
At position 5 for both columns, you'll find a character value for the category that the row belongs to. That character value determines the subcategory placement. In the first row, the category value is "A." All rows with the category of "A" will have its subcategory at position 6. In this case, the subcategory is R. For the second row, the category is "P." All rows with the category of "P" will have a subcategory at position 13. In this case, "D."
Both the category and subcategory values together determine how the rest of the characters (marked by X's in the example) are parsed.
Maybe I misunderstood what you meant by creating metadata, but when I create a positional file metadata object, it seems to assume that my column positions will be the same for every row, which isn't the case with my data. I could create a positional file object for one type of row, but it wouldn't be compatible with all the other types of rows within the same file.
Also, while my positional file does use multiple schemas, tFileInputMSPositional components seem to assume that there is only one field that determines which schema to use. In my case, it is two fields. One is always at the same position. The other is at a variable position that is dependent on the value of the first.
The lookup table idea sounds interesting. How could that be done in Talend?

Re: [resolved] Conditional parsing of a positional file into Oracle tables

Anonymous — Thu, 31 Jul 2014 16:08:58 GMT

The issue is that the position of the subcategory value changes based on category value.
>>
This is not a problem... based on the lookup, you can retrieve that sub category...
Lookup is like a simple table with the category and subcategory values, which will be loaded before execution of the job and used at appropriate places.

Vaibhav

Re: [resolved] Conditional parsing of a positional file into Oracle tables

Anonymous — Thu, 31 Jul 2014 17:43:22 GMT

This would work, but is this something that can be created in Talend or must I implement the lookup table as a separate database table?

Re: [resolved] Conditional parsing of a positional file into Oracle tables

Anonymous — Fri, 01 Aug 2014 09:00:24 GMT

This would be great if the solution works...
Lookup could be within talend using tfixedflow or a flat file or a database table etc...
Vaibhav