Importing And Exporting The Data Of A Container Inside A Product Using Excel in SAP CPQ

The whole process requires and involves the following steps: 

1. A configurable product that attached to a required category. 

2. A container to perform the import and export operations with columns. 

3. Two Global scripts one is for importing the data and another is for export. 

4. Knock out and html syntax to display the Export and import buttons over the container. 

5. Two external html file that uses Sheet.js library and Excel Utils library to import and export the data. 

1. Creation of the Product: BD_TEST1

   • Create a product named BD_TEST1 of product type à Configurable 

   • Fill the basic details of the product like Display type, Part Number, Unit of measure as shown in the below figure. 

   • Attach the product to the required category. 

     

2. Creation of Attribute

   • To create attribute Go to Setup à Product catalogue à Attributes à Add New 

   • Create the attribute BD_A_3. 

   • Make the type of the attribute as: Container. 

   • Click on general and select the above created product BD_TEST1 to add this container. 

     

   • Create the required columns in the container. 

   • Click on columns à Click on Addnew à and fill the required fields to create the columns as shown in the below image.

     

   • Here the Data type means type of the column like String, Number, Currency and Date. 

   • In the same way create the following columns also Price, Cost, Description and Comments. 

     Column and its Data type: 

     1. Material à String 

     2. Price à Number 

     3. Cost à Number 

     4. Description à String 

     5. Comments à String 

        

     Note: Based on column Type the Iron python syntax will get changed to import the data from the excel into the container. 

     Adding the container to the Product:  

     • Go to the product BD_TEST1 and click on attributes and search for the BD_A_3 and add it to the product like shown in the below image: 

       

     • After adding the attribute to the product, Go to the layout in the product and add the attribute in the configuration. 

3. Adding the Import and Export buttons using KnockOut.JS and html

   • In the responsive template that renders the attribute the below html syntax to be used to place the Import and the export buttons. 

     • First knock out condition to check the name of the container à BD_TEST1 

     • In place of name id can also be used to check the details of the container and the syntax would be  <!—ko if data.id() == “Container_ID” -- > 

     • Second knock out condition to check if there are rows present in the container or not. If rows present then only the Export Data button will displayed. 

     • The span element placed below the export button is load the html file from the File management to export the data into the excel. 

       

     • Once the above syntax is placed then the output will be like shown in the below image:  

       Top Image: Import Parameters button to import the data into the container from the excel file. 

       Bottom Image: Once the rows added to the container then only the Export Data button will get displayed. 

       

       Defining the JS function for export and import buttons

     • When the user clicks on the export button the below function will get executed where an html file is loading into the span element. 

     • Inside the html file a global script is getting executed and the data is being fetched using API response and the same data is passing in the JSON format to the Sheet.js library. 

     • The sent Json data will get converted into an excel file and downloaded. 

       JavaScript functions when the user clicks on the export and Import buttons

       

     • We are loading the Export_data.html file into the span element having id ExportData. 

     • When the user clicks on the export data button the file loaded into the span element and the JS code written in the file gets executed and excel file with the container data gets downloaded. 

     • We are loading the Import_data.html file into the input element having id importButton. 

     • When the user clicks on the Import parameters button the file loaded into the span element and the JS code written in the file gets executed and data in the excel file transferred to the global script through params and the data will be loaded into the container. 

4. File management

   1. Now two html files are required One is to export the data in the container to an excel file. 

   2. Another one is to import data into the container from an external excel file. 

   3. We can use the Sheet.js JavaScript library to perform the export and importing data in the form of Json. 

      1. Name of the export file à Export_data.html 

      2. Name of the import file à Import_data.html 

   4. The above files was uploaded into the file management in the following way 

      1. Go to Setup à General à File management à Additional files à location 

         1. Here the file can be uploaded in the additional files itself or a folder can be created and the file can be uploaded there. 

         2. By clicking on the upload button as shown in the below figure you can upload the file and the same path is to be given while loading these files into the html elements when the Export / Import buttons were clicked.

            

   html syntax of the export file

   

   html syntax for the import file

   

5. Global Scripts

   • Two global scripts are required. One is to export the data in the container and another one is to import data into the container. 

   • To create global scripts Go to setup à Develop à Global script à Add New à 

     • Export_container_data 

     • Import_container_data 

   Export_Container_data: 

   1. This script is used to export the data present inside the container. 

   2. When the user clicks on the Export data button, a special syntax like cpq.server.Executescript to execute the script. 

   Syntax: 

   

   Import_container_data: 

   1. This script is used to import the data. 

   2. For this script the data will be sent through the parameters in the form of JSON and the same data will be accessed in the global script using Param.Input 

   3. As soon as the user clicks on the import button a dialogue box will be appeared on the screen to upload the file where all the excel files in the system gets displayed in the pop-up. 

   4. As soon as the user uploads the file the data will be sent to this script and the data will be restructured and will be assigned. 

   Syntax: 

   

   Key points: 

   While importing the data these steps to be taken care of while filling data in the excel file: 

   1. Type of column à (Standard column, Product attribute) 

      1. If it is standard column the iron python syntax to assign the value to the column will be different. row[‘Column_name’] = value 

      2. If the column type is product attribute the syntax to assign the value as follows : 

         
         

         

         
         

   2. In the excel file while filling the data: 

      1. Based on column data type the data is to be filled. 

      2. Available column data types: 

         1. String à you can paste any value. 

         2. Number à Only numerical values are allowed. 

         3. Currency à Only currency available to be filled. 

         4. Date à Date format should be give. 

      3. If the user fills the data in three rows and left fourth row blank only those three rows data will be imported and any data after that will not be considered by the excel. 

   3. If the column in the container attached to an attribute of User selection or Auto complete sourced from a custom table, the data which present in those attributes should be given else data will not be imported to that column.