Load into Fixed length file
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 62
- Joined: Sat Mar 07, 2009 4:59 am
- Location: Chicago
- Contact:
Load into Fixed length file
Hi,
I have a delimited file , I am reading that through sequential file stage after that I am using aggregator,lookup and transformer as a part of transformation process, Finally I want to load into a fixed length file , How can I load into this file and what are the options to select in sequential file stage at target level.
I have a delimited file , I am reading that through sequential file stage after that I am using aggregator,lookup and transformer as a part of transformation process, Finally I want to load into a fixed length file , How can I load into this file and what are the options to select in sequential file stage at target level.
Suresh Reddy
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Re: Load into Fixed length file
If the file's table definition is defined as fixed length columns, then the load will be automatically done.
Firstly, Did you try loading data in to the target file?
Thanks,
Tony
Firstly, Did you try loading data in to the target file?
![Question :?:](./images/smilies/icon_question.gif)
Thanks,
Tony
-
- Participant
- Posts: 62
- Joined: Sat Mar 07, 2009 4:59 am
- Location: Chicago
- Contact:
The job is running successfull and the data is also loaded into file but not in the format i expected.
As you said there is no option directly saying that fixed length, there are 8 columns in my output, the sum of length of all columns is 112, so i mentioned 112 as a value in the option record length which is there in the format tab of sequential file stage, still the data is not coming as expected.
The data is coming like this.
Batch_ID file_name stage_count target_count status
1 address 23 34 success
2 client 34 23 failure
But i need the data like
Batch_ID file_name stage_count target_count status
1 address 23 34 success
2 client 34 23 failure
As you said there is no option directly saying that fixed length, there are 8 columns in my output, the sum of length of all columns is 112, so i mentioned 112 as a value in the option record length which is there in the format tab of sequential file stage, still the data is not coming as expected.
The data is coming like this.
Batch_ID file_name stage_count target_count status
1 address 23 34 success
2 client 34 23 failure
But i need the data like
Batch_ID file_name stage_count target_count status
1 address 23 34 success
2 client 34 23 failure
Suresh Reddy
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
-
- Participant
- Posts: 62
- Joined: Sat Mar 07, 2009 4:59 am
- Location: Chicago
- Contact:
My expected file is like the following
I am pasting again as the format in previous post is not proper
even if null is coming in any value it has to give spaces equal to length
I am pasting again as the format in previous post is not proper
even if null is coming in any value it has to give spaces equal to length
Suresh Reddy
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Re: Load into Fixed length file
In the table definition if the length of the columns are specified exactly, then the nulls also will have spaces equal to the length mentioned.antonyraj.deva wrote:If the file's table definition is defined as fixed length columns, then the load will be automatically done.
sureshreddy2009 wrote:The data is coming like this.
Batch_ID file_name stage_count target_count status
1 address 23 34 success
2 client 34 23 failure
But i need the data like
Batch_ID file_name stage_count target_count status
1 address 23 34 success
2 client 34 23 failure
Is there any difference between the requirement and the current output...
![Confused :?](./images/smilies/icon_confused.gif)
Thanks,
Tony
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 62
- Joined: Sat Mar 07, 2009 4:59 am
- Location: Chicago
- Contact:
But Null Filed Value () option will take the value common for all , Here i have different length for different columns so, How can I mention the null field value specific to fields.I am stilll working on it to get desired output.
Basically my need is i am creating a file using datastage and that file contians 8 columns ,first one is batchid and next 5 columns are count columns and last 2 columns are varchar fields .
after datastage one script is running to mail the contents of this mail as body in the mail, the body in the mail is in jumbled format and not in a readable format.so I decided to load in a fixed length format to view the report perfectly when we get the mail , this is a audit balance report for checking the record count
Basically my need is i am creating a file using datastage and that file contians 8 columns ,first one is batchid and next 5 columns are count columns and last 2 columns are varchar fields .
after datastage one script is running to mail the contents of this mail as body in the mail, the body in the mail is in jumbled format and not in a readable format.so I decided to load in a fixed length format to view the report perfectly when we get the mail , this is a audit balance report for checking the record count
Suresh Reddy
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
-
- Participant
- Posts: 62
- Joined: Sat Mar 07, 2009 4:59 am
- Location: Chicago
- Contact:
But Null Filed Value () option will take the value common for all , Here i have different length for different columns so, How can I mention the null field value specific to fields.I am stilll working on it to get desired output.
Basically my need is i am creating a file using datastage and that file contians 8 columns ,first one is batchid and next 5 columns are count columns and last 2 columns are varchar fields .
after datastage one script is running to mail the contents of this mail as body in the mail, the body in the mail is in jumbled format and not in a readable format.so I decided to load in a fixed length format to view the report perfectly when we get the mail , this is a audit balance report for checking the record count
Basically my need is i am creating a file using datastage and that file contians 8 columns ,first one is batchid and next 5 columns are count columns and last 2 columns are varchar fields .
after datastage one script is running to mail the contents of this mail as body in the mail, the body in the mail is in jumbled format and not in a readable format.so I decided to load in a fixed length format to view the report perfectly when we get the mail , this is a audit balance report for checking the record count
Suresh Reddy
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
In your target Sequential File stage, go to the Columns tab, right click on a column and choose 'Edit Row'. Then specify the Null Field Value for each column under the Nullable properties. As noted, specify the null field value for each column with the same number of characters as the lengthHow can I mention the null field value specific to fields.