Creating a Flat File Schema From a Sample File

When you create a flat file schema, keep the following points in mind:

You must have Write access to the folder in which you want to store the flat file schema.

Ensure that the format of the sample file is *.txt.

If you want to edit the schema after the schema is generated, you can do so. To see the details, refer Editing a Flat File Schema.

To create a flat file schema from a sample file

1. In the Package Navigator view of Designer, click File > New > Flat File Schema.

2. In the New Flat File Schema wizard, select the folder in which you want to save the flat file schema.

3. In the Element name field, type a name for the flat file schema using any combination of letters, numbers, and/or the underscore character. For information about restricted characters, see About Element Names.

4. Click Next.

5. In the Select Creation Mode panel, select Create from a sample file.

6. Click Browse to navigate to and select the sample file from your computer.

The Preview pane displays the first twenty lines of the sample file.

7. Click Next.

8. In the Select Record Identifier panel, select any one of the following:

a. Yes if the record has a tag identifier.

b. No if the record does not have a tag identifier. In this case, set the record identifier in the Record Identifier text box. For more details on specifying a record identifier, see Specifying a Record Identifier.

If you select No, Designer creates a flat file dictionary corresponding to the new flat file schema under the same package with name as <SchemaName>_dictionary. For more information on flat file dictionary, see Creating Flat File Dictionaries.

9. Click Next.

10. In the Choose Record Parser Type panel, select one of the following to indicate how the data in the sample file is formatted:

Record Parser Type	Description
Delimited	Use this parser when each record is separated by a delimiter. See Step13.
Fixed length	Use a fixed length record parser when each record is of a fixed length (for example, mainframe punch or print records). This parser splits a file into records of the same pre-specified length. See Step 14.
Variable length	This parser expects each record to be preceded by two bytes that indicate the length of the record. Each record may be of different length. See Step 15.

11. In Start import at row, set the row number so that Designer discards the previous rows and displays the records from that row under the Preview pane.

12. Click Next.

13. If you selected Delimited as the parser type, specify the parser properties in the Delimited Record Parser panel. Designer updates the Preview contents based on your selections.

a. Use the following table to specify the delimiters used in the sample file.

Property	Description
Record	Character that separates records in a flat file document. Important: If a new line character (\n) exists at the end of each record and the Record delimiter is not ending with \n, then Designer cannot render the records properly under the Preview pane.
Field	Character that separates fields in a flat file document.
Subfield	Character that separates subfields in a flat file document.
Quoted release character	Character used to enable a section of text within a field to be represented as its literal value. Any delimiter characters that appear within this section will not be treated as delimiters. For example, your field delimiter is (,) and your quoted release character is “. When you want to use (,) within a field as text, you must prefix it with your quoted release character. When using the convertToValues service to create the strings Doe, John and Doe, Jane, the record would appear as “Doe, John”,“Doe, Jane”. When using the convertToString service to create “Doe, John”,“Doe, Jane”, the value of the record would be Doe, John and Doe, Jane. When using the convertToString service, if you have specified both the Release Character and the Quoted Release Character, the Quoted Release Character will be used.
Release character	Character used to enable a delimiter to be used for its intended, original meaning. The character following the release character will not be treated as a delimiter. For example, your field delimiter is + and your release character is \. When using + within a field as text, you must prefix it with your release character. When using the convertToValues service to create the strings a+b+c and d+e+f, the record would appear as a\+b\+c+d\+e\+f. When using the convertToString service to create a\+b\+c+d\+e\+f, the value of the record would be a+b+c and d+e+f.

b. Select Set first row as column name to choose the records on the first row as the column name of each record.

c. Click Next.

d. Use the Define the Schema panel to identify mandatory fields or delete fields,

To identify a field as mandatory, select the check box under the Mandatory column.

When you select a field as Mandatory, you must define a value for the field after the schema is generated. To specify the flat file elements, see Defining Flat File Elements.

To delete a field from the record, you can click Delete under the Actions column.

e. Click Finish.

Integration Server generates a flat file schema and Designer displays it in the Package Navigator view.

14. If you selected Fixed length as the parser type, specify the parser properties. Designer updates the Preview contents based on your selections.

a. To create fields and subfields for the record, choose Fields or Subfields under Create and use the indentation ruler under the Preview pane. Indentation ruler appears on a mouse hover. You can click on the Preview pane and select the location of each field.

The position of each field and subfield on the Preview pane is displayed under Field separators and Subfield separators.

b. You can set a value for Record length to adjust the record so that it appears correctly under Preview pane. Record length only supports positive integer values between one and total number of characters in the file.

Note:
Record length cannot be empty.

c. Select Set first row as column name to choose the records on the first row as the column name of each record.

d. Click Next.

e. Use the Define the Schema panel to identify mandatory fields or delete fields,

To identify a field as mandatory, select the check box under the Mandatory column.

When you select a field as Mandatory, you must define a value for the field after the schema is generated. To specify the flat file elements, see Defining Flat File Elements.

To delete a field from the record, you can click Delete under the Actions column.

f. Click Finish.

Integration Server generates a flat file schema and Designer displays it in the Package Navigator view.

15. If you selected Variable length as the parser type, specify the parser properties in the Delimited Record Parser panel. Designer updates the Preview contents based on your selections.

a. Use the following table to specify the delimiters used in the sample file.

Property	Description
Field	Character that separates fields in a flat file document.
Subfield	Character that separates subfields in a flat file document.
Quoted release character	Character used to enable a section of text within a field to be represented as its literal value. Any delimiter characters that appear within this section will not be treated as delimiters. For example, your field delimiter is (,) and your quoted release character is “. When you want to use (,) within a field as text, you must prefix it with your quoted release character. When using the convertToValues service to create the strings Doe, John and Doe, Jane, the record would appear as “Doe, John”,“Doe, Jane”. When using the convertToString service to create “Doe, John”,“Doe, Jane”, the value of the record would be Doe, John and Doe, Jane. When using the convertToString service, if you have specified both the Release Character and the Quoted Release Character, the Quoted Release Character will be used.
Release character	Character used to enable a delimiter to be used for its intended, original meaning. The character following the release character will not be treated as a delimiter. For example, your field delimiter is + and your release character is \. When using + within a field as text, you must prefix it with your release character. When using the convertToValues service to create the strings a+b+c and d+e+f, the record would appear as a\+b\+c+d\+e\+f. When using the convertToString service to create a\+b\+c+d\+e\+f, the value of the record would be a+b+c and d+e+f.

b. Select Set first row as column name to choose the records on the first row as the column name of each record.

c. Click Next.

d. Use the Define the Schema panel to identify mandatory fields or delete fields,

To identify a field as mandatory, select the check box under the Mandatory column.

When you select a field as Mandatory, you must define a value for the field after the schema is generated. To specify the flat file elements, see Defining Flat File Elements.

To delete a field from the record, you can click Delete under the Actions column.

e. Click Finish.

Integration Server generates a flat file schema and Designer displays it in the Package Navigator view.