Flat File Data Objects
A flat file data object contains the metadata for a flat file. Use a flat file data object as the starting point in a collaborative project. When you add a flat file data object, the Analyst tool connects to the network path location or the location where you upload the source flat file to extract metadata.
To add a flat file data object, you must select the flat file, configure the file options, and configure the column data types. After you add the flat file data object, you can preview its properties and column data.
You can add a flat file data object as fixed-width or delimited. When you add a flat file data object as fixed-width, you can format data by fixed-width column breaks. When you add a flat file data object as delimited, you can format data by delimiters such as commas for column breaks.
You can also synchronize the changes to the flat file data object to get the updated metadata if the source flat file changes.
Import Flat File Data Objects
You can add flat file data objects in the Analyst tool by importing the flat files into projects or folders. When you import a flat file data object, you can choose to upload a flat file from your local machine or you can choose a network path. Choose a network path to import a flat file data object if the flat file is greater than 10 MB.
When you upload a flat file from your local machine, the Analyst tool uploads a copy of the flat file to a flat file cache directory in the Informatica services installation directory that the Analyst tool can access. Contact an administrator to configure the flat file cache that the Analyst tool uses for the network path. When you choose a network path, you can specify the location to the flat file on your local machine.
You can synchronize the changes to the flat file data object if you modify the source flat file.
When you import a flat file data object, the Analyst tool infers the Numeric or String datatypes for flat file fields based on the first 10,000 rows.
Flat File Options
When you import a flat file data object, you can configure the flat file options for each column in Add Flat File wizard. The options that you configure determine how the wizard reads the data from the source flat file.
You can configure the following flat file options in the Add Flat File wizard:
- Code Page
- Code page of the data in the flat file object. Select a code page that matches the code page of the data in the flat file object.
- Delimiters
- Character used to separate columns of data. Use the Other field to enter a different delimiter. Delimiters must be printable characters and must be different from the escape character and the quote character if selected. You cannot select non-printing multibyte characters as delimiters.
- Text Qualifier
- Quote character that defines the boundaries of text strings. Choose No Quote, Single Quote, or Double Quotes. If you select a quote character, the wizard ignores delimiters within pairs of quotes.
- Column Names
Option to import column names from the first line. Select this option if column names appear in the first row. The wizard uses data in the first row in the preview for column names.
If the first row contains numeric characters, the wizard uses COLUMNx as the default column name. If the first row contains special characters, the wizard converts the special characters to underscore and uses the valid characters in the column name. The wizard skips the following special characters in a column name: " . + - = ~ ` ! % ^ & * ( ) [ ] { } ' \ " ; : ? , < > \ \ | \t \r \n. Default is not enabled.
- Values
- Option to start value import from a line. Indicates the row number in the preview at which the wizard starts reading when it imports the file.
Flat File Datatypes
Configure datatypes for the data in each column in the Add Flat File wizard. The datatypes you configure determine how the wizard imports the data from the source flat file.
Configure the following datatypes:
- •Bigint. You can specify the format in the Numeric Format window. You can use the default or specify another numeric format and choose to make this the default numeric format.
- •Datetime. You can specify the format in the Datetime Format window. You can use the default or specify another datetime format and choose to make this the default datetime format.
- •Double. You can specify the format in the Numeric Format window. You can use the default or specify another numeric format and choose to make this the default numeric format.
- •Int. You can specify the format in the Numeric Format window. You can use the default or specify another numeric format and choose to make this the default numeric format.
- •Nstring. You cannot specify a format.
- •Number. You can specify the format in the Numeric Format window. You can use the default or specify another numeric format and choose to make this the default numeric format.
- •String. You cannot specify a format.
Datetime Datatypes
When you configure the datetime dataype, you can specify the format in the Datetime Format window. You can use the default or specify another datetime format and choose to make this the default datetime format.
You can specify the following datetime format strings as part of the date:
- AM, a.m., PM, p.m.
- Meridian indicator. Use any of these format strings to specify AM and PM hours. AM and PM return the same values as a.m. and p.m.
- DAY
- Name of day, including up to nine characters. The DAY format string is not case sensitive.
- DD
- Day of month.
- DDD
- Day of year including leap years.
- DY
- Abbreviated three-character name for a day. The DY format string is not case sensitive.
- HH, HH12
- Hour of the day.
- HH24
- Hour of the day from 0-23, where 0 is 12AM.
- J
- Modified Julian Day.
- MI
- Minutes from 0-59.
- MM
- Month
- MONTH
- Name of month, including up to nine characters. Case does not matter.
- MON
- Abbreviated three-character name for a month. Case does not matter.
- MS
- Milliseconds from 0-999.
- NS
- Nanoseconds from 0-999999999.
- RR
- Four-digit year. Use when source strings include two-digit years.
- SS
- Seconds from 0-59.
- SSSSS
- Seconds since midnight.
- US
- Microseconds from 0-999999.
- Y
- The current year with the last digit of the year replaced with the string value.
- YY
- The current year with the last two digits of the year replaced with the string value.
- YYY
- The current year with the last three digits of the year replaced with the string value.
- YYYY
- Four digits of a year. Do not use this format string if you are passing two-digit years. Use the RR or YY format string instead.
Adding a Delimited Flat File
When you import a flat file data object to a project or folder, you can set delimiters to format the data. You can change column attributes to match the data preview.
1. On the New header, click Flat File Data Object.
The Add Flat File wizard appears.
2. Choose to browse for a location or enter a network path to import the flat file.
- - To browse for a location, select Browse and Upload and click Choose File to select the flat file from a directory that your machine can access.
- - To enter a network path, select Enter a Network Path and configure the path and file name of the file.
3. Click Next.
4. Accept the default Delimited option.
5. Click Next.
6. Configure the flat file options, and preview the flat file data.
Note: Select a code page that matches the code page of the data in the file.
7. Optionally, click the Refresh icon in the Preview panel to update preview changes to the flat file data.
8. Click Next.
9. Optionally, change the Column Attribute.
10. Click Next.
11. Configure the name, optional description, and the location in the Folders panel where you want to add the flat file.
The Flat Files panel displays the flat files that exist in a project or folder.
12. Click Finish.
The Analyst tool displays the data preview for the flat file on the Data Preview tab. View the properties for the flat file on the Properties tab.
Adding a Fixed-width Flat File
When you import a fixed-width flat file to a project or folder, you can set column breaks to format the data.
1. On the New header, click Flat File Data Object.
The Add Flat File wizard appears.
2. Choose to browse for a location or enter a network path to import the flat file.
- - To browse for a location, select Browse and Upload and click Choose File to select the flat file from a directory that your machine can access.
- - To enter a network path, select Enter a Network Path and configure the path and file name of the file.
3. Click Next.
4. Select Fixed-width.
5. Click Next.
6. Configure the flat file options, and preview the flat file data.
Note: Select a code page that matches the code page of the data in the file.
7. Optionally, click the Refresh icon in the Preview panel to update preview changes to the flat file data.
8. Choose to set, remove, move, or edit column breaks.
- - To set a column break, click within the Preview panel.
- - To remove a column break, double-click the column break.
- - To move column breaks, drag them.
- - To edit column breaks, click the Edit Breaks icon and use the Edit Breaks dialog box to modify column breaks.
9. Click Next.
10. Optionally, change the Column Attribute.
11. Click Next.
12. Configure the name, optional description, and the location in the Folders panel where you want to add the flat file.
The Flat Files panel displays the flat files that exist in a project or folder.
13. Click Finish.
The Analyst tool displays the data preview for the flat file on the Data Preview tab. View the properties for the flat file on the Properties tab.
Rules and Guidelines for Flat Files
Consider the following rules and guidelines while working with flat files:
- Upload small files to an Informatica services installation directory.
- Upload files up to 10 MB to an Informatica services installation directory on the machine where the Analyst tool runs. The Analyst tool accesses this location to extract flat file metadata that does not change frequently. When you use files of sizes up to 10 MB, the Analyst tool accesses a copy of the file in the Informatica services installation directory. If you modify the original file, you need to upload the file again.
- Upload large files to a network path location.
- Enable the Analyst tool to connect to a network path location for files greater than 10 MB. The Analyst tool accesses this location to extract flat file metadata that changes frequently. The network path location should be a shared directory or file system that the Analyst tool can access. When you use files greater than 10 MB, the Analyst tool can connect to the flat file in the network path. If you modify the original flat file, you must refresh the flat file in the Analyst tool. Refreshing metadata for a large flat file can take time.
- Blank data rows are not imported.
- The Analyst tool does not import the blank rows above the first data row, blank middle rows, and blank rows after the last data row when importing a flat file.
- Refresh the data preview.
- After a preview, you can change the row number at which the Add Flat File wizard starts reading when it imports the file. This row number corresponds with the preview. If you choose to import column names from the first line, refresh the preview to update the row numbers for the preview data.