sc-import-csv
This document is a user guide for the sc-import-csv component release version 7.1.38.
1 Purpose
The "sc-import-csv" component places an import csv file field on a form allowing the user to import a csv file and save its records as documents in the database.
The "sc-import-csv" field on the form provides:
-
Two methods for selecting a csv file for import:
- An area to drag and drop a selected csv file.
- A "Choose Files" button to browse and select a csv file.
-
Counters displaying the number of "Records Read", "Documents Created" and "Errors".
-
A Results Table displaying each record of a 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"
- A
`"sc-import-csv""Save" button
to save the records "Awaiting Save" as documents in the database using the template defined by the "templateId"field described in Section 2.2 Required Customizable Fields. - A
`"sc-import-csv""Clear" button
to clear the Results Table and cancel the import process.
Section 3 Supported Date, Checkbox and Checklist Formats of this document lists the supported formats for fields to be imported and saved as a date/time, checkbox or a checklist field in a database document.
The "sc-import-csv" component definition provides fields for setting the properties of a "sc-import-csv" field on a form and is fully described below.
2 Definition
The "sc-import-csv"component 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 the 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 the field names or a pre-defined template whose documentId is defined by the "templateId" field.On saving, the template is used to:
Example: From Example 1 below. "columns": ["documentId","testImportToParty.firstName","testImportToParty.surname","testImportToEmail","testImportToPhone","testImportToCheckList","testImportToCheckBox","testImportToDropDown","testImportToDateTime"] |
| name | Any value written in camel case. | Defines the name of the "sc-import-csv" field in the document and database.Example: "name": "scImportCsvTypicalDefn" |
| templateId | A template id | The documentId of the pre-defined template used to create a document for each successfully imported csv file record. Example: "templateId": "759afa00-0f84-11e7-bd5b-0570620003a4" |
2.3 Optional Customizable Fields
| Field | Valid Values | Description |
|---|---|---|
| csvFieldnamePrefix | To save the records of an imported csv file, either or both of the following options can be used, each having different result:
|
|
| text value | As described above, defines a prefix to add to the "column number" when each "column value" of an imported csv file record is saved to the document as "column number": "column data". Example: "csvFieldnamePrefix": "col-"Note: The records of an imported csv file will only be saved to the document displayed on the form if the "sc-import-csv" component is defined with or defaults to "disableSave": false. |
|
| Defaults to no prefix is added to the "column number" when each "column value "of an imported csv file record is saved to the document displayed on the form as "column number": "column data". | ||
| directImporting | true | The csv file records are immediately imported and saved to the database as documents with the results being displayed in the Results Table. The Results Table will need to be cleared before an additional csv file can be imported. Example: "directImporting": true |
| false | Default value. The csv file records are not immediately imported and saved to the database as documents. Hence the "sc-import-csv" "Save" button will need to be clicked to import and save the csv file records as documents in the database.. |
|
| disableSave | true | Changing the "sc-import-csv" field value (i.e. selecting a csv file for import) does not trigger the document "Save" button to display and flash.Exiting the form without saving changes to the "sc-import-csv" field value does not trigger a warning message to display.As the "sc-import-csv" field provides a dedicated "Save" button , recommend defining "sc-import-csv" with "disableSave": true.Note: Should the document "Save" button display and flash  as a result of changing the value of another field on the form, then:
Example: "disableSave": true |
| false | Default value. Changing the "sc-import-csv" field value (i.e. selecting a csv file for import) does trigger the document "Save" button to display and 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 flashing document "Save" button will save the "sc-import-csv" field value to the document. |
|
| disableSaving | true | The "sc-import-csv" "Save" button is hidden.Example: "disableSaving": true |
| false | Default value. The "sc-import-csv" "Save" button is visible.Clicking the "sc-import-csv" "Save" button will import and save the csv file records as documents in the database.One document is created for each successfully imported csv file record. |
|
| documentIdMatch | Allows the user to include existing database documentId's in a csv file. 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 | Default value. 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 | Allows the user to include new documentId's in a csv file. 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 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 record's 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 | Default value. 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 record's 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 | Default value. 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 | Default value. The "sc-import-csv" field does not display full width on the form. |
|
| hideStatusCounts | true | Hide the "Records Read", "Documents Created" and "Errors" counters. Example: "hideStatusCounts": true |
| false | Default value. Do not hide the "Records Read", "Documents Created" and "Errors" counters. |
|
| label | Any value | Defines the name of the "sc-import-csv" field on the form i.e. the field label.Example: "label": "sc-import-csv Typical Defn" |
| 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: From Example 1 below. "labels": ["document ID","First Name","Surname","Email","Phone","Days Available","On Leave","Employee Type","Date Submitted"] |
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 | Default value. The "sc-import-csv" field 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 | Default value. 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 and saved as a date/time, checkbox or checklist field in a document.
3.1 Supported Date/Time Fomats
The "sc-import-csv" component 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" component 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" component 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.
"csvFieldNamePrefix": "col-"
"directImporting":true,
"disableSaving":true,
"documentIdMatch":"replace",
"documentIdNoMatch":"error",
"enabled":false,
"hideStatusCounts": true
"visible":false
5 Examples
Example 1
sc-import-csv defined with the typically needed fields shown in Section 4 Typical Definition..
{
"componentName": "sc-import-csv",
"name": "scImportCsvTypicalDefn",
"label": "sc-import-csv Typical Defn",
"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 record. The components section of this pre-defined template is shown below and defines each field listed as a value of the 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 records 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 record 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 would clear the Results Table and cancel the import process.
Clicking the `"sc-csv-import" "Save" button will save the 6 records of the selected csv file as 6 documents in the database.
Resulting field on the form after clicking "sc-csv-import" "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:
Note:
The templateId "759afa00-0f84-11e7-bd5b-0570620003a4" is both used to create, save and display the successfully imported documents.
Example 2
sc-import-csv defined with the typically needed fields plus three optional fields defined as"csvFieldNamePrefix": "col-", "hideStatusCounts": true and "disableSave": false (or "disableSave" not defined and defaults as false)
{
"componentName": "sc-import-csv",
"name": "scImportCsvNonTypicalDefn",
"label": "sc-import-csv Non-typical Defn",
"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": false
"csvFieldNamePrefix": "col-"
"hideStatusCounts": true
}
Resulting field on the form is the same as in Example 1 but without the 3 counter fields.
Resulting field on the form after selecting the same csv file from Example 1:
As "sc-import-csv" is defined with "disableSave: false", the user can perform either or both of the following "Save" options:
- Click the
"sc-import-csv""Save" button to save the 6 csv file records as 6 documents in the database i.e. as in Example 1. - Click the flashing document "Save" button to save the 6 csv file records to the document as an array of 6 elements in the
"scImportCsvNonTypicalDefn"field. This save option is available because the"sc-import-csv"is defined with or defaults to"disableSave": false
Resulting "scImportCsvNonTypicalDefn" field in the document on clicking the flashing document "Save" button:
"scImportCsvNonTypicalDefn": [
{
"documentId": "8ca2cce0-eadi-11e7-9185-4b6d458c503b",
"col-0": "Awaiting Save",
"col-1": "8ca2cce0-eadi-11e7-9185-4b6d458c503b",
"col-2": "James",
"col-3": "Brown",
"col-4": "james.brown@bigpond.net.au",
"col-5": "03 1234 5678",
"col-6": "Mon^Tue^Wed^Thu^Fri^Sat^Sun",
"col-7": "true",
"col-8": "Casual",
"col-9": "24/03/2017 11:26 AM"
},
{
"documentId": "8ca64f50-eadi-11e7-9185-4b6d458c503b",
"col-0": "Awaiting Save",
"col-1": "8ca64f50-eadi-11e7-9185-4b6d458c503b",
"col-2": "Gabby",
"col-3": "Green",
"col-4": "grace.green@optus.net.au",
"col-5": "07 2345 6789",
"col-6": "Mon",
"col-7": "",
"col-8": "Full Time",
"col-9": "02/11/17 02:30 PM"
},
{
"documentId": "8cabf4a0-eadi-11e7-9185-4b6d458c503b",
"col-0": "Awaiting Save",
"col-1": "8cabf4a0-eadi-11e7-9185-4b6d458c503b",
"col-2": "Betty",
"col-3": "Blue",
"col-4": "betty.blue@iinet.net.au",
"col-5": "03 3456 7890",
"col-6": "",
"col-7": "true",
"col-8": "Part Time",
"col-9": "24/03/2017"
},
{
"documentId": "",
"col-0": "Awaiting Save",
"col-1": "",
"col-2": "Tony",
"col-3": "Black",
"col-4": "tony.black@iinet.net.au",
"col-5": "03 3456 7890",
"col-6": "Sat^Sun",
"col-7": "true",
"col-8": "Part Time",
"col-9": "24/03/17"
},
{
"documentId": "",
"col-0": "Awaiting Save",
"col-1": "",
"col-2": "James",
"col-3": "McAllan",
"col-4": "james.mcallan@gmail.com",
"col-5": "03 3456 1234",
"col-6": "",
"col-7": "true",
"col-8": "Part Time",
"col-9": "02/05"
},
{
"documentId": "",
"col-0": "Awaiting Save",
"col-1": "",
"col-2": "Emily",
"col-3": "Katting",
"col-4": "emily.katting@gmail.com",
"col-5": "03 1111 2222",
"col-6": "Mon^Wed^Fri",
"col-7": "true",
"col-8": "Part Time",
"col-9": "11:30 PM"
}
]
Note:
- Each data item of each imported csv file record is saved to the document as "column number": "column data". As
"sc-import-csv"was defined with"csvFieldNamePrefix": "col-", the "column numbers" are prefixed with "col-" - When a csv file record has a column with no value, then the value will be saved as
"". For example, the 6th imported csv file record had no data in column 1, hence column 1 is saved in the document as"col-1": "".