How to create fixed length flat file with field separator and record separator?

, ,
1

Hi All,
Need an urgent help on the following flatfile issue.

As per my requirement, I am trying to process a fixed length flat file along with field separator as ‘|’ and record separator as ‘new line’. I have created the schema and it is parsing all the values as a single string(like 111222333444).

I try to explain with the example:

First Record
Field1: Value=111, length=3;
Field2: Value=222, length=3;
Field3: Value=33, length=3;
Field4: Value=444, length=3;
Output record length required=12

So my output file looks like:
11122233 444Rec2rec3rec4…

Excepted output:
111|222|33 |444
Rec1(separated fields by ‘|’ delimeter)
Rec2
Rec3
Rec4
.
.
.
Please let me know is there a way to define this in schema?

Thanks,
Smile

2

hi,
If i am not wrong, you are trying to generate a flatfile not parse a flatfile…

If you want to generate a flatfile, below steps would be the solution
In Schema, under flatfile defn tab, select the Record parser as Delimiter

Record
Character - new line
Field or Composite
Character - | (pipeline)

In the flow, while calling the ConvertToString service, you can mention the noEmptyTrailingFields set to false if required

let me know if u face any issues

-Senthil

3

Thanks a lot for your replay Senthil,I have to know how to create fixed length flat file along with field separator as ‘|’.

I will explain beter with the following example

EX:
var1=AAA; var2=BBB; var3=" " ; var4=DDD
and var1Length=3; var2Length=5; var3Length=5; var4Length=3

So my excepted output is : 

AAA|BBB  |     |DDD

after BBB two blank spaces, because var2 length=5 and 3rd 5 blank spaces

If I follow your steps, the output is : 

AAA|BBB||DDD

Could you please let me know if you have any idea.

Thanks,
Smile.

4

Let us consider the example that you posted here…

var1=AAA; var2=BBB; var3=" " ; var4=DDD
and var1Length=3; var2Length=5; var3Length=5; var4Length=3

In fixed length flatfile, record length is required… In this case, the record length is 16…

Using wm flatfile package, Fixed Length flatfile can be created… But not with delimiters…

Case I: Normal Fixed Length Flatfile without delimiters

Create dictionary - Field definition with positions starting from 0
In schema, set the dictionary through Properties window…
Select the Record parser as Delimiter and Record separater as New Line…

This brings an output like this for an input of 3 records…

AAABBB DDD
AAABBB DDD
AAABBB DDD

Case II: Fixed Length flatfile with delimiters

If you wanna output like AAA|BBB | |DDD, the record length is going to change… ie., 16+3 = 19 where | symbols are also considered as some fields… Different approach is required to achieve this…

  1. Create dictionary

RecordName
field1 0-4
field2 4-5 (This is for delimiters. While mapping other variables, hardcode it to |)
field3 5-10
field4 10-11 (delimiter)
field5 11-16
field6 16-17 (delimiter)
field7 17-20

  1. Create schema
    Choose Delimiter option
    Record Separater as New Line character
    In the flatfile tab, set the dictionary through Properties

This brings an output like below
AAA |BBB | |DDD
AAA |BBB | |DDD
AAA |BBB | |DDD

There may be different way also… Others can suggest their views…

-Senthil

5

Thanks a lot for your help Senthil .