Differences and Similarities compared to DFexport.rpc

DFexport and DFexport.rpc share many features yet they are also unique in several important ways.

General Approach

DFexport can export data in a plate-centric manner, like DFexport.rpc, using -Pnum, or in a module-centric manner that is unique to DFexport using -Mname or -Mnum. Many options are available to create data sets for a wide variety of needs. To get the most out of DFexport it can be useful to approach it's use with the following in mind:

Authentication and Database Permissions

Authentication.  To authenticate, DFexport requires the username and password for connection to a specific database server. These may be supplied as:

Specification of command-line options takes priority if both command-line options and environment variables are supplied. Refer to User Credentials for more information.

Database Permissions.  Subsequent to authentication with a specific database server, use of DFexport is allowed (or disallowed) by the Server - Export Data permission, or the intersection of both DFexplore - List view and DFexplore - Print/Save Data permissions. These permissions are granted to a role (and then user) by an administrator on a study-by-study basis (see System Administrator Guide, Server). An authenticated user with one of these role permissions will then be able to export data records from the set of site and subject IDs granted by their user permissions, which may further be limited by visit, plate and level restrictions associated with the user's role. DFexport provides no warning that some records cannot be exported due to permission restrictions as this would itself provide information about the existence of restricted data records. For roles without 'Show Hidden Fields' permission and fields with the Hidden or Masked property enabled, DFexport will output an empty (NULL) value when exporting such data fields, and it will also skip over reasons and queries attached to such fields.

Schema Listings

This feature is unique to DFexport and is not available in DFexport.rpc. DFexport is able to output database schema information. The available information includes plate numbers and module names, and may optionally also include field number, name, alias and data type for data fields in the requested plates or modules. This descriptive information can be useful reference material when a user wishes to further refine an export.

To obtain a listing of all defined plate numbers, use -listplates all. ^[1] To obtain a listing of all defined module names, use -listmodules all. In both cases:

%  DFexport -listmodules 100-200 10
Chemistry Eligibility Enrollment Header LabData PhysicalExam SocialImpact Urinalysis

Information about data fields in the specified plates or modules can be requested by additionally including the -listfields option. This option may only be used in conjunction with the -listmodules and -listplates options.

%  DFexport -listfields -listplates all 253
Plate 001: Blood Pressure Screening Visits
 Field  Name                       Alias                      Type
 -----  -------------------------  -------------------------  -------
    1   DFSTATUS                   DFstatus1                  Choice
    2   DFVALID                    DFvalid1                   Choice
    3   DFRASTER                   DFraster1                  String
    4   DFSTUDY                    DFstudy1                   Number
    5   DFPLATE                    DFplate1                   Number
    6   DFSEQ                      DFseq1                     Number
    7   ID                         ID001                      Number
    8   PINIT                      PINIT001                   String
    9   AGE                        AGE                        Number
   10   SEX                        SEX                        Choice
   11   RACE                       RACE                       Choice
   12   RACEOTH                    RACEOTH                    String
   13   S1DATE                     S1DATE                     Date
...

%  DFexport -listfields -listmodules all 253
Module: BloodPressure (ID=5022): Blood Pressure Readings
 Field  Name                  Alias                 Type     Used in Plates
 -----  --------------------  --------------------  -------  ------------------
    1   DFSTATUS              DFSTATUS              Choice   
    2   DFVALID               DFVALID               Choice   
    3   DFRASTER              DFRASTER              String   
    4   DFSTUDY               DFSTUDY               Number   
    5   DFPLATE               DFPLATE               Number   
    6   DFSEQ                 DFSEQ                 Number   
    7   DFPID                 DFPID                 Number   
    8   DFMNAME               DFMNAME               String   
    9   DFMID                 DFMID                 Number   
   10   DFMREF                DFMREF                Number   
   11   SBP1                  SystolicReading1      Number   1,5
   12   SBP2                  SystolicReading2      Number   1,5
   13   SBP3                  SystolicReading3      Number   1
   14   DBP1                  DiastolicReading1     Number   1,5
   15   DBP2                  DiastolicReading2     Number   1,5
   16   DBP3                  DiastolicReading3     Number   1
   17   SDAT                  ReadingDate           Date     1
   18   BPARM                 BPwhichArm            Choice   5
