Importing Fixed-Width Files into VDM

Fixed-width files store each field in a defined number of character positions rather than separating fields with commas or tabs. VDM can read these files as Database Files when you supply the field order and lengths.

Trouble Viewing Images? Right-click an image and select Open in new tab to view a larger version. You can also zoom in using Ctrl + Mouse Wheel.

Before You Begin

• Obtain the file layout, including the order, length and data type of each field.
• Confirm whether the file contains a header record.
• Use a copy of a small representative file for the first test.
• Make sure the source file is accessible to the Windows account running VDM.

Note: Database Files rely on file-system access and are not supported in WebReports. For delimited files, use the separate Database Files guide.

Create the Database File

1. Open Tables and Fields.
2. Right-click Database Files and select Add From Text File, then Fixed Width.
3. In File Options, select the appropriate File Selection. Use Single for one file.
4. Select the source File.
5. Set Include Headers according to the file layout.
6. Enter a descriptive Name under Table Options.

Define the Fields

In the field grid, enter each source field in the order in which it appears. Use the Add, Remove, Insert Above and Insert Below controls as needed.

Field positions follow from the order and Length values. You do not enter a separate starting-position value. Include padding and any unused positions in the layout rather than omitting characters between fields.

Note: Fixed-width import uses the field definition you enter. The delimited-file Preview procedure is not used to infer fixed-width boundaries.

Example: An 18-Character Record

The following demonstration has two records and no header. Spaces after the category are part of the file:

A0001North     012
A0002South     005

The three Length values total 18. Keep the source records aligned to those widths. Quantity is an integer, so the example values 012 and 005 are read as 12 and 5 rather than preserving their leading zeros. Use a text data type when leading zeros are meaningful identifiers.

Test the Import

1. Select Test Import.
2. Review the status message. If a row, field or value is reported, compare it with the source layout.
3. Correct field order, Length values or data types as needed.
4. Test again until the file passes validation.
5. Select Finish to complete the definition.

Test Import validates the input against the definition. Passing validation does not establish that a field was assigned the intended business meaning; compare the imported values with the original file.

Verify the Data in a View

1. Locate the new table under Database Files.
2. Add the required fields to a View.
3. Run the View and compare several rows with the source file.
4. Confirm that each field starts and ends in the intended position and that numeric and date values are interpreted correctly.
5. Save the View after verifying the result.

For the example, verify these values:

Refresh and Maintain the Definition

Enable Refresh on View Search when the View should read the current file contents on each search. If the source layout changes, edit the Database File definition and test it again before using it for reporting.

If fields appear shifted, first check the lengths and padding in the preceding fields. If a code loses leading zeros, review its data type. If another user or service cannot read the file, confirm its path and permissions for that account.

Related Articles

• How to Set Up and Use Database Files in VDM
• VDM System Requirements and Supported Platforms
• How to Use Data Grids in VDM