Handling of White Spaces When Parsing Flat Files With Fixed Length

Assume you want to parse a fixed position flat file that contains a field for which the value "space" is a valid content and does not mean that this field is empty.

Have a look at the example flat file below. It contains 4 fields: customer (20), country code (2), country name (30) and order balance. For a better visualization, spaces are highlighted by an underscore sign.

Wishes_Unltd________F_France________________________
All you can buy Inc___Switzerland___________________

This can be a problem, because as per default, the flat file parser assumes that "space" is the fill character. The parser will strip off all trailing spaces before parsing the records to objects. If a field consists of the fill character only (as does country code in record two), the attribute will be returned as NULL.
You will get as a result:

{
	"customer" : "Wishes Unltd";
	"country_code" : "F";
	"country_name" : "France";
}
{
	"customer" : "All you can buy Inc";
	"country_name": "Switzerland";
}

Avoiding this is very easy. Set a default value on the affected attribute.

Figure: Setting a Default Value On a Flat File Attribute

After you have set a default value of e.g. "  ", the new result will be:

{
	"customer" : "Wishes Unltd";
	"country_code" : "F";
	"country_name" : "France";
}
{
	"customer" : "All you can buy Inc";
	"country_code" : "  ";
	"country_name": "Switzerland";
}

Please note, that this example will be a little different, if you additionally use padding, e.g. right(" ") on the affected attribute. Using padding will trim off leading or trailing spaces and result in an empty attribute instead of NULL or default value.