sc-import-csv
This document is a user guide for the sc-import-csv component version 6.00, released February 12, 2020.
1 Purpose
sc-import-csv places an import csv file field on a form allowing the user to select a csv file and save its rows as documents in the database.
The import csv file field on the form provides:
- A button to browse and select a csv file for importing.
- An area to drag and drop a selected csv file for importing.
- A Results Table to display:
- Counts at the foot of the Results Table to show:
- The total number of items (rows) within the selected CSV file.
- The current page number and total number of pages within the Results Table.
- The number of selected rows within the Results Table.
- Each row within the selected csv file together with their import status e.g.
- "Awaiting Save"
- "Imported - documentId"
- "Replaced - documentId"
- "Updated - documentId"
- Failed - Error replacing document: Error preprocessing save fields on update: sc-date-time field named date has invalid value "24/15/2015 11:20 a"
- Counts at the foot of the Results Table to show:
- A "SAVE" button to save the rows displayed as "Awaiting Save" in the Results Table as documents in the database by using a pre-defined document template. The fields of the pre-defined document template define how each csv file row field is to be imported as a field of a database document e.g. as a text, check box, check-list, date, drop down, phone or email, etc field.
- A "CLEAR" button to clear the Results Table and cancel the import process.
- Counters above ther Results Table to show "Records Read", "Documents Created" and "Errors".
Section 3 Supported Date, Checkbox and Checklist Formats of this document lists the sc-import-csv supported formats for fields that need to be imported as a date/time, checklist or checkbox field in a database document.
The sc-import-csv definition provides fields for setting the properties of the sc-import-csv field and is fully described below.
2 Definition
sc-import-csv is defined by a set of name/value pair fields consisting of:
- Required system fields
- Required customizable fields
- Optional customizable fields
Note:
- Default values for fields described in the tables below are shown in bold text.
2.1 Required System Fields
Field | Valid Values | Description |
---|---|---|
componentName | sc-import-csv | The component name. Example: "componentName": "sc-import-csv" |
2.2 Required Customizable Fields
Field | Valid Values | Description |
---|---|---|
columns | Array of field names | An ordered array of field names belonging to the pre-defined template whose documentId is defined by the templateId field value.On saving, the template is used to:
Example: See Example 1 below in Section 5 Examples |
name | Any value written in camel case. | Defines the name of the sc-import-csv field in the document and database. Example: "name": "testScImportCsv" |
templateId | A template id | The documentId of the pre-defined template used to create a document for each successfully imported row of the csv file. Example: "templateId": "759afa00-0f84-11e7-bd5b-0570620003a4" |
2.3 Optional Customizable Fields
Field | Valid Values | Description |
---|---|---|
directImporting | true | The csv file rows are immediately imported and added to the database as documents and the results are displayed in the Results Table. The Results Table will need to be cleared before an additional file can be imported. Example: "directImporting": true |
false | Defaults to false. The csv file rows are not immediately imported and added to the database as documents. Hence the sc-import-csv field's dedicated "SAVE" button will need to be clicked to import and add the csv field rows as documents in the database.. |
|
disableSave | true | Changing the sc-import-csv field value (i.e. selecting a csv file) does not trigger the Save icon to flash. Exiting the form without saving changes to the sc-import-csv field value does not trigger a warning message to display. In the Angular version of the sc-import-csv component, clicking the Save icon will not save the sc-import-csv field value to the document. In the AngularJS version of the sc-import-csv component, clicking the Save icon will save the sc-import-csv field value to the document. Note: As sc-import-csv provides a dedicated "Save" button, recommend defining sc-import-csv with "disableSave": true. Example: "disableSave": true |
false | Default value. Changing the sc-import-csv field value (i.e. selecting a csv file) does trigger the Save icon to flash. Exiting the form without saving changes to the sc-import-csv field value does trigger a warning message to display. In both the Angular and AngularJS versions of the sc-import-csv component, clicking the Save icon will not save the sc-import-csv field value to the document. |
|
disableSaving | true | The sc-import-csv field's dedicated Save button is hidden. Example: "disableSaving": true |
false | Defaults to false. The sc-import-csv field's dedicated Save button is visible. Clicking the Save button will import and save the rows of csv file as documents in the database. One document is created for each successfully imported row in the csv file. |
|
documentIdMatch | Enables the user to supply existing database documentId's for updates. The system first checks if the documentId exists in the database and if found completes the document changes in accordance with the values below i.e. replace or merge. |
|
replace | If the documentId is found, creates a new version of the document by deleting the document contents, and adding just the fields in the csv file to the document. If the replace is successful, the Results column of the Results Table displays Replaced - documentId value If there is an error in the csv file data, the Results column of the Results Table displays a meaningful error message, e.g.: Failed - Error replacing document: Error preprocessing save fields on update: sc-date-time field named date has invalid value "24/15/2015 11:20 a" Example: "documentIdMatch": replace |
|
merge | Defaults to merge. If the documentId is found, creates a new version of the document by updating the document fields that are in both the csv file and the document and adding document fields that are in the csv file but not in the document. If the merge is successful, the Results column of the Results Table displays Updated - documentId value If there is an error in the csv file data, the Results column of the Results Table displays a meaningful error message, e.g.: Failed - Error updating document: Error preprocessing save fields on update: sc-date-time field named date has invalid value "24/15/2015 11:20 a" |
|
documentIdNoMatch | Enables the user to supply new documentId's for insertion in the database. The system first checks if the documentId exists in the database and if not found completes the document changes in accordance with the values below i.e. error or add. |
|
error | If the documentId is not found, then a document is not added to the database, the error count is incremented by one, and the Results column of the Results Table displays a meaningful error message, e.g.: documentId 7e148110-2600-11e6-8e7f-257d738feec4 is not in database and adding is not enabled If the csv file documentId field is empty, a document is not added to the database and the Results column of the Results Table displays a meaningful error message. Example: "documentIdNoMatch": error |
|
add | Defaults to add. If the documentId is not found, a new document is added to the database with that documentId and with fields corresponding to the fields in the csv file. If the csv file documentId field is empty, then a new document is added to the database with a generated documentId and the Results Table Results column displays Imported - documentId value". |
|
enabled | false | The sc-import-csv field buttons and Results Table are greyed out, cannot be clicked and a Stop icon appears on mouseover. A csv file cannot be selected for import. Example: "enabled": false |
true | Defaults to true. The sc-import-csv field buttons and Results Table are not greyed out, can be clicked and a Stop icon does not appear on mouseover. A csv file can be selected and imported. |
|
fullWidth | true | The sc-import-csv field displays full width on the form. Note: As sc-import-csv field contains multiple gui elements including a Results Table, various counters and buttons, recommend defining sc-import-csv to display full width on the form. Example: "fullWidth": true |
false | Defaults to false. The sc-import-csv field does not display full width on the form. |
|
label | Any value | Defines the name of the sc-import-csv field on the form i.e. the field label. Example: "label": "Test sc-import-csv" |
Defaults to no field label is displayed. | ||
labels | Array of any values | An ordered array of values to be used as column headings for the Results Table. Example: See Example 1 below in Section 5 Examples. |
Defaults to the array of field names defined by the "columns" field. | ||
mandatory | true | The sc-import-csv field displays as mandatory (i.e. label in red text with an asterisk). Saving without importing a csv file, a message prompts the user to import a csv file. Example: mandatory": true |
false | Defaults to false. The sc-import-csv fieldl displays as optional (i.e. field label in black text). Saving without importing a csv file, a message does not prompt the user to import a csv file. |
|
visible | false | The sc-import-csv field is not visible on the form. Example: "visible": false |
true | Defaults to true. The sc-import-csv field is visible on the form. |
3 Supported Date, Checkbox and Checklist Formats
This section lists the sc-import-csv supported formats for fields that need to be imported as a date/time, checkbox or checklist field in a database document.
3.1 Supported Date/Time Fomats
The sc-import-csv supported formats for date/time fields in a csv file are listed in the table below:
Supported Date/Time Formats |
Example | Interpreted As |
---|---|---|
DD/MM/YYYY hh:mm a | 21/04/2019 10:30 pm | 21/04/2019 10:30 pm |
DD/MM/YY hh:mm a | 21/04/19 10:30 am | 21/04/2019 10:30 am |
DD/MM/YYYY | 21/04/2019 Note: Time will default to midnight 12:00 am |
21/04/2019 12:00 am |
DD/MM/YY | 21/04/19 Note: Century will default to current century. Time will default to midnight 12:00 am |
21/04/2019 12:00 am |
DD/MM | 21/04 Note: Year defaults to current year. Time defaults to midnight 12:00am |
21/04/2019 12:00 am |
hh:mm a | 10:30 pm Date defaults to current date. |
04/12/2019 10:30 pm |
3.2 Supported Checkbox Formats
The sc-import-csv supported format for checkbox fields in a csv file is shown in the table below:
Supported Checkbox Format |
Example | Resulting Field in Document & Database |
---|---|---|
true | true | "testImportCheckBox": true |
"testImportCheckBox": "" |
3.2 Supported Checklist Formats
The sc-import-csv supported format for check list fields in a csv file is shown in the table below:
Supported Check List Format |
Example | Resulting Field in Document & Database |
---|---|---|
value 1^value 2^value 3^... | Mon^Wed | "testImportCheckList": ["Mon","Wed"] |
"testImportCheckList": "" |
4 Typical Definition
Below is a typical sc-import-csv definition, defined with its required fields plus any optional field whose value is typically other than its default value.
{
"componentName": "sc-import-csv",
"name": "testScImportCsv",
"label": "Test sc-import-csv using csv file with & without documentId, checklist & checkbox values",
"templateId": "759afa00-0f84-11e7-bd5b-0570620003a4",
"labels": [
"document ID",
"First Name",
"Surname",
"Email",
"Phone",
"Days Available",
"On Leave",
"Employee Type",
"Date Submitted"
],
"columns": [
"documentId",
"testImportToParty.firstName",
"testImportToParty.surname",
"testImportToEmail",
"testImportToPhone",
"testImportToCheckList",
"testImportToCheckBox",
"testImportToDropDown",
"testImportToDateTime"
],
"fullWidth": true,
"disableSave": true
}
One or more of the optional fields shown below can be included in the above definition should a value other than their default value be required.
"directImporting":true,
"disableSaving":true,
"documentIdMatch":"replace",
"documentIdNoMatch":"error",
"enabled":false,
"visible":false
5 Examples
Example 1
sc-import-csv defined with the typically needed fields.
{
"componentName": "sc-import-csv",
"name": "testScImportCsv",
"label": "Test sc-import-csv using csv file with & without documentId, checklist & checkbox values",
"templateId": "759afa00-0f84-11e7-bd5b-0570620003a4",
"labels": [
"document ID",
"First Name",
"Surname",
"Email",
"Phone",
"Days Available",
"On Leave",
"Employee Type",
"Date Submitted"
],
"columns": [
"documentId",
"testImportToParty.firstName",
"testImportToParty.surname",
"testImportToEmail",
"testImportToPhone",
"testImportToCheckList",
"testImportToCheckBox",
"testImportToDropDown",
"testImportToDateTime"
],
"fullWidth": true,
"disableSave": true
}
Resulting field on the form:
The templateId field "templateId": "759afa00-0f84-11e7-bd5b-0570620003a4"
of this sc-import-csv definition identifies the pre-defined template that sc-import-csv will use to create a document for each successfully imported csv file row. The components
section of this pre-defined template is shown below and defines each field listed as a value of the sc-import-csv columns
array.
"components": [
{
"componentName": "sc-party",
"name": "testImportToParty",
"label": "Contact Name",
"fullWidth": true
},
{
"componentName": "sc-email",
"name": "testImportToEmail",
"label": "Contact Email"
},
{
"componentName": "sc-static-html",
"fullWidth": true,
"html": "<br/>"
},
{
"componentName": "sc-phone-number",
"label": "Contact Phone Number (click to dial)",
"name": "testImportToPhone"
},
{
"componentName": "sc-static-html",
"html": "<br/>",
"fullWidth": true
},
{
"componentName": "sc-check-list",
"name": "testImportToCheckList",
"label": "Days Available",
"list": [
"Mon",
"Tue",
"Wed",
"Thu",
"Fri",
"Sat",
"Sun"
],
"fullWidth": true
},
{
"componentName": "sc-static-html",
"fullWidth": true,
"html": "<br/>"
},
{
"componentName": "sc-check-box",
"name": "testImportToCheckBox",
"label": "On Leave"
},
{
"componentName": "sc-static-html",
"fullWidth": true,
"html": "<br/>"
},
{
"componentName": "sc-drop-down",
"label": "Employee Type",
"name": "testImportToDropDown",
"dropDownList": [
"Full Time",
"Part Time",
"Casual",
"Probation"
]
},
{
"componentName": "sc-static-html",
"html": "<br/>",
"fullWidth": true
},
{
"componentName": "sc-date-time",
"label": "Date Submitted",
"name": "testImportToDateTime"
}
]
Resulting form for the pre-defined template:
A csv file to be imported using the above sc-import-csv field:
Each of the six rows of the above csv file has nine data fields containing in order the data items listed below.
Note: Comma delimiters indicate the start or end of a field regardless of the whether the field has value or not e.g. the fifth row of the above csv file has no value for its 1st and 6th fields (i.e. the "documentId" and "Days of the week a person is available" fields).
Field 1: documentId
Field 2: First Name of a person (will be saved in the predefined template's sc-party field)
Field 3: Surname of a person (will be saved in the predefined template's sc-party field)
Field 4: email Address of a person (will be saved in the predefined template's sc-email field)
Field 5: Phone Number of a person (will be saved in the predefined template's sc-phone-number field)
Field 6: Days of the week a person is available (will be saved in the predefined template's sc-check-list field)
Field 7: Indicates if a person is on leave (fwill be saved in the predefined template's sc-check-box field)
Field 8: Employment status of a person (will be saved in the predefined template's sc-drop-down field)
Field 9: Date & Time field (will be saved in the predefined template's sc-date-time field)
Resulting field on the form after selecting the above csv file to import:
Clicking the "CLEAR" button will clear the Results Table and cancel the import process.
Clicking the "SAVE" button will save the selected csv file to the database such that one document is created and saved for each row in the csv file.
Resulting field on the form after clicking the "SAVE" button:
To open a created document, click its hyperlink in the Result column.
Clicking the Results Table's 4th row hyperlink opens the document:
Resulting document in the database:
The templateId "759afa00-0f84-11e7-bd5b-0570620003a4" is both used to create, save and display the successfully imported documents.