Overview:
Excel import utility is used to import data from excel sheet into specific windows.
Advantage of using excel import utility is that a user can import large amount of data via excel which saves time. It is also possible to change the parameters once the data is imported in desired window.
Benefits of Excel Import Utility:
Imports data from excel to system.
Checks record to be inserted with existing record to avoid duplicity.
Allows data to be imported for all or selected fields.
Also allows importing data for windows which has multiple tabs.
Allows user to select default method for mandatory fields.
Pre-requisites for Excel Import Utility:
In order to use excel import utility user will have to install it. To do so, user has to login with System Administrator role.
Install Excel Import Utility:
After login, Super User will have to install Excel import utility from market button available in Dashboard.
...
NOTE: While install user will have to select the tenant on which Excel Data Import Utility is to be installed.
Enable Import icon from the Superuser:
For enabling the icon of import in right panel of any window, user has to login with the super user and open the Screen configuration window from menu.
...
After selecting checkbox, click on save icon and re-login with the user credentials and open the customer master window from the menu then on the right actions import icon has been shown as given in below screenshot:
...
Using Excel Import Utility:
There are two methods in VA system to run the import functionality.
Custom Mapping
Default Mapping
1. CUSTOM MAPPING:
Custom Mapping is a feature or capability within VA system that allows users to import data using custom mapping rules or configurations.
Custom mapping import functionality enables users to import external data into VA system while specifying custom rules or mappings to transform and match the imported data to the corresponding fields or entities within the system. Users can define mappings between external data sources and target fields within the system, ensuring accurate and efficient data migration or integration.
1.1 Creating Excel sheet for Import
To Import data in VA system, user will have to create a excel sheet and on sheet 1 and add same name of database column for which value is to be imported.
...
NOTE: Column name should be same as they are in import utility (including spaces)
1.2 Import Data with Excel import utility
Once the entire excel sheet is complete to import data user have to open the window on which data is to be imported by locating it using search option. When window is open on the right-side pane locate import option.
...
By clicking on Import button system will open excel import utility form.
1.3 Excel import utility Window in detail
...
Sr. No | Field | Description | Remarks |
1 | Select Mapping | Select Mapping is used to define new / select existing mapping. | Used to define new mapping which user can identify the particular is for which window, so that the same can also be used in future. |
2 | Excel Columns | On selection of excel sheet all the column from sheet are displayed for mapping with database column | Column displayed in excel column can also have column of other tab from the window, only in case we are importing multiple tab values. |
3 | Mapping Tab | Mapping tab lists all the tab related to the window on which data is to be imported. | The tab displayed can be expanded to check the mapping tab by tab. |
4 | Default Tab | Default tab list all the values assigned to the default parameters. | The values assigned to each tab of window can be seen by expanding particular tab name which list values like “True”, “False” , “F” |
5 | Log | Log tab shows detail of action performed along with the outcome. | In Logs we can also cross check the success or error message encountered during import. |
6
|
 | Export Log button is used to export the log created after the commencing the import process | This export log can also be saved as a file for reference purpose. |
7
|
 | Check box enables user to import excel data into database columns | If unchecked the values are not imported into database. |
8
|
 | The save button is use to save the mapping done between excel columns & database column | Once linked the column are highlighted with Green (columns mapped) & Blue (column which require mandatory default values) Color |
9
|
 | Import button is used to import data from excel to database. | Import button will only work once all column & default value columns are linked. |
10 |
 | Upload button is used to upload the excel file. | After selecting a mapping from the dropdown menu, the 'Upload Excel file' button becomes enabled. |
11 |
 | Download button is used to download the excel file. | After selecting a mapping from the dropdown menu, the 'Download Excel File' button becomes enabled |
