Importing fixed width files

Goal

This document describes how to access fixed width files.

Content

To access fixed-width files with information in a single row, a Delimited File data source can be used.  In the data source creation screen click on click on “Use fixed length” and define Column widths(bytes).  

To process a file in which the values have a fixed-width, it is recommended to select the “Use fixed length” option and provide the  values for the Column widths (bytes) text box.

Example:

A fixed-width file with information in a single row:

President                   Yearfirst inauguraPolitical Party

George Washington           1789None                        

John Adams                  1797Federalist                  

Thomas Jefferson            1801Democratic-Republican        

James Madison               1809Democratic-Republican        

James Monroe                1817Democratic-Republican        

John Quincy Adams           1825Democratic-Republican        

This file has three columns, the widths of the all three columns (from left to right)  are fixed to  28 char, 4 char and 29 char. So, the value of Column widths(bytes) used is : 28,4,29

A sample data source and base view for “Use fixed length” option is seen below:

It is also possible to access fixed-width files using a regular expression. In the data source creation screen click on “Use tuple pattern” and define a regular expression in the “Tuple Pattern” field.

Note that regular expressions in Denodo use a Java Pattern.  When creating the regular expression, external tools like the regular expression tester for Java can be useful.     

For the above example, the file has three columns.  The widths of the all three columns (from left to right)  are fixed at  28 char, 4 char,29 char. So this tuple pattern is used: (.{28})(.{4})(.{29})

When creating a Delimited File data source with “Use tuple pattern” option seen below:

References

Delimited File Sources

Regular Expression Tester for Java