...

History of all Changes

This feature is unique to DFexport and is not available in DFexport.rpc. DFexport is able to output the history of all changes made to the data for a specific subject, visit or plate. The available information includes when the change was made, who made the change, what was changed, any reason associated with the change and any queries related to the data. The output is always to a file, in Excel format, and hence option -o outfile.xlsx is required.

To obtain a history listing use -history. The history of changes is specific to a single subject, and includes all data, query and reason changes for all data elements in records idenitfied by the subject ID. The output can be further filtered by visit, -visit #, and plate, -plate # .

DFexport is designed to export history of changes for exactly one subject. To export history for multiple subjects, DFaudittrace is available.

Data Source

Specifying the data source for the records to export is required. Data can be exported by plate number, using the -Pnum option, or by module, using either the -Mname or -Mnum option. Within any of these data sources, the default behavior is to export all of the defined fields. It is possible to further select the exported fields using one of the -Fname, -Falias or -Fnum options. Field name, alias and number information can be obtained from a previous invocation that includes the -listfields option.

Record Selection by Keys and Filters

There are many options available for selecting subsets of data records for export. Several of these options are similar or identical to their counterparts in DFexport.rpc. ^[2] In all cases, record selection and filtering is from the set of records allowed by the user's database permissions.

The available selection mechanisms and filters are:

%  DFexport -S server -U username -Pnum missed -plate 1-10 -o - 254

%  DFexport -S server -U username -Mname ESIG -plate 100-110,200 -o esigmodules.txt 123

plate#:$(fieldname) op value

-Mname eSignature -ALL '1:$(SEX) == 1 | 1:$(RACE) == 2 | 2:$(ELIG) == 1'

Extracting/Combining/Concatenating Fields for Output

Frequently, only a subset of the fields in the data source are of interest. These can be selected using one of the -Fnum, -Fname or -Falias options. Special handling of data fields that are defined in a module which is then instanced across more than one plate is required.

-Fnum #, #-#.  Select by field number. From each exported record, select for output only those fields requested by their number. Output fields can be re-ordered by specifying the field numbers in the desired order. Field numbers can be repeated. It is possible to request field numbers using NF notation which selects by field number relative to the last field of the record. Some examples of NF usage are:

This option may not be combined with -Fname or -Falias - doing so will cause DFexport to exit with an error message.

-Falias fieldname_list, -Fname fieldname_list.  Select by field name or field alias. From each exported record, select for output only those fields requested by their name or alias. Output fields can be re-ordered by specifying the field names or aliases in the desired order. Field names/aliases can be repeated. This option may not be combined with -Fnum - doing so will cause DFexport to exit with an error message. Similarly, only one of -Falias or -Fname may be specified - any other specification or combination will cause DFexport to exit with an error message.

-joinANY, -joinALL.  The default behavior of DFexport is to export data from one source, a plate or a module. However special handling is needed in the cases where a module instance is distributed across multiple plates. With the -joinALL and -joinANY options it is possible to combine and export data from a data source that is defined across plates in one invocation and into one output file. The join keys (the "join key triple") are always the subject ID, the sequence number and the module reference instance number and must match across data sources. These keys are always exported as the first three fields in each output record when joining occurs. Multiple data sources are specified using | as a delimiter. The specification for a single data source uses the notation:

plate#[fieldlist]

where:

The -joinANY and -joinALL options specify the resulting output behavior: with the -joinALL option, the database must contain a data record for every data source; otherwise, no data is output for that join key triple. Conversely, specifying -joinANY will export data so long as the join key triple appears in at least one data source.

-Mname DEMOGRAPHICS -joinANY '1[PINIT, SEX] | 2[RACE, RACE:d, RACEOTH] | 1[AGE, DOB:c]'

