User Manual – Excel Data Import Utility

Overview:

The Excel import utility is used to import data from excel sheet into specific screens. 

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 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.

Open

In the Market, click on the search panel and locate Excel Data Import Utility to install 

Open

Enable Import icon from the Superuser: 

For enabling the icon of import in right panel of any screen, user has to login with the super user and open the Screen configuration screen from menu.  

 To open the screen configuration screen user can search the screen in the menu search utility  

or  

open the screen using the following path:  

Menu Path: - Menu> Screen Configuration

• Click on the Screen Configuration link. After clicking on the link, new screen will open as showing in the below screenshot: 

•   Enter the screen name in search, on which the user wants to enable the import button. For  example: User wants to enable on Customer Master screen 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 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. 

1. Custom Mapping 

2. 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 screen. 

• Save the sheet once all the values are entered and is ready for import. 

1.2 Import Data with Excel import utility

 Once the entire excel sheet is complete to import data user have to open the screen on which data is to be imported by locating it using search option. When 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 screen in detail

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.  

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 screen 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 screen, 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 screen, 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 1^st tab mapping only. 

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:  

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. 

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 screen 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. 

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 screen’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 screen 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 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 screen. 

2.2 Download the default mapping excel file 

• Select the Default Mapping from the drop down in the ‘Select Mapping’ field. 

• 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 system 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 screen: 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) 

(screenName/TabName.FieldName_screenName/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) 

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 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 screen 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 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 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. 

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

(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 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.

Open the downloaded Excel file. 

NOTE: If a user login with either English / German language preference, then go to import screen of any screen. let say, Customer Master 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. 

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.