...
User is able to select from the drop-down option and after selection, click on the default button.(which is changeable after import).
1.4 Mapping multiple tab with excel
If the excel to be imported has values pertaining to other tabs of same window user will have to select the appropriate tab first in order to display database column for mapping.
...
(iii) Contact Details tab.
(i) Customer Tab: On the right side, user has to select the tab on which they want to import data then start mapping as given in below screenshot:
...
All default values marked on header by user can view as given in below screenshot:
...
(ii) Location Tab:
...
On Location window, User has to map the location with the Address field. Once user clicks on Address field, a screen appears as given in below screenshot:
...
All default values marked on header by user can view as given in below screenshot:
...
(iii) Contact Details tab:
...
On the above screenshot third tab of the customer master i.e. contact details has been shown. User has to map the above-mentioned fields from the database fields.
...
All default values marked on header by user can view as given in below screenshot:
...
1.5 Set Identifier
While right click to field the Set Identifier fields appears, Set Identifier means a unique identifier like a search key that is used to identify a specific set of data. If the user wants specific parent tab data on the child tabs, then the user should mark a particular field as Set Identifier so that the system can automatically fetch that field's data on the child tabs. The set identifier is used as a reference point for importing data into the system.
...
User will see the result in the log section also that the import is done successfully.
...
1.6 Log Check
After the import commences the detail log of the same can be viewed in LOG tab as shown in below screenshot:
...
In this file, log information has there in text format as given in below screenshot:
...
1.7 Removing Mapped columns
If user wants to do any changes in mapping that already done or wants to remove the mapped column, user has to go excel import utility and select the already mapped sheet from dropdown along with excel spreadsheet:
...
If user wants to do any changes in mapping that already done follow the same step as above by removing or adding the mapping points, user able to do the changes.
1.8 Download Saved mapping excel file
Download button will enable when user select mapping from the dropdown. User clicks on download button then excel file will download on user system with the screen name
...
NOTE: The downloaded excel file will contain only those fields that were saved by the user, with all columns mapped earlier during the mapping process
2. DEFAULT MAPPING:
The default mapping import functionality typically refers to a feature within a VA system that facilitates the import of data using pre-defined or standard mapping rules or configurations.
...
By using the default mapping, user just need to select the default mapping on the import screen, download the default excel file, enter values in it, then again upload the same file on the import screen and finally click on the import button to import the excel data in the VA system.
2.1 Using the default mapping for import
For using the default mapping functionality to import data in the VA system, User need to login into the VA system with the credentials and then go to window on which the user wants to import data from the excel sheet.
...
On the right side of the import screen (Reference: Above screenshot), the system will show all the fields in particular sequence as they are sequenced on the window.
2.2 Download the default mapping excel file
Select the Default Mapping from the drop down in the ‘Select Mapping’ field.
...
(iv) Date - MM-DD-YYYY
2.3 Enter data to import
Now user need to enter the data in the downloaded excel file that the user wants to import into the VA system.
Open the downloaded excel file.
Enter data in the excel file according to the fields available there.
...
NOTE: "TRUE" must be mentioned in the excel sheet if the user wants to mark the active checkbox or any other checkbox as true.
2.4 Upload the excel file for import
After entering the data into the excel file then user needs to follow below mentioned steps:
...
After the data is imported, the upload and download button is enabled if the user wants to re-upload the Excel sheet after some corrections. By clicking on upload, the user can re-upload the Excel sheet again on the import screen.
2.5 Import data in the VA system
For importing the data into the VA system, user needs to mark the ‘Commit After Import’ checkbox as true and click on the import button as shown in the screenshot below:
...
All the data mentioned in the Excel sheet is successfully imported into the VA system, as shown in the above screenshot, and is visible on the customer master screen.
2.6 Save as feature for the default mapping
Enabling users to reuse their mapping while preserving any customizations made to the default mapping.
...
Selected Excel file is imported.
Now, to save data into the screen user need to click on the ‘Commit to import’ button then Excel file is imported and saved in the database.
...
VALIDATIONS:
Validations are given below:
...
For example: In the case of </EOR> in Excel, the user has inserted </EOR> into the header cell in the third row. So, after importing that Excel, the system will not fetch the data of importing the third row or other rows that are after the third row. Please refer below mentioned screenshots:
...
NOTE: Support culture
(i) Display page UI according to login language
When user logs in with the language other than English US (en_US) e.g. German Language, then opens import screen for any window such as Customer Master, all user interface elements will be displayed in German, if translation is available for the fields and UI labels in message screen.
...
(ii) Download excel file according to the login language translations
Based on the login language other than English US (en_US) e.g. German Language, user opens import page of any screen such as Customer master and selects the Default mapping from the dropdown then clicks on the download button, the file will be downloaded in the language selected at the time of login based on the translations available in the system. All the field names (excel column headers) and the instruction row will be in the translated format based on the login language.
...
Open the downloaded Excel file. 
...
NOTE: If a user login with either English / German language preference, then go to import screen of any window. let say, Customer Master window then select a mapping from the dropdown & clicks on the ‘Download Excel file’ button, the system will download the Excel file. The excel column headers and the instruction row will be according to the translations available for the login language.
...