How to create Schema to validate different types of record formats in one flatfile?

Requirement is to create schema to parse below file formats

HDDRSSS123ASSHH [fixed record length say 25,record Identifier may/maynot be present]
123456789AABBBBCCCDDD [fixed record length say 50, NO record Identifier, set maxrepeate Unlimited]
123456789AABBBBCCCDDD [fixed record length say 50, NO record Identifier ]
123456789AABBBBCCCDDD [fixed record length say 50, NO record Identifier ]

TRAILER123456789875645 [fixed record length say 35,record Identifier may/maynot be present]

So there will be 3 record formats in my file
a)Header record with fixed length of 25, record Identifier may or maynot be present.
b)Data Record with lenght 50, NO record Identifier and with property maxrepeate Unlimited. and
c)Trailer record with fixed length of 35, record Identifier may or maynot be present.

I have created a dictionary for data Record( so that I can set this dictionary in property while creating the schema)

Confusion is how to create schema to validate these requirements in the files?can all these will be done in one schema? when i try to set Default Record, I get many recordwithNoID’s attached to all the records.No Idea how to go about it? please help

Yes you are in the right direction with recordWithNoID option but would have to play with your file format hierarchy and do some R&D ofcourse it will be under one schema for your parsing to work.

HTH,
RMG

thanks rmg for reply.

Now questions are –

a) What record should be created as dictionary, so that I can set that in my schema?
say . i have 3 records formats(header,data and trailer). so in my dictionary, should I mention all the three records?
if yes, which record (out of these 3) I should make as default record in my schema? if possible, please tell me in detail.

b)If i set the default record in my schema, all of my record gets appended by recordwithNoID. What to do if I want recordwithNoID only for my data record, not for my header and trailer record?

Thank you once again.
Rakesh

For this format kind just straight go with FF Schema and not use as Dictionary reference.

What option you selected Delimiter and record as CarriageReturn?

Yes recordWithNoID for all records make sure there is no typo/case sensitive here (recordWithNoID)

Hi rmg,

I am selecting Delimiter option while creating the schema and the record separator is NewLine, nothing else.

recordWithNoID gets appended automatically and it is correct. I hope questions which I have mentioned in my previous post is clear (please let me know if it is not clear). Please let me know the answers for them.

thanks again.
Rakesh

. i have 3 records formats(header,data and trailer). so in my dictionary, should I mention all the three records?
if yes, which record (out of these 3) I should make as default record in my schema? --> starting Header record should be default record

Make sure you have the hierarchy level as in the below or you can adjust it based on how the flatfile:convertToValues parsing works.

HDDR (max repeat single) ---- this will be recordWithNoID - if you aware of exact identifier is not defined/unknown
—>Line Level (max repeat unlimited) recordwithNoID - if you aware of exact identifier is not defined/unknown
—>Trailer( max repeat single) - if you aware of exact identifier is not defined/unknown

HTH,
RMG

Hi rmg,

As you suggested, I am creating the structure like below:

HDDR
Data Record
Trailer Record

do we have any explanation why to keep Data record and Trailer record inside the Header record?
Is there any other document which discuss more on flat file structure (with recordWithNoIDs concepts) and talk about the hierarchy level, (since I donot see much discussion on this topic in basic flat file structure pdf)?

thank you so much for your help.
Rakesh

That is how the hierachy should be the way the parsing service works (convertToValues).

No I don’t think or seen any direct documentation on recordWithNoId concepts…except you may deep dive on the FlatFile Adapter’s user guide which you already :smiley: .

HTH,
RMG

Hi rmg,

as discussed I have created the schema(pls refer attachment)-

I have one requirement saying every record must end at 23 position. i.e. each of my data record length is 23.

with this schema, convertToValue, validate all the files(even record length >50) i.e. isValid is true in all the cases. And it ignores all the data present after 23 position in the file.

please let me know how to validate the length of record? if any record has data after 23th position (for header and trailer, it is 33), we should be in position to catch that record and push it into different file (say error file)

thank you once again.
Rakesh

May be you have to select a different type of schema for doing the record validation ie Fixed Length rather than Delimiter based…

When you validate it validates against schema and so first it should be handled by FF Schema definition it self.

Can you try/test that FL option?

HTH,
RMG

Sure, I will try to create one in Fixed length.

so… do you mean that we can not perform validation for record length in Delimiter option when record Identifier is not present?

here requirement is HDDR/Trailer record length is 33 and data record length is 23. is it possible to do the check for HDDR/DATA/Trailer record length in one Fixed length schema?

Not possible as far I know…as it’s all need to be dealt with in the FF schema definition and FL should handle it.

But if we create Fixed Length Schema, we will not be able to validate different record lengths in one schema for different records(say for Header record length should be 33,
for data record - record length should be 23, and
for trailer record - record length should be 40.

can we do all check in one schema (Fixed length schema)? note - I do not have record identifier in my records.

If not , what is the solution you can think of for my requirement?please advice.

If you create schema for each record definition how can work on the parsing?

It should be under the same schema and each record will be fixed length position and hierachy also…

Can’t you define record definition for FL you mean?

for Header record - record length maximum can be of 33,
for Data record - record length maximum can be of 23,
for Trailer record - record length maximum can be of 40,

my question is when we define Fixed Length Definition, we have record length option just in one place. SO that means schema validation will expect all the records of that length only. So if we define record Length = 23, schema will not accept the HDDR and Trailer record.

but requirement is all records(Hddr, data and Trailer) have condition of different record length. So Should I still use Fixed length Schema to validate this?

My Idea-

I was thinking of going with Delimiter option only.
And to evaluate record length, I will have a dummy field defined stating with position in my records (HDDR, Data and Trailer records).
After parsing it, if these dummy fields will contain any value, I will state them as error record in my flow service logic.

please let me know your suggestion.

Yes your work around option also should work with customization and error handling mechanism via a formatService (specification) option on that particular field.

Please test with FL option also and see if that is feasible for you.

HTH,
RMG