Parsing fields from flat file
Moderators: chulett, rschirm, roy
Parsing fields from flat file
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
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
Pradeep Kumar
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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.
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.
Pradeep Kumar
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
- Does this tag have a specific format?pradkumar wrote: 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 repeat in a given Record?
Need more explaination on the Input you have and the output you want
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
Parsing fields from flat file
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] )
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] )
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:
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.
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)
NB this is for the last field only. How you extract other fields is up to you.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: