Flat file services
Flat file services are used to define how a particular flat file (whether it's delimited, has fixed width columns, or is an Excel spreadsheet) is structured. They, in turn, are used by Gloop to determine how data should be parsed (read) from or serialized (written).
Once a flat file service is configured and saved, Martini will generate a Data model and a service. The model will contain the properties specified in the service1 while the service can be used to easily open your data, ready for Gloop to process. The generated service returns a Gloop cursor model array, which is then used to read or write the flat file data.
Creating a flat file service
To create a flat file service:
- Right click anywhere under a package's
code
folder and then select New > Flat File Service. Note that the folder you right-clicked on will be the default location of your to-be-created flat file service. -
In the appearing wizard, populate the following fields:
-
Location
Where the flat file service will be stored. By default this will be populated with the location that was right-clicked on.
-
Name
The name of the descriptor.
-
Type
Specifies how fields in the flat file are identified. There are three options:
-
Delimited
Choose this for flat files whose fields are delimited by specific characters (alternatively called separators). CSV and tab-delimited files are some examples:
1 2 3 4
James,Smith Thomas,Miller Sarah,Clark Elizabeth,Taylor
-
Fixed Width
Choose this for flat files which make use of fixed column widths to identify individual fields. If a value can't occupy the entire column, it is typically padded with spaces. For example:
1 2 3 4
James Smith Thomas Miller Sarah Clark Elizabeth Taylor
-
Spreadsheet (Excel)
Choose this for Excel spreadsheets that are in
.xls
or.xlsx
format. For example:
-
-
-
If the chosen Type is Excel and you have a file you wish to generate the flat file service from, click Next. Otherwise click Finish.
-
If you're on the Excel-specific screen in the create flat file wizard, choose an Excel file, and choose the sheet and first data rows. This will use the spreadsheet to create the flat file service for you.
Working with the flat file service editor
The editor has three tabs namely: Design, Preview, and Source.
Design tab
The Design tab is where you define the structure of the flat file.
The fields visible in the editor is dependent upon which type (separators versus fixed width delimitation) this descriptor is configured to handle.
Common configuration fields
The following fields are both present in services for character-delimited and fixed width flat files:
-
Type
The type of the descriptor.
-
Input First Data Row
The row number where reading of data should start. This field is handy if the data has header rows that contain the column names, and you wish to skip them.
-
Encoding
The text-encoding method used in the flat file.
-
Output Header Type
Determines whether headers are present or not in the data, when Gloop is writing it.
Option Description Column Names Acknowledge the first row of entries as property names Data There are no headers and data is displayed in the first row immediately.
Character-delimited
The following fields are specific to character-delimited flat file services only:
-
Column Delimiter
The character used to set fields apart fields in an entry.
-
Text Delimiter
The character used to quote text; set to double quotes (
"
) by default. -
Escape Character
The character used to escape special characters to allow them to be treated as ordinary characters.
-
Comment Maker
The character used to mark data as a comment.
-
Record Separator
The character(s) used to separate records.
-
Null String
The string to be used for
null
values. -
Quote Mode
Indicates how values are quoted in the flat file.
Option Description All
For quoting all columns. Minimal
For quoting only fields which contain special characters such as a delimiter, quote character, or any character used as a record separator. Non-Numeric
For quoting only non-numeric fields. None
For not quoting at all. When the delimiter occurs in a field, it must be preceded by the escape character. An exception will be thrown if any character that is unescaped but requires escaping is encountered. -
Allow Missing Column Names
Specifies whether missing column names are allowed when parsing the header (first row), if any. This is set to
false
by default. -
Ignore Empty Lines
Specifies whether to ignore empty lines; set to
false
by default. -
Ignore Surrounding Spaces
Specifies whether leading or trailing spaces would be ignored around values; set to
false
by default. -
Property
This is similar to the Data model editor, this is used to design the model that will represent the flat-file data.
Fixed width
The following fields are specific to fixed-width flat file service only:
-
Record Separator
The character(s) used to separate records.
-
Padding Character
Sets the character used to pad fields in a record.
-
Left Justified
Specifies whether the columns are left justified or not; set to
false
by default. -
Property
This is similar to the Data model editor, this is used to design the model that will represent the flat-file data.
Spreadsheet (Excel)
The following fields are specific to spreadsheet (Excel) flat file services only:
-
Sheet Number
The first sheet in the spreadsheet to process data from. This number is 1-based, meaning the first sheet is 1. If this is set to 0, then all sheets in the spreadsheet will be processed.
Preview tab
The Preview tab enables you to test the flat file service with sample data. Once it is fed data, it parses your data displays the results in a table. You can provide data by importing a flat file or by simply adding text in the provided text area.
Source tab
The Source tab shows the source code of the flat file service. It is written in JSON format, parsed by Gloop, and is used to define the flat file service.
Using a flat file service in a service
To use a flat file service in a Gloop, drag and drop the generated service to the service:
Once the generated service is added as an invoke step, set the arguments required in the call:
flatFileData
is the flat file to read or write to. This can be aFile
,InputStream
,OutputStream
,Reader
, orWriter
.type
is used to indicate whether to read or write data. Set this parameter's value toInput
if you need to read data orOutput
if you need to write data.
As said earlier, the output of the generated service is a Gloop cursor. If you've opted to read, the cursor will contain the entries in the flat file; when writing, however, the cursor should receive the entries you want to write to the file. In either scenarios, you would need to use an iterate step.
To read each entry, you must iterate over the cursor like you would an array. To do this, create an iterate step and
then set the cursor as the value of the iterate step's Input Array
property.
To write entries, you must create an iterate step as well; but this time, the Input Array
should be set to hold the
items you want to serialize and the Output Array
's value should be the cursor.
Quickly generate a flat file service call
To quickly generate Gloop code that reads or writes to a flat file:
- Press the key in the service editor.
- Type the name of the flat file service you want to process.
- Select the service to call by moving your arrow keys up or down and then finally pressing once your choice is final.