DataLayer.CSV
Developers often need to read data from text files in CSV (comma-separated values) format. Prior to the 9.1 release of Logi products, a special database connection using the Microsoft Jet driver was used to access this data. Now developers can instead use a datalayer element dedicated to the task, DataLayer.CSV. This topic discusses this new datalayer.
Attributes
The DataLayer.CSV element has the following attributes:
| Attribute | Description |
|---|---|
CSV File | (Required) Specifies the fully-qualified file system path and filename for the data file. The token @Function.AppPhysicalPath~ can be used to get the application folder path. |
| ID | (Required through v10.1.46) The unique element name. |
| Column Names | If left blank and if the Column Names In First Row attribute is set False or is blank, columns retrieved into the datalayer will be assigned default names in the form: Column1, Column2, Column3, etc. If, however, an optional comma-separated list of custom datalayer column names is specified here, they will override the default names. Or, if the Column Names In First Row attribute is set True, valid text names from the first data row will automatically be assigned as the datalayer column names, overriding the default names. |
Column Names In | Specifies if the text in the first data row should be used as the column names in the datalayer. |
| CSV Delimiter | Specifies the character used to delimit the columns of data in the .CSV file. As of v10.0.259, you may use \t = Tab, \v = Vertical Tab, \r = Carriage Return, \n = Line Feed, or Space = blank space. |
| Date Columns | Specifies a comma-separated list of column names of those columns that should be formatted as DateTime data. The names entered here should match those specified using earlier attributes for the datalayer column names, e.g. these may be the default names: Column1, Column5, etc. or the custom names specified in the Column Names attribute, or the names read from the first row of data. |
| Text Qualifier | Specifies the character used to qualify data that is to be handled as text. The default character is the double-quote. When data is read into the datalayer, the text qualified character is removed from the beginning and end of a column (and should therefore always occur in pairs). If the qualifier character is doubled within the column, it will be replaced with a single instance of the character. |
Work with DataLayer.CSV
In most respects, DataLayer.CSV functions exactly as other datalayer elements do and its data can be filtered and conditioned using appropriate elements. One major difference, however, is that there is no need to use a Connection element in the _settings definition with this element. The DataLayer.CSV element directly accesses the file system to read .CSV files.
As shown above, a DataLayer.CSV element is added as a child element to a data table or other data container element. Its attributes as set so that it accesses the desired .CSV data file, and so it handles column names as desired.
The datalayer reads and caches the data from the .CSV file. You can add child elements beneath the datalayer to affect its contents, including:
- Filtering: Sort, group, or restrict the data
- Extending: Add virtual columns to the datalayer that contain aggregated, calculated, or totaled data values
- Securing: Limit access to the data using Logi security
- Linking: Make the results reusable elsewhere in your report definitions
The use of many of these elements is described in separate DevNet documents.
Data read into the datalayer is cached in XML format in memory and/or on the web server's file system. The latter is discussed in The Logi Server Engine and may be of interest to developers working with extremely large datasets or large numbers of concurrent users.
The data read with the datalayer is available using @Datatokens, in the format @Data.ColumnName~. The spelling of the column name is case-sensitive. The data is only available within the scope of the parent element of the datalayer, not throughout the entire report definition. The DataLayer.Linked element can be used to make the data reusable in another datalayer outside this scope.
In Logi Info, the Auto Columns element can be used to quickly display in your report all the data in a datalayer.
The data retrieved into the datalayer can be viewed by turning on the Debugging Link in your _settings definition (General element) and using the resulting link at the bottom of your report page to view the Application Trace page. A link on the Trace page will display the retrieved data.
Use Studio's DataLayer Wizard
Beginning in v10.0.299, Studio includes a wizard that can assist you in configuring DataLayer.CSV.
As shown above, the wizard can be started by selecting and then right-clicking the parent element under which you want to add the datalayer, and using the context menus to select "Add a CSV DataLayer". The wizard will open; use it as follows:
- Enter (or browse to) the fully-qualified path and filename of the CSV file to be used. Note that this may be an MS Excel file. Click Next to continue.
- Indicate whether the file has column names in the first row, by selecting Yes or No. Click Next to continue.
- Click Finish to insert the datalayer.
- The wizard will insert the datalayer and configure its attributes, as shown above. Other optional attributes have to be configured manually.