Parsing fields from flat file

[pradkumar]

Hi

I am having a flat file which is having data like:
1 record: 470E|Hyd! 3721|Hyd! Consolidated||Intl||Asia Pacific||North Asia||Total China w/ JV||Total China||470E|Hyd! 3721

2 record: 471E|Hyd! 3722|Hyd! Consolidated||Intl||Total China w/ JV ||Total China || 471E|Hyd!3722



I want to detach the code which is at end of each record into separte column "alt_text" and palce at the end of record. The output should look like

OUTPUT :
CODE | TEXT|.... |ALT_TEXT (=TEXT)
470E | HYD!3721|...|HYD!3721
471E|HYD!3722|.....|HYD!3722
How to do this?

My job is something like seq -> transformer->table

[DSguru2B]

Confused. What is the delimiter of your flat file. Is it double pipes? How did you specify that in your sequential file stage in the first place?

[pradkumar]

The delimiter is pipe
I am sorry for not being clear.

[I_Server_Whale]

Hi,


1. Are you able to read the flat-file using sequential file stage?

2. Your example sometimes shows two "|" pipe characters, Is this because you have an empty field?

If you are able to read the flat-file successfully, then I think this should be pretty much a straight map.

It is tough to decipher your example.

Whale.

[pradkumar]

Thanks for the reply

I am able to read the flat file successfully.
But the problem is : the last tag is coming in different column number in different records. I want it to send to one last column known as alt_text.

We don't have any knowledge in which column that particular tag appears.

[DeepakCorning]

I think your issue is unncessary pipes which are there in your file ans hence the data is not coming in the correct places. Can you open the file in EXL and confirm that the data is not shifted to other columns etc.

[narasimha]

But the problem is : the last tag is coming in different column number in different records. I want it to send to one last column known as alt_text.

We don't have any knowledge in which column that particular tag appears.

- Does this tag have a specific format?
- Does this tag repeat in a given Record?

Need more explaination on the Input you have and the output you want

[pradkumar]

No the tag does not repeat

[shilpa79]

Delimited Substrings is the one You can extract using this syntax:

string [ delimiter, instance, fields ]

string is the string containing the substring.

delimiter specifies the character that delimits the substring.

instance specifies the instance of delimiter where the extraction is to start.

fields specifies the number of fields to extract.


you can also use field function 'field'
Field (string, delimiter, instance [ ,number] )

[ShaneMuir]

If the number of columns that you are receiving is variable and you have to get the last column you could try an amalgamation of a couple of functions.
Read the input row in as one field, and use an amalgamation of the Field and Count functions eg:

Code: Select all

Field(input.string,'|',(Count(input.string,'|')+1),1)

Not sure if the +1 is required, you would have to tinker. You could also use the functions separately. Put the count function in a stage variable and pass it to the field function.
NB this is for the last field only. How you extract other fields is up to you.

[ray.wurlod]

That construction is so useful I store it as a reuseable component, a Transform called FinalDelimitedSubstring.