Appboard/2.4/builder/data sources/file

File based data adapters allow AppBoard to consume data from any files the server process has permissions to read, including network mounted filesystems.

The Shell Command adapter is a special case where AppBoard actually executes the specified command and then captures the response.

CSV File & Directory

Comma Separated Values (CSV) files are a common way to store tables of information and can be produced by a wide variety of applications and tools. The CSV File and Directory adapters allows AppBoard to consume data from these files including variations on these such as space and tab separated values.

Unlike other data adapters there is no specific caching options with the CSV adapters. Instead AppBoard will read and cache the response until next requested when it will check to see if the file has changed, if not then the cached copy is returned otherwise the file is re-read and the new data returned.

Configuration Options

Option	Description
Directory	Only applicable to the CSV Directory data adapter and defines the path containing the files to be read. See Specifying Files/Directories below for more information.
File Extension	Only applicable to the CSV Directory data adapter and defines the set of files AppBoard will look for in the defined directory, e.g. *.csv
File Path	Only applicable to the CSV File data adapter and defines the specific path and filename of the file to be read. See Specifying Files/Directories below for more information.
Data Delimiter	Defines the field separator which is comma by default, but can be set to semi-colons, spaces, or tabs.
Contains Header Line	Defines whether the file contains a header line, which should be the first row in the file. If no header is present the first row returned in discovery will be used to define the initial field names.
Header Meta Delimiter	See below for a further information on header meta data. This option specifies the meta delimiter used for each field to separate the actual field name from meta information. By default this is disabled but can be set to asterisk (*) or hash (#)
String Boundaries	For files that contain strings which could potentially include the data delimiter character the fields need to be further grouped by quotes so the parser doesn't incorrectly split the field. Typically this should be set to Delimited by Quotes even in the case quotes aren't used as this still behaves as expected (note that this approach is compliant with RFC 4180). Setting this to Not Delimited will specifically ignore quotes and split on every data delimiter found.

Header Meta Data

While it's simple to incorporate CSV data into AppBoard using the CSV data adapters, CSV files themselves do not define the data types of the columns. While this may be inferred AppBoard does not attempt to do this and the administrator needs to configure the types appropriately on the Explore step of the Data Source Wizard.

Starting with AppBoard 2.4 meta information describing the column types and primary keys can be encoded into the header line, removing the need to manually set the types and keys.

A Header Meta Delimiter must be configured for AppBoard to separate out the actual field name from the meta information, for example using an asterisk meta delimiter, and a comma data delimiter:

Name*STRING*PK,Description*STRING,Total*NUMBER,LastUpdate*DATE*yyyy-MM-dd

ExampleRow,Description Here,123,2013-06-24

The data types are the same as within AppBoard; BOOLEAN, NUMBER, LONG, INT, STRING, and DATE. In addition to the type, one or more fields can set the PK flag to indicate that particular field is a primary key. The DATE type also allows for the source date formatting to be defined using the same notation as within AppBoard

XLS File & Directory

The XLS data adapters are similar to the CSV adapters except they support reading from Microsoft Excel files.

Configuration Options

Option	Description
Directory	Only applicable to the XLS Directory data adapter and defines the path containing the files to be read. All files within this directory with the extention .xls are used. See Specifying Files/Directories below for more information.
File Path	Only applicable to the XLS File data adapter and defines the specific path and filename of the file to be read. See Specifying Files/Directories below for more information.
Sheet Name Regex	A regular expression to determine which sheet(s) to use from the XLS file(s). Each sheet will represent a separate entity.
Contains Header Line	Defines whether the a header row is present, which should be the first row in the file. If no header is present the first row will also be used to define the field names.

Shell Command

Typical usage for the Shell Command data adapter is to provide a generic integration method to AppBoard in cases where a more specific data adapter isn't appropriate. This adapter allows AppBoard to execute a command and capture the response which is expected to be in CSV format. As a result this adapter shares the CSV specific configuration options including support for header meta data - please refer to the CSV documentation above for more information.

Both static and dynamic variables (query SHIMs) can be provided to the script via command line parameters and setting environment variables. The combination of dynamic variables and CSV responses with meta information allows for tight integration with AppBoard.

Using the Shell Command data adapter means AppBoard will be spawning processes each time the data source needs to be queried. It's important to set reasonable cache timeouts to avoid excessive calls and factor in the additional load on the AppBoard server.

Configuration Options

Option	Description
Script	Defines the command to execute. See Specifying Files/Directories below for more information, however the default directory is [INSTALL_HOME]/server/webapps/enportal/WEB-INF/shell-script
Command Line Parameters	One or more command line parameters, these can include SHIM expressions.
Handle Standard Error (stderr)	Only command output to STDOUT is captured by AppBoard as the data response, while anything to STDERR is considered error messages and logged. This option determines the behaviour of the data adapter where by default this is set to Log and continue, in other words the response is always valid regardless of STDERR. The Log and break option means that on any STDERR output the response will be considered invalid.
Environment Variables	Define or override the environment provided to the script one variable per line in the format KEY=value. These can include SHIM expressions.
Inherit Server Environment	By default the executed command will inherit the complete environment from the AppBoard server along with any changes in the Environment Variables configuration. Setting this switch to No means only the environment defined in Environment Variables will be passed to the script. Using environment variables to pass sensitive information such as passwords is a better method than command line parameters.
Cache Timeout	How long the cache manager will keep a response before re-running the command.

Specifying Files/Directories

For all the adapters above when specifying the file path or directory a text input is shown with a purple magnifying glass next to it. By default the path shown is ${application.home}/data which is the equivalent of [INSTALL_HOME]/server/webapps/enportal/WEB-INF/data.

To have data files automatically included with full archives then place them under the [INSTALL_HOME]/server/webapps/enportal/WEB-INF/data/custom/ directory

The text input will accept any valid file path or directory as long as the AppBoard process has permissions to read it. For example, on Windows systems a valid entry could be C:\mydata\file.csv while on Linux and UNIX systems the following is valid /mydata/file.csv.

If the purple magnifying glass is clicked this will open the Remote File Browser dialog. Please note this browser is restricted to browse the [INSTALL_HOME]/server/webapps/enportal/WEB-INF/data directory only.

Remote File Browser

Accessing Files External to the AppBoard Server

AppBoard can connect to files that are not located on the AppBoard server. Sometimes it may take some experimentation to ensure that the path to the file is valid. There can be some trial and error involved to get a path that will work in a particular environment. For example, on a Windows server a path such as \\servername\path1\path2\filename.xls would work as long as that path mapping is valid.

When specifying the file path or directory in the Data Source configuration wizard, by default the path shown is ${application.home}/data/, which is the equivalent of [INSTALL_HOME]/server/webapps/enportal/WEB-INF/data/. If the purple magnifying glass is clicked, this will open the Remote File Browser dialog. This browser is restricted to browse under the [INSTALL_HOME]/server/webapps/enportal/WEB-INF/data/ directory only. However, the text input field will accept any valid file path or directory as long as the AppBoard process has permissions to read it. For example, on Windows systems a valid entry could be C:\mydata\file.csv, while on Linux and UNIX systems the following is valid /mydata/file.csv.

As an advanced technique, a symbolic link, or symlink, is a reference to another file or directory in the form of an absolute or relative path that affects pathname resolution. By creating a symbolic link from the default /data/ directory to another path, you can actually have AppBoard show the files from that other path in the Remote File Browser dialog.

For example, perform the following steps to create a Windows symlink to access a CSV data file from AppBoard:

Note: The paths in the following example assume that you have installed AppBoard in the directory c:\AppBoard and you have a CSV file located in c:\MyData\Files\Data.csv that you want to use in an AppBoard CSV Data Source

1. Create the following folder in your AppBoard file system: C:\AppBoard\server\webapps\enportal\WEB-INF\data\custom\Files\
2. Create the symlink from C:\AppBoard\server\webapps\enportal\WEB-INF\data\custom\Files\ to c:\MyData\Files\:
   1. Run a command line console window (cmd.exe) as an administrator
   2. Enter the following commands:
      1. > cd C:\AppBoard\server\webapps\enportal\WEB-INF\data\custom\Files\
      2. > mklink /d "C:\AppBoard\server\webapps\enportal\WEB-INF\data\custom\Files\AppBoard_CSV_Data" "C:\MyData\Files"
   3. Observe the message: symbolic link created for C:\AppBoard\server\webapps\enportal\WEB-INF\data\custom\Files\AppBoard_CSV_Data <<===>> C:\MyData\Files
3. Configure the AppBoard Data Source
   1. Follow the standard instructions for creating a CSV File Data Source
   2. When prompted for the data file name, enter: ${application.home}/data/custom/Files/AppBoard_CSV_Data/Data.csv

To have your data files automatically included with full archives, place them under the [INSTALL_HOME]/server/webapps/enportal/WEB-INF/data/custom/ directory. Files outside of the AppBoard file system are not preserved by the AppBoard backup utility. When upgrading AppBoard, or migrating AppBoard to another server, such files would need to be manually moved and configured so that any Data Source referencing the files will continue to function correctly.