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.
In the Market, click on the search panel and locate Excel Data Import Utility to install
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.
To open the screen configuration window user can search the window in the menu search utility
or
open the window using the following path:
Menu Path: - Menu> Screen Configuration
Click on the Screen Configuration link. After clicking on the link, new window will open as showing in the below screenshot:
Enter the window name in search, on which the user wants to enable the import button. For example: User wants to enable on Customer Master window as given in below screenshot:
Click on fourth tab i.e. ‘Action’ and mark Import Map checkbox to enable import icon for customer master 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 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 window.
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 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 |
To assign name to import, type the name & click on the + icon as shown in above figure
Once user give the mapping name then the user clicks on save icon as shown in screenshot:
After saving the mapping name, User has to select the excel file by clicking on upload icon, which user created for importing the records as shown in below screenshot:
Select the file that user wants to import into the VA system and then click on the open button as shown in the screenshot below.
Upon selection of excel sheet all the columns listed in sheet 1 will be listed for mapping with database column as shown below:
Excel headings are displayed in the field on the left, and system fields are presented in the field on the right. Once the columns from excel and database are listed select the matching column and click on map button.
NOTE: Only one record per instance can be mapped together.
The (*) beside each database column indicate it as mandatory column.
To specify the default value for select columns, select the database column and check / Uncheck (default (is default) button & then click on default button.
To select default value for parameters, select the same & then select the default value for specific column.
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.
In below scenario User are importing value into Customer Master which has multiple tabs, User will be importing in three different tabs of Customer Master, which include: -
(i) Customer Tab.
(ii) Location Tab.
(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 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:
User has to mapped the address 1, address 2, country, city, zip and region, after mapping is done, user has to click on “OK”.
Once user done with mapping 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:
(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.
Once user done with mapping 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:
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 window, 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.
The option to select the field as “Set Identifier” should be done on the 1st tab mapping only.
NOTE: Each tab mapping to be saved before proceeding to the next tabs.
In the second & third tab the identifier field will be tagged to the reference field as shown in the figure below:
In the above figure, the user has mapped name field in excel file with C_BPartner_ID (reference field)
As the “Name” field has a reference on third tab as well and as user are importing data for third tab it is mandatory to tag the same.
Once all the excel columns & identifier are mapped to database column click on save button
Upon which system will pop an alert message as shown below screenshot:
Uncheck “Commit after import” so that the errors can be tracked before the final import. Then click on import button.
Once import is done, the system gives a notification that appears on user screen as per below screenshot:
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:
NOTE: If any error occurs while importing then it will be show on Log tab, and on ErrorLog tab, user can see about the specific error
Once all the mapping is done without any error then user has to click on “Commit After Import” checkbox and click on import icon as shown in below screenshot:
After that all the data of excel sheet will be successfully imported into system.
NOTE: If the same file is imported again by user, system maintains a check on same & does not allow the import with status “Import done with error”. As shown in the figure below.
Logs can be exported with the help of
button, which download the log file for reference purpose as per given 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 tab to see the already mapped column with database column. To remove mapping
simply click on remove mapping icon as given below:
Once removed, a column can be remapped to database column.
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
If mapping is saved against the Excel file, then the user can download the Excel file by clicking on download button, but if the mapping is not saved against the Excel file, then by clicking on the download button, the system will give an error message, i.e., mapping not found for the selected mapping.
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.
The default mapping import functionality allows users to import external data into VA system using pre-defined or standard mapping rules without the need for manual configuration. This feature automatically maps fields from the imported data to corresponding fields or entities within the system based on predetermined mapping logic.
Default mapping is available in the dropdown of every window’s import functionality. In Default mapping, mapping is already done by the system as shown in below screenshot:
Before selecting any mapping from the dropdown, download and upload buttons are disabled.
By using the default mapping, the user no longer needed to map every single field to the respective field. Now users can import their large excel data into the VA system in a much simpler process.
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.
Let’s take an example of Customer Master Screen,
Go to the Customer Master screen from the menu search utility.
Go to the Right Action Panel of the Customer master screen.
Click on the import button to load the import functionality.
A new import window 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 window.
2.2 Download the default mapping excel file
Select the Default Mapping from the drop down in the ‘Select Mapping’ field.
After selecting Default mapping, Download and Upload button will be enabled.
Click on the download Excel button just under the select mapping field as shown in the screenshot below.
An excel file will be downloaded in the system with the name of Customer Master.xlsx.
A Pop-up message will appear on the screen i.e., File downloaded successfully as shown in the screenshot below.
Go to ‘downloads’ folder of the windows to access the downloaded excel file.
The downloaded file is shown in the screenshot below:
NOTE: The Top Row of excel sheet will be freeze.
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 window: 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’.
In the second row, an instruction row for the following types of fields/columns:
(i) Identifier Column (Table/TableDirect/Search)
(WindowName/TabName.FieldName_WindowName/TabName.FieldName)
For e.g. - Tax Rate/Tax.Name_Tax Rate/Tax.Value
(ii) Boolean column - Dropdown list of values Y and N
(iii) List Value - Dropdown list with the list values from which user can select
(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: User needs to enter the data in the excel file appropriately and accurately in the respective fields and columns.
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 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 will open up, where user needs to select the excel file and then click on the open button as shown in screenshot below.
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.
Now, user all needs to click on the Import button to import the excel data into the VA system.
The system will take few seconds to import the data as system shows importing data on the screen as shown in the screenshot below:
After import is successfully done on the VA system, the system will show a message that ‘Import Done’ as shown in the screenshot below.
Also, under the Log section, user will get individual ‘result’ of Import and also shows ‘Import Done Successfully’ message at the bottom as shown in the 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:
A message pops up showing the import is done successfully.
Once the data is imported in the VA system then the user can go to the Customer master screen and check for the record that the user has imported.
User will be able to see the imported data on the Customer Master Screen 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.
After entering the data into the excel file, user needs to follow below mentioned steps (e.g user is importing data on Customer Master Screen):
Go to Customer Master screen, click on the import button from the Right Action Panel.
Import window 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 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.
Now, user updates the mapping e.g. removed some columns from the mapping or set any column values as default.
After making changes into the mapping, now the user click on the ‘Save mapping’ button as mentioned below.
A pop up will open, which will ask user to enter the mapping name.
Enter Mapping name as per the user’s choice
Now Click on the Save button.
After the mapping is saved, it will appear at the top of the list under the ‘Select Mapping’ dropdown value.
Now the user can upload the Excel file by using the ‘Upload Excel File‘ button.
The system will now do the mapping based on the changes done at the time of update done for the default mapping and saved with a different name.
Click on the import button
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:
NOTE: There is a validation that if the user selects default mapping, then only the downloaded default mapping Excel file should be uploaded; if the user selects a file other than the default mapping download, then the system will display an error message i.e. “Please Select Valid Excel File” and vice versa.
NOTE: A Validation has been added to the import functionality, if there is any empty row between the rows in the Excel sheet during the import, the system will not import the records, after the empty row.
For example: There is an Excel sheet displayed in the screenshot below where there is data in the first two rows, followed by empty rows numbered 4 and 5. The sequence then resume from row number 6. In this case, system will import only the data available in the first two rows, as data should not be imported after encountering an empty row, please refer below mentioned screenshots:
NOTE: New keyword added to stop excel import i.e. </EOR>.. When the user utilizes </EOR> which indicate End of Row, ensuring that the Excel Import tool will not check the rows after this keyword
**</EOR> only works in the case of the first row first column. If it is defined other than that column it will not work. **
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.
NOTE: User will have to upload the excel with same login language in which user downloaded the excel file, otherwise, the column headers will not match with the mapping saved.