Create a Flat File Data Object
You can create a flat file data object as an empty data object, from an existing flat file, or from a control file. You can create a delimited or fixed-width flat file data object using any method.
When you create a flat file data object, you can create the data object in the following ways:
- As an empty data object
- Create an empty flat file data object when you want to define the data object columns and rows in the Developer tool.
- From an existing flat file
- Create a flat file data object from an existing flat file when you have a flat file that defines the metadata you want to include in the data object.
- From a control file
- Create a flat file data object from a control file when you want to define the data object columns and rows based on a control file.
Creating an Empty Flat File Data Object
Create an empty flat file data object when you want to define the data object columns and rows in the Developer tool.
1. Select a project or folder in the Object Explorer view.
2. Click File > New > Data Object.
3. Select Physical Data Objects > Flat File Data Object and click Next.
The New Flat File Data Object dialog box appears.
4. Select Create as empty.
5. Enter a name for the data object.
6. Optionally, click Browse to select a project or folder for the data object.
7. Click Next.
8. Select a code page that matches the code page of the data that you want to process.
9. Select Delimited or Fixed-width.
10. If you selected Fixed-width, click Finish. If you selected Delimited, click Next.
11. Configure the following delimited format properties:
Property | Description |
|---|
Delimiters | Character used to separate columns of data. If you enter a delimiter that is the same as the escape character or the text qualifier, you might receive unexpected results. |
Text Qualifier | Quote character that defines the boundaries of text strings. If you select a quote character, the Developer tool ignores delimiters within a pair of quotes. |
12. Click Finish.
The empty data object opens in the editor. Define the columns for the data object in the Overview view.
Creating a Flat File Data Object from an Existing Flat File
Create a flat file data object from an existing flat file when you have a flat file that defines the metadata you want to include in the data object.
1. Select a project or folder in the Object Explorer view.
2. Click File > New > Data Object.
3. Select Physical Data Objects > Flat File Data Object and click Next.
The New Flat File Data Object dialog box appears.
4. Select Create from an existing flat file.
5. Click Browse and navigate to the directory that contains the file.
6. Click Open.
The wizard names the data object the same name as the file you selected.
7. Optionally, edit the data object name.
8. Optionally, click Browse to select a project or folder for the data object.
9. Click Next.
10. Select a code page that matches the code page of the data that you want to process.
11. Select Delimited or Fixed-width.
12. Optionally, edit the maximum number of rows to preview.
13. Click Next.
14. Configure the format properties, based on whether the flat file is delimited or fixed-width.
- - For a delimited flat file, configure the following properties:
Property | Description |
|---|
Delimiters | Character used to separate columns of data. If you enter a delimiter that is the same as the escape character or the text qualifier, you might receive unexpected results. |
Text Qualifier | Quote character that defines the boundaries of text strings. If you select a quote character, the Developer tool ignores delimiters within pairs of quotes. |
Import Column Names From First Line | If selected, the Developer tool uses data in the first row for column names. Select this option if column names appear in the first row. The Developer tool prefixes "FIELD_" to field names that are not valid. |
Row Delimiter | Specify a line break character. Select from the list or enter a character. Preface an octal code with a backslash (\). To use a single character, enter the character. The Data Integration Service uses only the first character when the entry is not preceded by a backslash. The character must be a single-byte character, and no other character in the code page can contain that byte. Default is line-feed, \012 LF (\n). |
Escape Character | Character immediately preceding a column delimiter character embedded in an unquoted string, or immediately preceding the quote character in a quoted string. When you specify an escape character, the Data Integration Service reads the delimiter character as a regular character. |
Start Import at Line | Row number at which the Data Integration Service starts reading when it imports the file. For example, if you specify to start at the second row, the Developer tool skips the first row before reading. |
Treat Consecutive Delimiters as One | If selected, the Data Integration Service reads one or more consecutive column delimiters as one. Otherwise, the Data Integration Service reads two consecutive delimiters as a null value. |
Retain Escape Character in Data | Includes the escape character in the output string. |
- - For a fixed-width flat file, configure the following properties:
Property | Description |
|---|
Import Column Names From First Line | If selected, the Developer tool uses data in the first row for column names. Select this option if column names appear in the first row. |
Start Import at Line | Row number at which the Data Integration Service starts reading when it imports the file. For example, if you specify to start at the second row, the Developer tool skips the first row before reading. |
Column breaks | Configures the column breaks in the fixed-width file. Click Edit Breaks to edit column breaks. Or, follow the directions in the wizard to manipulate the column breaks in the file preview window. You can move column breaks by dragging them. Or, double-click a column break to delete it. |
15. Click Next to preview the flat file data object.
16. Click Finish.
The data object opens in the editor.
Creating a Flat File Data Object from a Control File
Create a flat file data object from a control file when you want to define the data object columns and rows based on a control file.
1. Select a project or folder in the Object Explorer view.
2. Click File > New > Data Object.
3. Select Physical Data Objects > Flat File Data Object and click Next.
The New Flat File Data Object dialog box appears.
4. Select Create from a control file.
5. Click Browse and navigate to the directory that contains the control file.
6. Click Open.
The wizard names the data object the same name as the control file you selected.
7. Optionally, edit the data object name.
8. Optionally, click Browse to select a project or folder for the data object.
9. Click Next.
10. Select a code page that matches the code page of the data that you want to process.
11. Select Delimited or Fixed-width.
12. If you selected Fixed-width, click Finish. If you selected Delimited, click Next.
13. Configure the following delimited format properties:
Property | Description |
|---|
Delimiters | Character used to separate columns of data. If you enter a delimiter that is the same as the escape character or the text qualifier, you might receive unexpected results. |
Text Qualifier | Quote character that defines the boundaries of text strings. If you select a quote character, the Developer tool ignores delimiters within a pair of quotes. |
14. Click Finish.
The data object opens in the editor.