Data File Upload

Intellicus provides an option to browse and upload file, validate, initiate data flow processing and loading of data to ingest data into tables, queries, cubes and reports.
Intellicus’ Data Administrators can now incrementally load data into tables on a regular basis.

Create organizationId folder at ‘Jakarta/webapps/intellicus/client/config/’ to set up Upload templates.
- Set ‘configuration.properties’ file located at ‘Jakarta/webapps/intellicus/client/config/’ for ‘ReportEnginePath’ parameter in the last row as shown below.

Figure 1: Location of configuration.properties

Various templates are predefined in ‘FileUploadTemplate.json’ file placed at ‘Jakarta/webapps/intellicus/client/config/<organizationId folder>’. These are specified with certain details like format of file allowed to upload, no. of columns, fixed column names etc. Please refer below a sample snippet JSON file.
- Set up the ‘Upload’ folder within Installable folder under ‘ReportEnginedata’. This folder serves as a placeholder for uploading user provided files.

Go to Navigate > Design> Data File Upload (Alternately, this can be reached from the shortcut on Home page). On searching/selecting a template, allowed format for that template appears on portal.

Set up the Upload connection in Intellicus pointing to the ‘Upload’ folder as specified above. This connection is also used on ‘FileUploadTemplate.json’ against various templates.
- Next, select the file to be uploaded.

If file with same file name exists in Upload folder, confirmation for rename or overwrite existing file is shown.
On selecting Upload, the browsed file would be validated against certain set of validations for that template as defined in template JSON file.
On successful validation of uploaded file, file gets uploaded to Report Engine data folder. File history appears in uploaded file list on the same screen with action “Load Now” and “Delete” and status as “Uploaded”. These actions appear on mouse hover of the uploaded file.
File upload history displayed is maintained automatically in another JSON file ‘UploadHistory.json’ kept at ‘Jakarta/webapps/intellicus/client/output/<organizationId folder>’.
Success/error message appears on UI based on file validation criteria.
Set ‘PlaceholderParameterName’ if they are being used in underlying executable entity (query, report or job)
Once uploaded, Load Now button would open corresponding executable entity (query, report or job) page in new tab to execute it.
When user clicks the ‘Delete’ action, the uploaded file under ‘Upload’ folder will be deleted on confirmation.
On successful loading of data, updated list is shown on portal with updated action and status. You can filter the file list on the basis of Status as ‘Loaded’, ‘Uploaded’, ‘Loading Failed’ or ‘Deleted’.

A sample FileUploadTemplate.json is as below:

{
“Template1”: {
“Description”: “Configures whether the editor should re-indent the current line when a character is typed that might change its proper indentation (works only if the mode supports indentation). Configures whether the editor should re-indent the current line when a character is typed that might change its proper indentation (works only if the mode supports indentation).”,
“EntityInfo”: {
“EntityType”: “Query”,
“EntityId”: “159895434970026ad3a3e7-aeff-4742-8bfa-c7b8ea1a8f12643479”,
“DeleteOnSuccessfulLoading”: false,
“ConnectionName”: “DemoFileSource”,
“ConnectionLocation”: “../data/demofilesource”,
“PlaceholderParameterName”: “”
},
“ValidationCriteria”: {
“AllowedExtn”: “csv”,
“NoOfColumns”: “6”,
“Fix_Columns”: [“Salespersonid”,”Salesperson”,”Storeid”,”Month”,”Targetsales”,”Testcol”],
“CheckForNonEmptyFile”: “No”,
“CheckColumnNamesForMonth”: “No”,
“MinFileSize”: “0”,
“CheckFirstTenCharactersForDate” : “No”,
“CheckColumnsForSubset”: “NO”
}
},

Keys of the template are as follows:
Description – This stores the template description.
EntityInfo – This is a JSON Object containing the information about the entity associated with the template. Following are its attributes:
EntityType– It can be either query, report or job.
EntityId– This stores the entity id. In case of job EntityType, EntityId stores the job name.
ConnectionName – This stores the name of file source connection to be used.
ConnectionLocation – This stores the path to connection location.
DeleteOnSuccessfulLoading – If file is to be deleted after successfully loading it into the query object.
PlaceholderParameterName – A text type parameter needs to be created in repository and that parameter should be used in query objects as a placeholder to specify file. Whenever any uploaded file will be loaded using Load Now option then name of that file will be substituted in this parameter and its data will be processed as per the design of Query Object.
Validation Criteria– This is a JSON Object containing the information about the criteria against which a certain file needs to be evaluated. Following are its attributes:

AllowedExtn – This determines which extension files will be allowed to be uploaded. Currently supported extensions are XLSX, XLSB, TXT and CSV. It can contain single value or multiple values of extensions separated by a comma. This also supports password protected files and prompts end user to supply it as part of validation criteria before accepting the uploaded file.
NoOfColumns – It stores the column count. Number of columns of any browsed file should match with the count specified in this entry. If this criterion needs to be skipped, then either leave this entry as “” or specify “0”.
Fix_Columns – It stores the columns array. Columns of any browsed file must match with the columns specified in this entry in the same order as specified in JSON. If this criterion needs to be skipped, then specify empty column array – [].
CheckForNonEmptyFile – It checks for non-empty browsed file. If this criterion is to be used, then specify “Yes” and if this check needs to be skipped then either specify this entry as “” or “No”.
SkipColumnNamesWithMonth – If any column name contains month then that column will not be matched against the corresponding column present in fix_columns array in JSON. If it needs to be used, then specify “Yes” else leave this entry as “” or “No”.
MinFileSize – It checks the browsed file to be of size greater than the value present in this entry. This criterion will only be used for text files. To skip this criterion leave it as “” or “No” and to use it specify “Yes”.
CheckFirstTenCharactersForDate – This criterion is only to be used for text files. It fetches the first ten characters of file and check it to be a valid date in format DD.MM.YYYY and it can be skipped for other files by specifying “” or “No” against this entry.
CheckColumnsForSubset – This criterion compares the columns of browsed file against a subset of fix columns present in json. This validation will pass if columns of browsed file are subset of columns specified in template json. To skip this criterion leave it as “” or “No” and to use it specify “Yes”.

Kyvos 2024.12

Data File Upload

Related content