This file documents version 0.2.6 of ffe, a flat file extractor.
Copyright © 2007 Timo Savinen
Permission is granted to make and distribute verbatim copies of this manual provided the copyright notice and this permission notice are preserved on all copies.Permission is granted to copy and distribute modified versions of this manual under the conditions for verbatim copying, provided that the entire resulting derived work is distributed under the terms of a permission notice identical to this one.
Permission is granted to copy and distribute translations of this manual into another language, under the above conditions for modified versions.
The ffe program is a used to extract fields from flat files and to print them in different formats. The input file structure and printing definitions are defined in a configuration file, which is always required. Default configuration file is ~/.fferc (ffe.rc in windows).
ffe is a command line tool developed for GNU/Linux and UNIX systems. ffe can read from standard input and write to standard output, so it can be used as a part of a pipeline.
There is also binary distribution for windows.
One example of using ffe for printing personnel information in XML format from fixed length flat file:
$ cat personnel
john Ripper 23
Scott Tiger 45
Mary Moore 41
$
A file personnel contains three fixed length fields: `FirstName', `LastName' and `Age', their respective lengths are 9,13 and 2.
In order to print data above in XML, following configuration file must be available:
$cat personnel.fferc
structure personel {
type fixed
output xml
record person {
field FirstName 9
field LastName 13
field Age 2
}
}
output xml {
file_header "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n"
data "<%n>%t</%n>\n"
record_header "<%r>\n"
record_trailer "</%r>\n"
indent " "
}
$
Using ffe:
$ffe -c personnel.fferc personnel
<?xml version="1.0" encoding="ISO-8859-1"?>
<person>
<FirstName>john</FirstName>
<LastName>Ripper</LastName>
<Age>23</Age>
</person>
<person>
<FirstName>Scott</FirstName>
<LastName>Tiger</LastName>
<Age>45</Age>
</person>
<person>
<FirstName>Mary</FirstName>
<LastName>Moore</LastName>
<Age>41</Age>
</person>
$
ffe is a command line tool. Normally ffe can be invoked as:
ffe -o OUTPUTFILE INPUTFILE...
ffe uses the definitions from configuration file and tries to guess the input file structure using the first 10 000 lines or 1 MB of input data.
If the structure cannot be guessed the option -s must be used.
The format for running the ffe program is:
ffe option ...
ffe supports the following options:
-c file--configuration=file-s structure--structure=structure-p output--print=output-o file--output=file-f list--field-list=list-e expression--expression=expression-a--and-v--invert-match-l--loose-r--replace=field=valuedata.
-d--debug-?--help-V--versionAll remaining options are names of input files, if no input files are specified or - is given, then the standard input is read.
Expression can be used to select specific records comparing field values. Expression has syntax fieldxvalue, where x is the comparison operator. Expression is used to compare field's contents to value and if comparison is successful the record is printed. Several expressions can be defined and at least one must evaluate to true in order to print a record. If option -a is defined all expressions must evaluate to true.
If value starts with string file: then the rest of value is considered as a file name.
Every line in file is used as value in comparison. Comparison evaluates true if one or more values matches, so this makes possible use several different values in comparison. Note: The file size is limited by available memory because the file contents is loaded to memory.
Expressions can be defined as:
ffe uses a configuration file in order to read the input file and print the output.
Configuration file for ffe is a text file. The file may contain empty lines.
Commands are case sensitive. Comments begin with the #-character and end at the end of the line.
The string definitions can be enclosed in double quotation " characters.
char is a single character. string and char can contain following escape codes:
\a, \b, \t, \n, \v, \f, \r, \" and \#.
A backslash can be escaped as \\.
Configuration has two main parts: the structure, which defines the input file structure and the output, which defines how the input data is formatted for output.
Common syntax for configuration file is:
#comment
`command`
const name value
...
structure name {
option value ...
...
record name {
option value ...
...
}
record name {
option value ...
...
}
...
}
structure name {
...
}
...
output name {
option value ...
...
}
output name {
...
}
...
lookup name {
option value ...
...
}
lookup name {
...
}
...
Keyword structure is used to define an input file content. An input file can contain several
types of records (or lines). E.g. file can have a header, data and trailer record types. Records
must be distinguishable from each other, this can be achieved defining different 'keys'
(id in record definition) or having different line lengths (for fixed length structure) or different count
of fields (for separated structure) for different records.
Typically a structure maps to a file and a records maps to a line in the file.
A structure is defined as:
structure name {
option value ...
...
}
A structure can contain following options:
type fixed|separated [char] [*]quoted [char]header first|all|nooutput namerecord name {options ...}A record defines one type of input line in a file. Different records can be distinguished using
the id option or different line lengths or field counts.
A record is defined as:
record name {
option value ...
...
}
A record can contain following options:
id position stringA record definition can contain several id's, then all id's must match the input line
(id's are and-ed).
field name|FILLER|* [length]|* [lookup]|* [output]The last field of a fixed length input structure can have a * in place of length. That means that the last field has no exact length specified and it gets the remainder of the input line after all other fields. This allows a fixed record to have arbitrary long last field.
Length is also used for printing the fields in fixed length format (directive %D in output definitions).
If * is defined instead of the name, then the name will be the ordinal number of the field,
or if the header option has value first or all, then the name of the field will taken from
the header line (first line of the input).
If lookup is defined the fields contents is used to make a lookup in lookup table lookup. If length is not needed (separated format) but lookup is needed, use asterisk (*) in place of length definition.
If output is defined field will be printed using output definition output. If length and/or lookup are not needed use asterisk in place of them.
If field is named as FILLER, the field will not appear in output.
The order of fields in configuration file is essential, it defines the field order in a record.
field-count numberfield * number times. This can be used in separated structure instead of
writing sequential "field *" definitions. Several field-counts can be used in the same record and
they can be mixed with field.
fields-from recordfield and fields-from are mutually
exclusive.
output nameKeyword output defines one output format for formatting the flat file data. Formatting
is controlled using options and printf style directives. An output definition is all ways independent
from structure, so one output format can be used with different input file formats.
A output is defined as:
output name {
option value ...
...
}
Actual formatting and printing is controlled using pictures in output options. Pictures can contain following printf style directives:
%f%s%r%o%O%n%t%d%D%l%L%p%e%%Output options:
file_header picturefile_trailer pictureheader picture%n directive.
data picturelookup picturedata.
This makes possible to use different picture when the field is related to a lookup table. Default is to use the picture from data.
separator stringrecord_header picturerecord_trailer picturejustify left|right|chardata option is left or right justified.
char justifies output according the first occurrence of char
in the data picture. Default is left.
indent stringfield-list name1,name2,...no-data-print yes|nofield-list is defined and and this is set as no and none of the fields in field-list
does not belong to the current record, then the record_header and record_trailer are not printed.
Default is yes.
field-empty-print yes|noempty-chars.
If none of the fields of a record are printed, then the printing of record_trailer is also suppressed.
Default is yes.
empty-chars stringKeyword lookup defines a lookup table which can searched using field contents. Found values can
be printed using output directives %l and %L.
A lookup table is defined as:
lookup name {
option value ...
...
}
Lookup options:
search exact | longestexact.
pair key valuefile name [separator]default-value valueKeyword const defines one name/value pair which can be used as an additional output field.
Constants can be used only in field lists (option -f,--field-list, or output option field-list).
Constants can be used to add fields to output which do not appear in input. E.g. new fields for separated output or adding spaces after a fixed length field (changing the field length).
Note that value is printed as it is for every record. It cannot be changed record by record.
If a constant has the same name as one of the input fields, the value value is printed instead of the input field contents.
A constant is defined as:
const name value
When name appears in field list it is treated as one of the input fields having contents value.
Command Substitution allows the output of a command to replace parts of the configuration file. Syntax for
command substitution is:
`command`
The command is executed and the `command` is substituted with the standard output of
the command, with any trailing newlines deleted. Command substitutions may not be nested.
Before executing the command ffe sets following environment variables:
FFE_STRUCTUREFFE_OUTPUTFFE_FORMATFFE_FIRST_FILEFFE_FILESIf -s is not given, ffe tries to guess the input structure. ffe reads first 10 000 lines or 1 MB of input data and tries to match the structure definitions from configuration file to input stream. If all lines match one and only one structure, the structure is used for reading the input file.
Guessing uses following execution cycle:
id's are compared to the input line, if all id's of a record match
the input line and the
records line length matches the total length (or total count for separated structure) of the fields,
the record is considered to match the input line. If there are no id's,
only the line length or field count is checked.
At least in GNU/Linux ffe should be able to handle big files (> 4 GB), other systems are not tested.
Regular expression can be used in expressions (operator ? in option -e, --expression) only in systems where regular expression functions (regcomp, regexec, ...) are available.
Following examples use two different input files:
Fixed length personnel file with header and trailer, line (record) is identified by the first byte (H = Header, E = Employee, B = Boss, T = trailer).
$cat personnel.fix
H2006-02-25
EJohn Ripper 23
BScott Tiger 45
EMary Moore 41
ERidge Forrester 31
T0004
$
Structure for reading file above. Note that record `boss' reuses fields from `employee'.
structure personel_fix {
type fixed
record header {
id 1 H
field type 1
field date 10
}
record employee {
id 1 E
field EmpType 1
field FirstName 9
field LastName 13
field Age 2
}
record boss {
id 1 B
fields-from employee
}
record trailer {
id 1 T
field type 1
field count 4
}
}
Same file as above, but now separated by colon.
$cat personnel.sep
H,2006-02-25
E,john,Ripper,23
B,Scott,Tiger,45
E,Mary,Moore,41
E,Ridge,Forrester,31
T,0004
$
Structure for reading file above. Note that the field lengths are not needed in separated format.
structure personel_sep {
type separated ,
record header {
id 1 H
field type
field date
}
record employee {
id 1 E
field type
field FirstName
field LastName
field Age
}
record boss {
id 1 B
fields-from employee
}
record trailer {
id 1 T
field type
field count
}
}
Data in examples above can be printed in XML using output definition like:
output xml {
file_header "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"
data "<%n>%t</%n>\n"
record_header "<%r>\n"
record_trailer "</%r>\n"
indent " "
}
Example output using command (assuming definitions above are saved in ~/.fferc)
ffe -p xml personnel.sep
<?xml version="1.0" encoding="UTF-8"?>
<header>
<type>H</type>
<date>2006-02-25</date>
</header>
<employee>
<type>E</type>
<FirstName>john</FirstName>
<LastName>Ripper</LastName>
<Age>23</Age>
</employee>
<boss>
<type>B</type>
<FirstName>Scott</FirstName>
<LastName>Tiger</LastName>
<Age>45</Age>
</boss>
<employee>
<type>E</type>
<FirstName>Mary</FirstName>
<LastName>Moore</LastName>
<Age>41</Age>
</employee>
<employee>
<type>E</type>
<FirstName>Ridge</FirstName>
<LastName>Forrester</LastName>
<Age>31</Age>
</employee>
<trailer>
<type>T</type>
<count>0004</count>
</trailer>
Data in examples above can be loaded to database by generated sql commands. Note that the header and trailer are not loaded, because only fields `FirstName',`LastName' and `Age' are printed and `no-data-print' is set as no. This prevents the `record_header' and `record_trailer' to be printed for file header and trailer.
output sql {
file_header "delete table boss;\ndelete table employee;\n"
record_header "insert into %r values("
data "'%t'"
separator ","
record_trailer ");\n"
file_trailer "commit\nquit\n"
no-data-print no
field-list FirstName,LastName,Age
}
Output from command
ffe -p sql personnel.sep
delete table boss;
delete table employee;
insert into employee values('john','Ripper','23');
insert into boss values('Scott','Tiger','45');
insert into employee values('Mary','Moore','41');
insert into employee values('Ridge','Forrester','31');
commit
quit
This output format shows the fields suitable for displaying in screen or printing.
output nice {
record_header "%s - %r - %f - %o\n"
data "%n=%t\n"
justify =
indent " "
}
Output from command
ffe -p nice personnel.fix
personel - header - personnel.fix - 1
type=H
date=2006-02-25
personel - employee - personnel.fix - 2
EmpType=E
FirstName=John
LastName=Ripper
Age=23
personel - boss - personnel.fix - 3
EmpType=B
FirstName=Scott
LastName=Tiger
Age=45
personel - employee - personnel.fix - 4
EmpType=E
FirstName=Mary
LastName=Moore
Age=41
personel - employee - personnel.fix - 5
EmpType=E
FirstName=Ridge
LastName=Forrester
Age=31
personel - trailer - personnel.fix - 6
type=T
count=0004
Personnel data can be displayed as HTML table using output like:
output html {
file_header "<html>\n<head>\n</head>\n<body>\n<table border=\"1\">\n<tr>\n"
header "<th>%n</th>\n"
record_header "<tr>\n"
data "<td>%t</td>\n"
file_trailer "</table>\n</body>\n</html>\n"
no-data-print no
}
Output from command
ffe -p html -f FirstName,LastName,Age personnel.fix
<html>
<head>
</head>
<body>
<table border="1">
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>Age</th>
<tr>
<td>John</td>
<td>Ripper</td>
<td>23</td>
<tr>
<td>Scott</td>
<td>Tiger</td>
<td>45</td>
<tr>
<td>Mary</td>
<td>Moore</td>
<td>41</td>
<tr>
<td>Ridge</td>
<td>Forrester</td>
<td>31</td>
</table>
</body>
</html>
Printing only Scott's record using expression with previous example:
ffe -p html -f FirstName,LastName,Age -e FirstName^Scott personnel.fix
<html>
<head>
</head>
<body>
<table border="1">
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>Age</th>
<tr>
<td>Scott</td>
<td>Tiger</td>
<td>45</td>
</table>
</body>
</html>
Make all bosses and write a new personnel file printing the fields in fixed length format
using directive %D:
Output definition:
output fixed
{
data "%D"
}
Write a new file:
$ffe -p fixed -r EmpType=B -o personnel.fix.new personnel.fix
$cat personnel.fix.new
H2006-02-25
BJohn Ripper 23
BScott Tiger 45
BMary Moore 41
BRidge Forrester 31
T0004
$
The length of the fields FirstName and LastName in fixed length format will be made two bytes longer. This will be done by printing a constant after those two fields. We use dots instead of spaces in order to make change more visible.
Because we do not want to change header and trailer we need specially crafted configuration file. Employee and boss records will be printed using new output fixed2 and other records will be printed using output default.
New definition file new_fixed.rc:
const 2dots ".."
structure personel_fix {
type fixed
record header {
id 1 H
field type 1
field date 10
}
record employee {
id 1 E
field EmpType 1
field FirstName 9
field LastName 13
field Age 2
output fixed2
}
record boss {
id 1 B
fields-from employee
output fixed2
}
record trailer {
id 1 T
field type 1
field count 4
}
}
output default
{
data "%D"
}
output fixed2
{
data "%D"
field-list Emptype,FirstName,2dots,LastName,2dots,Age
}
Print new flat file:
$ ffe -c new_fixed.rc personel_fix
H2006-02-25
EJohn ..Ripper ..23
BScott ..Tiger ..45
EMary ..Moore ..41
ERidge ..Forrester ..31
T0004
$
Lookup table is used to explain the EmpTypes contents in output format nice:
Lookup definition:
lookup Type
{
search exact
pair H Header
pair B "He is a Boss!"
pair E "Not a Boss!"
pair T Trailer
default-value "Unknown record type!"
}
Mapping the EmpType field to lookup:
structure personel_fix {
type fixed
record header {
id 1 H
field type 1
field date 10
}
record employee {
id 1 E
field EmpType 1 Type
field FirstName 9
field LastName 13
field Age 2
}
record boss {
id 1 B
fields-from employee
}
record trailer {
id 1 T
field type 1
field count 4
}
}
Adding the lookup option to output definition nice.
output nice {
record_header "%s - %r - %f - %o\n"
data "%n=%t\n"
lookup "%n=%t (%l)\n"
justify =
indent " "
}
Running ffe:
$ffe -p nice personnel.fix
personel_fix - header - personel_fix - 1
type=H
date=2006-02-25
personel_fix - employee - personel_fix - 2
EmpType=E (Not a Boss!)
FirstName=John
LastName=Ripper
Age=23
personel_fix - boss - personel_fix - 3
EmpType=B (He is a Boss!)
FirstName=Scott
LastName=Tiger
Age=45
personel_fix - employee - personel_fix - 4
EmpType=E (Not a Boss!)
FirstName=Mary
LastName=Moore
Age=41
personel_fix - employee - personel_fix - 5
EmpType=E (Not a Boss!)
FirstName=Ridge
LastName=Forrester
Age=31
personel_fix - trailer - personel_fix - 6
type=T
count=0004
In previous example the lookup data could be read from external file like:
$cat lookupdata
H;Header
B;He is a Boss!
E;Not a Boss!
T;Trailer
$
Lookup definition using file above:
lookup Type
{
search exact
file lookupdata
default-value "Unknown record type!"
}
Command substitution can be used to make a configuration for reading any csv file. The number of fields will be read from the first file using awk. Input filenames and date are printed in the file header:
structure csv {
type separated ,
header first
record csv {
field-count `awk "-F," 'FNR == 1 {print NF;exit;}' $FFE_FIRST_FILE`
}
}
output default {
file_header "Files: `echo $FFE_FILES`\n`date`\n"
data "%n=%d\n"
justify =
}
structure personel_fix {
type fixed
record header {
id 1 H
field type 1
field date 10
}
record employee {
id 1 E
field EmpType 1 Type
field FirstName 9
field LastName 13
field Age 2
}
record boss {
id 1 B
fields-from employee
}
record trailer {
id 1 T
field type 1
field count 4
}
}
structure personel_sep {
type separated ,
record header {
id 1 H
field type
field date
}
record employee {
id 1 E
field type
field FirstName
field LastName
field Age
}
record boss {
id 1 B
fields-from employee
}
record trailer {
id 1 T
field type
field count
}
}
output xml {
file_header "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"
data "<%n>%t</%n>\n"
record_header "<%r>\n"
record_trailer "</%r>\n"
indent " "
}
output sql {
file_header "delete table boss;\ndelete table employee;\n"
record_header "insert into %r values("
data "'%t'"
separator ","
record_trailer ");\n"
file_trailer "commit\nquit\n"
no-data-print no
field-list FirstName,LastName,Age
}
output nice {
record_header "%s - %r - %f - %o\n"
data "%n=%t\n"
lookup "%n=%t (%l)\n"
justify =
indent " "
}
output html {
file_header "<html>\n<head>\n</head>\n<body>\n<table border=\"1\">\n<tr>\n"
header "<th>%n</th>\n"
record_header "<tr>\n"
data "<td>%t</td>\n"
file_trailer "</table>\n</body>\n</html>\n"
no-data-print no
}
output fixed
{
data "%D"
}
lookup Type
{
search exact
pair H Header
pair B "He is a Boss!"
pair E "Not a Boss!"
pair T Trailer
default-value "Unknown record type!"
}
ffe can be used to check flat file integrity, because ffe checks for all lines the line length and id's for fixed length structure and field count and id's for separated structure.
Integrity can be checked using command
ffe -p no -l inputfiles...
Because option -p has value no nothing is printed to output except the error messages.
Option -l causes all erroneous lines to be reported, not just the first one.
Example output:
ffe: Invalid input line in file 'inputfileB', line 14550
ffe: Invalid input line in file 'inputfileD', line 12
If you find a bug in ffe, please send electronic mail to tjsa@iki.fi. Include the version number, which you can find by running `ffe --version'. Also include in your message the output that the program produced and the output you expected.
If you have other questions, comments or suggestions about ffe, contact the author via electronic mail to tjsa@iki.fi. The author will try to help you out, although he may not have time to fix your problems.