Excel Db Store

Configuration

Since the Excel store needs a Directory File store, it is not possible to create an health check for the moment. Therefore, you should not put "HealthCheck": true in the configuration of an Excel store.

{
    "Name": "excel-file",
    "Type": "excel",
    "Config": {
        "ConnectionString": ""
    }
}

Change in 1.4.4

Before the 1.4.4, the ConnectionString could only point to one excel file at a time. It was problematic, since for some client the excel file name may include variable data such as the date.

Since the 1.4.4, it is possible to select multiple multiple file store by adding parameters to the ConnectionString :

• The fileStore Indicate the filestore name in which the excel are located

• The suffix indicate an eventual suffix to filter the files we want to retrieve. It is optional.

• The prefix indicate an eventual prefix to the excel file name that we want to retrieve. It is Optional.

Note that If the excel files are not at the root of the fileStore, but are in a sub-folder, it is necessary to include that in the prefix.

For example, if the excel are located as such : 

Then the prefix could be:

BulkInsertWorker

If the excelDbStore is used as a data source for the bulkInsertWorker, then the query field int the bulkInsertWorker. can contains specific instruction on how the excel file(s) will be read.

In the example abore, the query contains multiple parameters :

• "SELECT * FROM [Sheet1]" Is Mandatory. The Sheet1 must be replaced by the desired sheet name in the excel file.

• "HEADERS true" is Optional. Default to false. It indicated whether the excel file contains header, and this if we should ignore the first line of the excel(s) file(s)

  • If COLUMNCOUNT is not specified, the number of column is set to the number of headers found (1.4.7+).

• "EMPTYROWS false" is Optional. Default to true. It indicated whether we want to include the empty rows that are often found at the end of an excel file.

• "COLUMNCOUNT 2” is Optional. Force the number of column to a specific value (1.4.7+)

• “LINEOFFSET 3” is Optional. Skip the first 3 lines.

• “COLUMNOFFSET 2” is Optional. Skip the first 2 columns (1.4.7+).

Complete example :

SELECT * FROM [Sheet1] HEADERS true EMPTYROWS false COLUMNOFFSET 4 LINEOFFSET 10