Skip to content

sc-import-csv

This document is a user guide for the sc-import-csv component version 6.00, released February 12, 2020.

go-to-typical-definition

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"
  • 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:

  1. 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:
  • Create a document in the database for each successfully imported row of the csv file. Each created document has the array field names in array field name order and set to the csv file column values in csv column order.
  • Display each created document.

    • 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:

    sc-import-csv-Example-1-Predefined-Template-Form

    A csv file to be imported using the above sc-import-csv field:

    sc-import-csv-field-Example-1-Image-2

    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:

    sc-import-csv-field-Example-1-Image-3

    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:

    sc-import-csv-field-Example-1-Image-4

    To open a created document, click its hyperlink in the Result column.

    Clicking the Results Table's 4th row hyperlink opens the document:

    sc-import-csv-field-Example-1-Image-5

    Resulting document in the database:

    sc-import-csv-field-Example-1-Image-6

    The templateId "759afa00-0f84-11e7-bd5b-0570620003a4" is both used to create, save and display the successfully imported documents.