Overview:
The Excel import utility is used to import data from excel sheet into specific windowsscreens.
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.
...
This utility allows users to import large amounts of data quickly, which saving time.
Advantages 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 screens 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 windowscreen, user has to login with the super user and open the Screen configuration window screen from menu.
To open the screen configuration window screen user can search the window screen in the menu search utility
or
open the window screen using the following path:
...
Click on the Screen Configuration link. After clicking on the link, new window screen will open as showing in the below screenshot:
...
Enter the window screen name in search, on which the user wants to enable the import button. For example: User wants to enable on Customer Master window screen as given in below screenshot:
...
After selecting checkbox, click on save icon and re-login with the user credentials and open the customer master window screen 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.
...
The above figure illustrates the value which are to be imported from excel into Customer Master windowscreen.
Save the sheet once all the values are entered and is ready for import.
...
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 screen on which data is to be imported by locating it using search option. When window screen 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 screen 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 windowscreen, 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 windowscreen, only in case we are importing multiple tab values. |
3 | Mapping Tab | Mapping tab lists all the tab related to the window screen 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 screen 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 screen 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:
...
In above screenshot, Fields that highlighted with blue color are marked as default, for example: Record should be active by default and is customer checkbox is true by default.
Fields that are highlighted with green color are mapped with fields that are shown on left side as per the excel sheet.
Once user done with mapping on one tab then user can view the mapping again as per below screenshot:
...
All default values marked on header by user can view as given in below screenshot:
...
(ii) Location Tab:
...
On Location windowscreen, 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.
For example, in the customer master windowscreen, the user has to mark Customer (C_BPartner_ID) as set as an identifier so that this field information is automatically fetched into the location and contact details tab. The user doesn’t have to map this field every time again.
...
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:
...
Once the appropriate mapping sheet is selected user will have to scroll down & click on mapping tab
...
Expand the window screen tab to see the already mapped column with database column. To remove mapping
...
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.
...
Default mapping is available in the dropdown of every window’s screen’s import functionality. In Default mapping, mapping is already done by the system as shown in below screenshot:
...
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 screen on which the user wants to import data from the excel sheet.
...
Click on the import button to load the import functionality.
A new import window screen will open as shown in the screenshot below.
...
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 windowscreen.
2.2 Download the default mapping excel file
Select the Default Mapping from the drop down in the ‘Select Mapping’ field.
...
Go to ‘downloads’ folder of the windows system to access the downloaded excel file.
...
The header tab and its child tabs will be in different colors. The header row is in the darker color and the others row are in the lighter color as compared to the header row.
In the default excel sheet, the format of the tab and field will be in this format: “Tab name.field name”.
For example, in the Customer Master windowscreen: the format of the tab and field is: Customer.Name, Customer.Add to Interest Area, Customer.Organization, Customer.search key, Customer.Active etc.
It Represents ‘Customer’ as a ‘Tab name’ and ‘Name’ represents the ‘field name’.
The mandatory sign indicating that users must fill out this columns.
In the second row, an instruction row for the following types of fields/columns:
(i) Identifier Column (Table/TableDirect/Search)
(WindowNamescreenName/TabName.FieldName_WindowNamescreenName/TabName.FieldName)
For e.g. - Tax Rate/Tax.Name_Tax Rate/Tax.Value
...
(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.
...
Let’s add up few data for the customer master screen in the downloaded excel file, as shown in the screenshot below.
After entering data into the default excel file, Save the file with same the name. (Customer Master)
...
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:
Now, user needs to go back to the Customer Master screen, click on the import button from the Right Action Panel.
Import window screen will open in a new tab.
Now select the default Mapping file in the Select Mapping field as shown in the screenshot below.
Upload the excel file by clicking on the upload icon as shown in the screenshot below.
...
A new pop-up window screen will open up, where user needs to select the excel file and then click on the open button as shown in screenshot below.
...
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.
...
Go to Customer Master screen, click on the import button from the Right Action Panel.
Import window screen will open in a new tab.
Now select the “Default Mapping” file in the Select Mapping field as shown in the screenshot below.
Upload the excel file by clicking on the upload icon as shown in the screenshot below.
A new pop-up window screen will open up, where user needs to select the excel
The system will load the Excel file on the left-hand side of the screen, and mapping is already created in the system by default, as shown in the screenshot below.
...
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 screen 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.
...
NOTE: If a user login with either English / German language preference, then go to import screen of any windowscreen. let say, Customer Master window screen 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.
...