Concatenation with +.  By default, each selected field is output with optional formatting (see the section called “Output Formatting” and is separated from adjacent selected fields by the delimiter. Alternatively, selected fields can be concatenated, output with no delimiter at all. To specify concatenation, use the special concatenation symbol, +, between fields to concatenate. For example, to concatenate the site identifier and the subject identifier, use

DFSITE_ID+SUBJID

or to concatenate fields 9 and 12, use

9+12

It is also possible to concatenate a range of fields. In this case, concatenation has precedence over field range so,

8+9-12

or

8-11+12

or

8+9+10+11+12

are all equivalent. Note that there is no notation to concatenate a range of fields by specifying only the minimum and maximum field numbers; at least one of the numbers must be inidividually specified so that the concatenation operator can be used.

Including Metadata in Output

Study metadata is available for export as columns in each output record. Metadata is specified for inclusion in the output by inserting metadata keywords in the list of fields for export. In all cases, the exported value has the string data type.

The available metadata keywords are grouped by 6 categories: field, page, visit, image, site and study.

Output Formatting

Date Modifiers.  By default, date fields are output "as is" in their defined format. The options -c and -j may be used to cause imputation and to alter/normalize that defined format for all date fields that are exported. The -c (calendar format) option requests that any 2-digit year be replaced with a 4-digit year and imputation be applied. The -j (julian format) option requests that imputation be applied and dates be exported as their equivalent julian value - this is primarily useful for mathematical calculations and comparisons with other date values. On an individual field basis, it is also possible to override the defined format with field level date modifiers. By following the field number, name or alias specification with a :c or :j modifier, it is possible to export the same calendar or julian format result for a single field. The :o modifier requests the "original" date value, and can be useful where -c or -j has been previously specified. DateField and DateField:o produce the same result. Similarly, DateField:c also produces the same result if the field's format already specifies 4-digit years and no imputation is needed.

-Fname "VisitDate, VisitDate:o VisitDate:c, VisitDate:j"

A field level date modifier cannot be applied to a non-date field nor can it be applied to a range of field numbers, names or aliases; it may only be applied to a single field number, name or alias at a time.

Label Decoding.  For any field that is defined with coding, it is possible to output the decoded label for a field's value by appending the :d modifier to the field number, name or alias.

-Falias "sex, sex:d"
2|female

Any coded value that cannot be decoded is output as is. The modifier cannot be applied to a field that has no coding nor can it be applied to a range of field numbers, names or aliases; it must be applied to a single field number, name or alias at a time.

Header Record.  Specifying -h forces the inclusion of a descriptive record, the header, as the first row of output. If -h is specified with -D, the header contains each field's description. Otherwise, if -h is specified with -Fname, the header contains each field alias. If -h is combined with -Fnum, or no field selection criteria are needed:

During export it is possible to create one or more new data fields by including options to string split (see String Splitting), extract substrings (see Extracting Sub-Strings) or include fixed, literal values (see Literal, constant values). If -h is specified then these new fields also need names to appear in the header record. This is done with the -H header_list option. This option specifies the variable names to appear in the header record for any new fields. If header_list contains fewer names than there are new fields, the remaining fields are assigned names identical to their original field names.

String Splitting.  It is possible to split a string field into multiple, shorter string fields by appending a :numxcharscw modifier to a field number, name or alias selection. The modifier includes a specification of the number of fields to split the input string into (num), the literal x as a separator, the maximum width in characters of each output field (chars), and whether or not splitting should be done on character (c) boundaries or word (w) boundaries. If word boundaries are used, a word is delimited by any combination of space or tab characters. Word splitting will always split at the word boundary closest to but less than the maximum width. If there is no word boundary in the substring, the string will be split at the character boundary.

-Fname "comments:5x200c"

If a field to be split contains a missing value code, the first output field will contain the same missing value code and the remaining fields will be blank. If a field is split to create additional fields and a header record is requested with -h, field names for the new fields are identical to the original field name unless -H is also specified. If -H header_list is given, the field names for the new fields are assigned in order from the header_list.

Extracting Sub-Strings.  New fields can be created from substrings while exporting fields. The modifier notation :xS.L indicates that a substring is being extracted (x), starting at character position S (the first position is 1) and having length L characters. The modifier can be applied to any field number, name or alias selection.

New fields can be created from substrings while exporting fields. The modifier notation :xS.L indicates that a substring is being extracted (x), starting at character position S (the first position is 1) and having length L characters. The modifier can be applied to any field number, name or alias selection. Substrings can be extracted from any field type: string, date, time or numeric. Substrings are extracted from the string value equivalent of the field. Numeric field values are leading zero-padded to their store width before substring extraction is performed. ^[5]

-Fname "PID:x1.3,PID:x4.4"
005|0001
006|0401
123|2003

Literal, constant values.  A constant value may be inserted into the data export by including it in one of the -Fnum, -Fname or -Falias specifications. The value must always be enclosed with single-quotes, as in 'AB', to prevent any confusion with a matching field name or alias. To insert a blank field into the export, it must be represented by 2 adjacent single quotes, specifically ''.

-Falias "EnrollDate,'AD',SubjInit"
2014/12/12|AD|STN
2013/06/15|AD|PRP
2015/03/12|AD|KKW

Enclosed by single-quotes, DFexport ignores the special meaning of any characters that would otherwise be delimiters. For example, 'a,b' is the constant value a,b, not two separate constants. If -h is present, the name used in the header record is the value itself. If -H header_list is also present, the name used in the header record for each constant value will be the next value from header_list, if one is available; otherwise, the value itself will again be used.

-h -H "When" -Falias "EnrollDate,'AD',SubjInit"
EnrollDate|When|SubjInit
2014/12/12|AD|STN
2013/06/15|AD|PRP
2015/03/12|AD|KKW

Trailing Field Delimiter.  For backwards compatibility, exported data records can be terminated by a trailing | if one is not already present. This is specified with the -p option. ^[6] With the trailing delimiter, exported data records maintain the original DFdiscover record format so that they can be, for example, easily re-imported into DFdiscover in a subsequent step.

Missed Record Handling.  Missed records are include in the export if either -status all or -status missed are specified. To export missed records, there are two output options:

When missed records are exported along with one of the field selection options -Fnum, -Fname, or -Falias, the second output option is always used. If the option -L lostcode has not been specified, the standard DFdiscover missing value code (*) is automatically inserted in each user-defined field of the exported record.

The lostcode specified with -L must be comprised of one or more alphanumeric characters. It is not permissible to use control characters. Also, the DFdiscover delimiter (|) may not be used, unless data is also being exported in CSV format with the -z option. Note that specifying the -L lostcode option will not, by itself, cause missed records to be exported. That is controlled entirely by the -status option. The -L option only indicates that the second, "normalized" output format is desired and is to be used with the specified substitution code.

Output Options

Output File.  By default, output from DFexport is written to standard output (the command-line output). To write the output to a specific file, specify the filename with -o outfile. The user must have filesystem permission to create or modify the file. If outfile is specified as a relative pathname, the file is created relative to the DFexport command invocation directory.^[7] It is also possible to be explicit that standard output is the output destination by specifying -o - although this is not necessary.

Output Mode.  Options -w and -a control the output mode. The output is written (-w) or appended (-a) to the output file. This option is ignored if exported records are written to standard output rather than a file. If the output file does not exist, it is first created. If the output file already exists, it is either overwritten or appended to, depending upon the output mode.

Error Output.  Re-direct any error messages to a specific file with the -e errorfile option. By default, error messages are written to standard error and hence would get intermixed with data if the data is being exported to standard output.

Comma-Separated Values (CSV) Format.  CSV is a popular format used for sharing data records between different software programs. By including the -z option, DFexport will generate output that is compliant with this format. The unique requirements of the format are:

Exported records, in CSV or traditional (non-CSV), format are always delimited by a newline character.

If the -h option is also specified, the CSV requirements are applied to the header record and all of the values in the header.

Exit Status

DFexport exits with status 0 if the command was successful. Exit status 1 indicates that an error occurred - errors are written to standard error or the errlog file if -e was specified.

Examples

In the following examples, only the options unique to the example are specified. For clarity, required options such as authentication credentials, output destination and study number are omitted.

-Pnum 6 -ANY "1:$(S1DATE) == 01/01/06"

-Mname BASE
73|180||
||120|70
65|145||
||110|65

-Mname BASE -joinALL "1[HEIGHT,WEIGHT] | 2[SYSTOLIC,DIASTOLIC]"
73|180|120|70
65|145|110|65

-Mname eSignature -expr '$(SigName) == "Jack"'

-Mname eSignature -expr 'tolower($(SigName)) ~ "jack"'

-Mname eSignature -pattern "jack"